SQLCLR vs. SQL Server 2017, Part 6: “Trusted Assemblies” – Whitelisted Assemblies can’t do Module Signing

Posted on by Solomon Rutzky

“Trusted Assemblies”, a new feature starting in SQL Server 2017, is a means of whitelisting Assemblies that one feels pose no threat, and can be created (and used) without needing to be a) signed and b) have a corresponding signature-based Login that has been granted the UNSAFE ASSEMBLY permission. In Part 4 of this series — “Trusted Assemblies” – The Disappointment — I mentioned quite a few problems with “Trusted Assemblies”, explained how Certificates solved the main issue, and why they are better than “Trusted Assemblies”. One of the problems that I mentioned in Part 4 is that they don’t allow for module signing of the T-SQL wrapper objects that point to the methods within the Assembly. Now we will take a look at exactly what that means as well as see both that it is true and that Certificates do not have that problem.

While it is not terribly often that we would need to sign one or more of an Assemblies T-SQL wrapper objects, there are still times when we do need this ability. One such time is when a SQLCLR method will be executed by a User that does not own at least one object that they are accessing and has not been granted permission on it. Most of the time a User will have implied permissions on objects referenced within Stored Procedures, Views, Functions, etc, that they have explicit permission to execute. This is due to ownership chaining, which skips permissions checking on sub-objects if the sub-objects have the same owner as the primary object being executed, or selected from, etc. This is how it’s possible for a User that has no INSERT, UPDATE, or DELETE permissions on a Table, to none-the-less perform those operations on that Table as long as it is a) done through a Stored Procedure that is owned by the owner of the Table, and that b) the User has been given EXECUTE permission on the Stored Procedure.

Ownership chaining is quite handy as it makes it easier to not grant explicit permissions on base objects (i.e. Tables, etc) to everyone. Instead, you just grant EXECUTE / SELECT permissions to Stored Procedures, Views, etc.

However, one situation where ownership chaining does not work is when using Dynamic SQL. And, any SQL submitted by a SQLCLR object is, by its very nature, Dynamic SQL. Hence, any SQLCLR objects that a) do any data access, even just SELECT statements, and b) will be executed by a User that is neither the owner of the objects being accessed nor one that has been granted permissions to the sub-objects, needs to consider module signing in order to maintain good and proper security practices. BUT, the catch here is that in order to sign any Assembly’s T-SQL wrapper objects, that Assembly needs to have been signed with a Strong Name Key or Certificate prior to being loaded into SQL Server. Neither “Trusted Assemblies” nor even signing the Assembly with a Certificate within SQL Server suffices for this purpose, as we will see below.

The Tests / Proof

We will start with a very simple SQLCLR Stored Procedure that accepts an NVARCHAR input parameter and inserts that value into a table that holds just that value and an IDENTITY column. The Stored Procedure uses the OUTPUT clause to pass back the inserted ID value so that it will be easier to see immediately if it actually worked (not getting an error doesn’t necessarily indicate success).

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class Test
{
   [SqlProcedure(Name = "SomeValue_Insert")]
   public static void InsertSomeValue([SqlFacet(MaxSize = 500)]
       SqlString ValueToInsert)
   {
      using (SqlCommand _Command = new SqlCommand())
      {
         _Command.CommandText = @"
             INSERT INTO dbo.InsertTarget ([SomeValue])
                 OUTPUT INSERTED.InsertTargetID
                 VALUES (@SomeValue);";

         SqlParameter _ParamSomeValue =
          new SqlParameter("@SomeValue", SqlDbType.NVarChar, 500);
         _ParamSomeValue.Value =
             (ValueToInsert.IsNull) ? null : ValueToInsert.Value;
         _Command.Parameters.Add(_ParamSomeValue);

         SqlContext.Pipe.ExecuteAndSend(_Command);
      }

      return;
   }
}

I compiled the above C# code into an Assembly named SQL2017_NeedsModuleSigning.dll. I am using the same test Database that has been used for the previous articles in this series, but am including a conditional CREATE DATABASE statement for anyone who wants to copy and paste this code to try it out but who does not have that Database already created.

USE [master];
SET NOCOUNT ON;
GO

IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL) 
BEGIN
    PRINT 'Creating Database...';
    CREATE DATABASE [SQLCLRvsClrStrictSecurity]
        COLLATE Latin1_General_100_CI_AS;
END;


ALTER DATABASE [SQLCLRvsClrStrictSecurity]
    SET RECOVERY SIMPLE,
    TRUSTWORTHY OFF;
GO

The next block of code loads the Assembly. In order to load it with “clr strict security” enabled, and without first having the SHA-512 hash of the Assembly calculated and registered as a “Trusted Assembly”, the Database property of TRUSTWORTHY is disabled, and then re-enabled once the Assembly is loaded.

In order to load the Assembly without needing to reference an external file, I converted the binary DLL file into a hex bytes string using a command-line utility called Binary Formatter. “Binary Formatter” is an open source utility that I wrote and host on GitHub. I use the point-and-click method so that I just right-click on the DLL in File Explorer, “Send To -> Binary Formatter”, and double-click on the generated .sql file.

 



USE [SQLCLRvsClrStrictSecurity];
ALTER DATABASE [SQLCLRvsClrStrictSecurity]
    SET TRUSTWORTHY ON;

CREATE ASSEMBLY [SQL2017_NeedsModuleSigning]
    AUTHORIZATION [dbo]
    FROM 0x\
4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000\
000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21\
546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E\
0D0D0A2400000000000000504500004C010300145DCD590000000000000000E00002210B010B00\
000C00000008000000000000CE2B00000020000000400000000000100020000000020000040000\
000000000006000000000000000080000000020000000000000300608500001000001000000000\
10000010000000000000100000000000000000000000742B00005700000000400000E804000000\
000000000000000000000000000000006000000C0000003C2A00001C0000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000200000080000\
000000000000000000082000004800000000000000000000002E74657874000000D40B00000020\
0000000C000000020000000000000000000000000000200000602E72737263000000E804000000\
40000000060000000E0000000000000000000000000000400000402E72656C6F6300000C000000\
006000000002000000140000000000000000000000000000400000420000000000000000000000\
0000000000B02B0000000000004800000002000500D82000006409000001000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000001B3003006100000001000011731000000A0A0672010000706F1100000A722601\
00701F0C20F4010000731200000A0B070F00281300000A2D090F00281400000A2B01146F150000\
0A066F1600000A076F1700000A26281800000A066F1900000ADE0A062C06066F1A00000ADC2A00\
000001100000020006005056000A000000001E02281B00000A2A42534A4201000100000000000C\
00000076342E302E33303331390000000005006C0000005C020000237E0000C80200009C030000\
23537472696E677300000000640600003C01000023555300A00700001000000023475549440000\
00B0070000B401000023426C6F620000000000000002000001471502000900000000FA25330016\
0000010000001B0000000200000002000000010000001B0000000D000000010000000100000002\
00000000000A0001000000000006003E0037000A00660051000A00AF0094000600D300C1000600\
EA00C10006000701C10006002601C10006003F01C10006005801C10006007301C10006008E01C1\
000600A701C1000600C001C1000600F001DD013B00040200000600330213020600530213020A00\
8C0294000A00B802A2020A00D602C3020A00F002A2020A00FD0245000A001C03C3020A003203A2\
020A005C0394000A00670394000600870337000000000001000000000001000100010010002900\
0000050001000100502000000000960070000A000100D020000000008618800010000200000001\
008600190080001000210080002700290080002700310080002700390080002700410080002700\
490080002700510080002700590080002700610080002700690080002700710080002C00810080\
003200890080001000910080001000990080001000A100E0022700A90080005400110007035C00\
110012036000B90028036400990049036900C10058036E00C9006F037500D10078037A00D90093\
03100009008000100020007B00370024000B0014002E00330004012E001B00A7002E002300C800\
2E002B00CE002E00130087002E003B0037012E004300C8002E005B0074012E00630082012E006B\
008B012E0073009401800004800000040005000600580000000000000071020000040000000000\
00000000000001002E000000000004000000000000000000000001004500000000000000003C4D\
6F64756C653E0053514C323031375F4E656564734D6F64756C655369676E696E672E646C6C0054\
657374006D73636F726C69620053797374656D004F626A6563740053797374656D2E4461746100\
53797374656D2E446174612E53716C54797065730053716C537472696E6700496E73657274536F\
6D6556616C7565002E63746F720056616C7565546F496E73657274004D6963726F736F66742E53\
716C5365727665722E5365727665720053716C4661636574417474726962757465005379737465\
6D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373\
656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E6669\
6775726174696F6E41747472696275746500417373656D626C79436F6D70616E79417474726962\
75746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F\
7079726967687441747472696275746500417373656D626C7954726164656D61726B4174747269\
6275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956\
657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E41747472\
69627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472\
696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D\
70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E73417474\
7269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005351\
4C323031375F4E656564734D6F64756C655369676E696E670053716C50726F6365647572654174\
747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E\
640053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E64007365745F436F6D6D\
616E64546578740053716C506172616D657465720053716C446254797065006765745F49734E75\
6C6C006765745F56616C7565004462506172616D65746572007365745F56616C75650053716C50\
6172616D65746572436F6C6C656374696F6E006765745F506172616D6574657273004164640053\
716C436F6E746578740053716C50697065006765745F506970650045786563757465416E645365\
6E640049446973706F7361626C6500446973706F736500000081230D000A002000200020002000\
20002000200020002000200020002000200049004E005300450052005400200049004E0054004F\
002000640062006F002E0049006E00730065007200740054006100720067006500740020002800\
5B0053006F006D006500560061006C00750065005D0029000D000A002000200020002000200020\
00200020002000200020002000200020002000200020004F005500540050005500540020004900\
4E005300450052005400450044002E0049006E0073006500720074005400610072006700650074\
00490044000D000A00200020002000200020002000200020002000200020002000200020002000\
20002000560041004C0055004500530020002800400053006F006D006500560061006C00750065\
0029003B000015400053006F006D006500560061006C0075006500002B26D3FEFAA1384C842528\
7B08FF19260008B77A5C561934E0890500010111090320000112010001005408074D617853697A\
65F4010000042001010E05200101113D04200101081C01000100540E044E616D6510536F6D6556\
616C75655F496E73657274072003010E115908032000020320000E042001011C04200012610620\
0112551255040000126905200101124D060702124D12551F01001A53514C323031375F4E656564\
734D6F64756C655369676E696E6700002001001B68747470733A2F2F53716C5175616E74756D4C\
6561702E636F6D2F00000501000000003501003053716C205175616E74756D204C656170202820\
68747470733A2F2F53716C5175616E74756D4C6561702E636F6D2F202900003201002D53514C43\
4C522076732053514C2053657276657220323031373A205472757374656420417373656D626C69\
657300003C010037436F7079726967687420286329203230313720536F6C6F6D6F6E205275747A\
6B792E20416C6C207269676874732072657365727665642E00000D010008362E372E382E393900\
000801000200000000000801000800000000001E01000100540216577261704E6F6E4578636570\
74696F6E5468726F7773010000000000145DCD5900000000020000001C010000582A0000580C00\
0052534453779AE4A3F8F4A94383D3F671023654D902000000633A5C53514C5175616E74756D4C\
6561705C50726F6A656374735C426C6F67546F706963735C343136395C53514C323031375F4E65\
6564734D6F64756C655369676E696E675C53514C323031375F4E656564734D6F64756C65536967\
6E696E675C6F626A5C52656C656173655C53514C323031375F4E656564734D6F64756C65536967\
6E696E672E70646200000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000009C2B00000000000000000000BE2B00000020000000000000000000\
00000000000000000000000000B02B00000000000000000000000000000000000000005F436F72\
446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000100100000001800008000000000000000000000000000000100010000\
003000008000000000000000000000000000000100000000004800000058400000900400000000\
000000000000900434000000560053005F00560045005200530049004F004E005F0049004E0046\
004F0000000000BD04EFFE00000100070006006300080007000600630008003F00000000000000\
0400000002000000000000000000000000000000440000000100560061007200460069006C0065\
0049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F00\
6E00000000000000B004F0030000010053007400720069006E006700460069006C00650049006E\
0066006F000000CC030000010030003000300030003000340062003000000050001C0001004300\
6F006D006D0065006E00740073000000680074007400700073003A002F002F00530071006C0051\
00750061006E00740075006D004C006500610070002E0063006F006D002F000000840031000100\
43006F006D00700061006E0079004E0061006D00650000000000530071006C0020005100750061\
006E00740075006D0020004C00650061007000200028002000680074007400700073003A002F00\
2F00530071006C005100750061006E00740075006D004C006500610070002E0063006F006D002F\
00200029000000000060001B000100460069006C00650044006500730063007200690070007400\
69006F006E0000000000530051004C0032003000310037005F004E0065006500640073004D006F\
00640075006C0065005300690067006E0069006E00670000000000340009000100460069006C00\
6500560065007200730069006F006E000000000036002E0037002E0038002E0039003900000000\
0060001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C00\
32003000310037005F004E0065006500640073004D006F00640075006C0065005300690067006E\
0069006E0067002E0064006C006C00000000009400380001004C006500670061006C0043006F00\
7000790072006900670068007400000043006F0070007900720069006700680074002000280063\
00290020003200300031003700200053006F006C006F006D006F006E0020005200750074007A00\
6B0079002E00200041006C006C0020007200690067006800740073002000720065007300650072\
007600650064002E00000068001F0001004F0072006900670069006E0061006C00460069006C00\
65006E0061006D0065000000530051004C0032003000310037005F004E0065006500640073004D\
006F00640075006C0065005300690067006E0069006E0067002E0064006C006C00000000007C00\
2E000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C\
0052002000760073002000530051004C0020005300650072007600650072002000320030003100\
37003A0020005400720075007300740065006400200041007300730065006D0062006C00690065\
0073000000380009000100500072006F006400750063007400560065007200730069006F006E00\
000036002E0037002E0038002E0039003900000000003C000900010041007300730065006D0062\
006C0079002000560065007200730069006F006E00000034002E0035002E0036002E0038003800\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000002000000C000000D03B000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000
        WITH PERMISSION_SET = SAFE;

ALTER DATABASE [SQLCLRvsClrStrictSecurity]
    SET TRUSTWORTHY OFF;
GO



 

Now that the Assembly is loaded, we calculate its SHA-512 hash and register that as a “Trusted Assembly” (which is more accurately a “trusted hash” since a hash value can refer to multiple, different Assemblies). We also display the size of the Assembly (in bytes), the build ID of the Assembly (that changes each time the code is compiled), and the calculated SHA-512 hash of the Assembly. We will use these values to compare with another version of this Assembly later in this demo.

DECLARE @Hash BINARY(64),
        @ClrName NVARCHAR(4000),
        @AssemblySize INT,
        @MvID UNIQUEIDENTIFIER;

SELECT  @Hash = HASHBYTES(N'SHA2_512', af.[content]),
        @ClrName = CONVERT(NVARCHAR(4000), ASSEMBLYPROPERTY(af.[name],
                N'CLRName')),
        @AssemblySize = DATALENGTH(af.[content]),
        @MvID = CONVERT(UNIQUEIDENTIFIER, ASSEMBLYPROPERTY(af.[name], N'MvID'))
FROM    sys.assembly_files af
WHERE   af.[name] = N'SQL2017_NeedsModuleSigning'
AND     af.[file_id] = 1;

SELECT  @ClrName, @AssemblySize, @MvID, @Hash;

EXEC sys.sp_add_trusted_assembly @Hash, @ClrName;
GO

That SELECT statement returns:

  • ClrName = sql2017_needsmodulesigning, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
  • AssemblySize = 5632 bytes
  • MvID = FED3262B-A1FA-4C38-8425-287B08FF1926
  • Hash = 0x316CA8894F404F4D88FE1A06A845AB13DB76B54F9DB66BAAA1893B150E543B9CBEFD3C459C1ED9496EA931B770DAB71553586DCE99C513F32EEF455D5B80A9DD

Now we create the Table that will be inserted into, and the Stored Procedure T-SQL wrapper object:

CREATE TABLE dbo.InsertTarget
(
    [InsertTargetID] INT IDENTITY(1, 1) NOT NULL
                     CONSTRAINT [PK_InsertTarget] PRIMARY KEY,
    [SomeValue]      NVARCHAR(500)
);
GO

CREATE PROCEDURE [dbo].[SomeValue_Insert]
(
    @ValueToInsert NVARCHAR(500)
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SQL2017_NeedsModuleSigning].[Test].[InsertSomeValue];
GO

Below we run the first test: executing the Stored Procedure as the owner of both the Stored Procedure and the Table that it inserts into. This is expected to work, and is mostly just a control for the experiment to show that there are no syntax or compilation errors (i.e. the mechanism, in general, works):

/** TEST A (Running as [dbo]) **/

EXEC [dbo].[SomeValue_Insert] N'first test';
-- Success!
GO

Now that we saw it working, we create a User that does not have any inherent permissions on the Table or Stored Procedure. We grant the User EXECUTE permission on the Stored Procedure. If ownership chaining works, then no additional permission needs to be granted on the Table as it will be assumed:

/** TEST B (Running as [AvgJoe]) **/

CREATE USER [AvgJoe] WITHOUT LOGIN;
GRANT EXECUTE ON [dbo].[SomeValue_Insert] TO [AvgJoe];

EXECUTE AS USER = N'AvgJoe';

EXEC [dbo].[SomeValue_Insert] N'second test';
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 6522, Level 16, State 1, Procedure SomeValue_Insert,
     Line 0 [Batch Start Line YYYYY]
A .NET Framework error occurred during execution of user-defined routine or
     aggregate "SomeValue_Insert": 
System.Data.SqlClient.SqlException: The SELECT permission was denied on the
     object 'InsertTarget', database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
*/
REVERT;
GO

Oops. As predicted, ownership chaining does not work here due to the submitted query, even executed over the Context Connection which is the same Session and permissions as the calling context, being Dynamic SQL. One way around this problem is to grant explicit permissions on the Table to the User. But, that is not a properly secure setup as it allows that User to perform those actions whenever they want, even outside of the Stored Procedure API.

Rather than granting explicit permissions on the Table, we will use module signing — the preferred security mechanism — to grant the additional permissions since any additional permissions granted in this manner only exist within the context of only those modules (i.e. Stored Procedures, Triggers, Functions, etc) that are signed with the same Asymmetric Key or Certificate that was used to create the User and/or Login that contains those additional permissions.

CREATE CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert]
    ENCRYPTION BY PASSWORD = 'YaddaYaddaYadda!'
    WITH SUBJECT = 'Sql Quantum Leap',
    EXPIRY_DATE = '2099-12-31';


CREATE USER [SQL2017-TrustedAssemblies-NeedsSigning-User]
    FROM CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert];


GRANT INSERT, SELECT
    ON [dbo].[InsertTarget]
    TO [SQL2017-TrustedAssemblies-NeedsSigning-User];


ADD SIGNATURE
    TO [dbo].[SomeValue_Insert]
    BY CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert]
    WITH PASSWORD = 'YaddaYaddaYadda!';
/*
Msg 15351, Level 16, State 5, Line XXXXXX
The CLR procedure/function/type being signed refers to an assembly that is
     not signed either by a strong name or an assembly.
*/
GO

Ooops. It looks like SQL Server will not allow module signing of the Stored Procedure T-SQL wrapper object because the Assembly containing the actual SQLCLR code has not been signed. If you will recall, we don’t need to sign the Assembly anymore since we can go the “easy” route of registering its SHA-512 hash as a “Trusted Assembly”. Ok, fine. Since we just created a Certificate in the previous step, we will just use that to sign the Assembly as that is what SQL Server is complaining about:

/** TEST C (Running as [AvgJoe]) **/

ADD SIGNATURE
    TO Assembly::[SQL2017_NeedsModuleSigning]
    BY CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert]
    WITH PASSWORD = 'YaddaYaddaYadda!';


EXECUTE AS USER = N'AvgJoe';

EXEC [dbo].[SomeValue_Insert] N'third test';
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 6522, Level 16, State 1, Procedure SomeValue_Insert,
     Line 0 [Batch Start Line YYYYY]
A .NET Framework error occurred during execution of user-defined routine or
     aggregate "SomeValue_Insert": 
System.Data.SqlClient.SqlException: The SELECT permission was denied on the
     object 'InsertTarget', database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
*/
REVERT;
GO

Oops. Signing the Assembly alone is not good enough. We probably need to also sign the Stored Procedure T-SQL wrapper object, now that we signed the Assembly containing the actual code:

ADD SIGNATURE
    TO [dbo].[SomeValue_Insert]
    BY CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert]
    WITH PASSWORD = 'YaddaYaddaYadda!';
/*
Msg 15351, Level 16, State 5, Line XXXXXX
The CLR procedure/function/type being signed refers to an assembly that is
     not signed either by a strong name or an assembly.
*/
GO

Ooops. It seems as though we still cannot sign the Stored Procedure T-SQL wrapper object. I guess signing the Assembly within SQL Server is not enough to consider the Assembly being truly signed, at least for module signing purposes (we saw in Part 4 that signing Assemblies in this manner is enough to satisfy the “clr strict security” requirement).

We have reached the end-of-the-road for unsigned Assemblies. Now we will try signing the same DLL with a Certificate, prior to loading it into SQL Server. We will still use the “Trusted Assemblies” feature for allowing the Assembly to operate, rather than creating the same Certificate in [master], and creating a Login from that Certificate, and finally granting the Login the UNSAFE ASSEMBLY permission.

We are taking this approach to make the fewest changes possible so that if there is a difference in behavior, then we will know specifically what caused the difference. In order to accomplish this, we first make a copy of the DLL to a new file, and then we will create a Certificate that will be used to sign the new, copied DLL. Please note that I am using a password of “blah” for the Certificate. Not a good password, but this is just for a simple demo; please use a much stronger password for Production-level code.

COPY /B /V SQL2017_NeedsModuleSigning.dll SQL2017_NeedsModuleSigning2.dll

MAKECERT -r -pe -n "CN=SQLCLR Cert,O=SqlQuantumLeap.com,C=US" ^
-e "12/31/2099" -sv SQL2017-NeedsModuleSigning-Cert.pvk ^
SQL2017-NeedsModuleSigning-Cert.cer


PVK2PFX -pvk SQL2017-NeedsModuleSigning-Cert.pvk -pi blah ^
-spc SQL2017-NeedsModuleSigning-Cert.cer ^
-pfx SQL2017-NeedsModuleSigning-Cert.pfx


SIGNTOOL sign /f SQL2017-NeedsModuleSigning-Cert.pfx /p blah /v SQL2017_NeedsModuleSigning_Signed.dll

Once the steps above have been completed, I use the BinaryFormatter utility again to format the binary DLL file into a text file containing the hex bytes of the DLL. Please note in the section below that I first drop the Stored Procedure and the Assembly. I do this, even though I copied the DLL to a new filename, because the underlying DLL is still the same, and SQL Server will not allow two copies of the same DLL to be loaded into a Database. And, since it really is the same Assembly, I create it as the same Assembly name within SQL Server (it doesn’t matter that the filename was different).

 



DROP PROCEDURE [dbo].[SomeValue_Insert]
DROP ASSEMBLY [SQL2017_NeedsModuleSigning];


ALTER DATABASE [SQLCLRvsClrStrictSecurity]
    SET TRUSTWORTHY ON;


CREATE ASSEMBLY [SQL2017_NeedsModuleSigning]
    AUTHORIZATION [dbo]
    FROM 0x\
4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000\
000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21\
546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E\
0D0D0A2400000000000000504500004C010300145DCD590000000000000000E00002210B010B00\
000C00000008000000000000CE2B00000020000000400000000000100020000000020000040000\
000000000006000000000000000080000000020000ED4000000300608500001000001000000000\
10000010000000000000100000000000000000000000742B00005700000000400000E804000000\
0000000000000000160000D8050000006000000C0000003C2A00001C0000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000200000080000\
000000000000000000082000004800000000000000000000002E74657874000000D40B00000020\
0000000C000000020000000000000000000000000000200000602E72737263000000E804000000\
40000000060000000E0000000000000000000000000000400000402E72656C6F6300000C000000\
006000000002000000140000000000000000000000000000400000420000000000000000000000\
0000000000B02B0000000000004800000002000500D82000006409000001000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000001B3003006100000001000011731000000A0A0672010000706F1100000A722601\
00701F0C20F4010000731200000A0B070F00281300000A2D090F00281400000A2B01146F150000\
0A066F1600000A076F1700000A26281800000A066F1900000ADE0A062C06066F1A00000ADC2A00\
000001100000020006005056000A000000001E02281B00000A2A42534A4201000100000000000C\
00000076342E302E33303331390000000005006C0000005C020000237E0000C80200009C030000\
23537472696E677300000000640600003C01000023555300A00700001000000023475549440000\
00B0070000B401000023426C6F620000000000000002000001471502000900000000FA25330016\
0000010000001B0000000200000002000000010000001B0000000D000000010000000100000002\
00000000000A0001000000000006003E0037000A00660051000A00AF0094000600D300C1000600\
EA00C10006000701C10006002601C10006003F01C10006005801C10006007301C10006008E01C1\
000600A701C1000600C001C1000600F001DD013B00040200000600330213020600530213020A00\
8C0294000A00B802A2020A00D602C3020A00F002A2020A00FD0245000A001C03C3020A003203A2\
020A005C0394000A00670394000600870337000000000001000000000001000100010010002900\
0000050001000100502000000000960070000A000100D020000000008618800010000200000001\
008600190080001000210080002700290080002700310080002700390080002700410080002700\
490080002700510080002700590080002700610080002700690080002700710080002C00810080\
003200890080001000910080001000990080001000A100E0022700A90080005400110007035C00\
110012036000B90028036400990049036900C10058036E00C9006F037500D10078037A00D90093\
03100009008000100020007B00370024000B0014002E00330004012E001B00A7002E002300C800\
2E002B00CE002E00130087002E003B0037012E004300C8002E005B0074012E00630082012E006B\
008B012E0073009401800004800000040005000600580000000000000071020000040000000000\
00000000000001002E000000000004000000000000000000000001004500000000000000003C4D\
6F64756C653E0053514C323031375F4E656564734D6F64756C655369676E696E672E646C6C0054\
657374006D73636F726C69620053797374656D004F626A6563740053797374656D2E4461746100\
53797374656D2E446174612E53716C54797065730053716C537472696E6700496E73657274536F\
6D6556616C7565002E63746F720056616C7565546F496E73657274004D6963726F736F66742E53\
716C5365727665722E5365727665720053716C4661636574417474726962757465005379737465\
6D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373\
656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E6669\
6775726174696F6E41747472696275746500417373656D626C79436F6D70616E79417474726962\
75746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F\
7079726967687441747472696275746500417373656D626C7954726164656D61726B4174747269\
6275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956\
657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E41747472\
69627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472\
696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D\
70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E73417474\
7269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005351\
4C323031375F4E656564734D6F64756C655369676E696E670053716C50726F6365647572654174\
747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E\
640053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E64007365745F436F6D6D\
616E64546578740053716C506172616D657465720053716C446254797065006765745F49734E75\
6C6C006765745F56616C7565004462506172616D65746572007365745F56616C75650053716C50\
6172616D65746572436F6C6C656374696F6E006765745F506172616D6574657273004164640053\
716C436F6E746578740053716C50697065006765745F506970650045786563757465416E645365\
6E640049446973706F7361626C6500446973706F736500000081230D000A002000200020002000\
20002000200020002000200020002000200049004E005300450052005400200049004E0054004F\
002000640062006F002E0049006E00730065007200740054006100720067006500740020002800\
5B0053006F006D006500560061006C00750065005D0029000D000A002000200020002000200020\
00200020002000200020002000200020002000200020004F005500540050005500540020004900\
4E005300450052005400450044002E0049006E0073006500720074005400610072006700650074\
00490044000D000A00200020002000200020002000200020002000200020002000200020002000\
20002000560041004C0055004500530020002800400053006F006D006500560061006C00750065\
0029003B000015400053006F006D006500560061006C0075006500002B26D3FEFAA1384C842528\
7B08FF19260008B77A5C561934E0890500010111090320000112010001005408074D617853697A\
65F4010000042001010E05200101113D04200101081C01000100540E044E616D6510536F6D6556\
616C75655F496E73657274072003010E115908032000020320000E042001011C04200012610620\
0112551255040000126905200101124D060702124D12551F01001A53514C323031375F4E656564\
734D6F64756C655369676E696E6700002001001B68747470733A2F2F53716C5175616E74756D4C\
6561702E636F6D2F00000501000000003501003053716C205175616E74756D204C656170202820\
68747470733A2F2F53716C5175616E74756D4C6561702E636F6D2F202900003201002D53514C43\
4C522076732053514C2053657276657220323031373A205472757374656420417373656D626C69\
657300003C010037436F7079726967687420286329203230313720536F6C6F6D6F6E205275747A\
6B792E20416C6C207269676874732072657365727665642E00000D010008362E372E382E393900\
000801000200000000000801000800000000001E01000100540216577261704E6F6E4578636570\
74696F6E5468726F7773010000000000145DCD5900000000020000001C010000582A0000580C00\
0052534453779AE4A3F8F4A94383D3F671023654D902000000633A5C53514C5175616E74756D4C\
6561705C50726F6A656374735C426C6F67546F706963735C343136395C53514C323031375F4E65\
6564734D6F64756C655369676E696E675C53514C323031375F4E656564734D6F64756C65536967\
6E696E675C6F626A5C52656C656173655C53514C323031375F4E656564734D6F64756C65536967\
6E696E672E70646200000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000009C2B00000000000000000000BE2B00000020000000000000000000\
00000000000000000000000000B02B00000000000000000000000000000000000000005F436F72\
446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000100100000001800008000000000000000000000000000000100010000\
003000008000000000000000000000000000000100000000004800000058400000900400000000\
000000000000900434000000560053005F00560045005200530049004F004E005F0049004E0046\
004F0000000000BD04EFFE00000100070006006300080007000600630008003F00000000000000\
0400000002000000000000000000000000000000440000000100560061007200460069006C0065\
0049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F00\
6E00000000000000B004F0030000010053007400720069006E006700460069006C00650049006E\
0066006F000000CC030000010030003000300030003000340062003000000050001C0001004300\
6F006D006D0065006E00740073000000680074007400700073003A002F002F00530071006C0051\
00750061006E00740075006D004C006500610070002E0063006F006D002F000000840031000100\
43006F006D00700061006E0079004E0061006D00650000000000530071006C0020005100750061\
006E00740075006D0020004C00650061007000200028002000680074007400700073003A002F00\
2F00530071006C005100750061006E00740075006D004C006500610070002E0063006F006D002F\
00200029000000000060001B000100460069006C00650044006500730063007200690070007400\
69006F006E0000000000530051004C0032003000310037005F004E0065006500640073004D006F\
00640075006C0065005300690067006E0069006E00670000000000340009000100460069006C00\
6500560065007200730069006F006E000000000036002E0037002E0038002E0039003900000000\
0060001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C00\
32003000310037005F004E0065006500640073004D006F00640075006C0065005300690067006E\
0069006E0067002E0064006C006C00000000009400380001004C006500670061006C0043006F00\
7000790072006900670068007400000043006F0070007900720069006700680074002000280063\
00290020003200300031003700200053006F006C006F006D006F006E0020005200750074007A00\
6B0079002E00200041006C006C0020007200690067006800740073002000720065007300650072\
007600650064002E00000068001F0001004F0072006900670069006E0061006C00460069006C00\
65006E0061006D0065000000530051004C0032003000310037005F004E0065006500640073004D\
006F00640075006C0065005300690067006E0069006E0067002E0064006C006C00000000007C00\
2E000100500072006F0064007500630074004E0061006D00650000000000530051004C0043004C\
0052002000760073002000530051004C0020005300650072007600650072002000320030003100\
37003A0020005400720075007300740065006400200041007300730065006D0062006C00690065\
0073000000380009000100500072006F006400750063007400560065007200730069006F006E00\
000036002E0037002E0038002E0039003900000000003C000900010041007300730065006D0062\
006C0079002000560065007200730069006F006E00000034002E0035002E0036002E0038003800\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000002000000C000000D03B000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000D805000000020200308205C506092A864886F70D010702\
A08205B6308205B2020101310B300906052B0E03021A0500304C060A2B060104018237020104A0\
3E303C3017060A2B06010401823702010F3009030100A004A20280003021300906052B0E03021A\
05000414EA8A2C1D6AD110A0FCDD13E47263319DBBA7B2C9A082037D3082037930820265A00302\
01020210D87AE74FDC0D1F924C0B5DAF6A2ABF9F300906052B0E03021D05003040310B30090603\
55040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D311430\
120603550403130B53514C434C5220436572743020170D3137303932363231353232345A180F32\
303939313233313034303030305A3040310B3009060355040613025553311B3019060355040A13\
1253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C52204365\
727430820122300D06092A864886F70D01010105000382010F003082010A02820101008F7A160A\
A9DF6D5FB545B2CF6D93D9276C1F99EF4B93FF43085979C20EA755AE9AD9904E985CE9567FAC69\
E8BB27F9ABFE6B88F72D381FD3A413E7EF757A4DE517DA4EB9F700C4DBCCEDB0D270C4C17835D0\
F1F8A4437151C1F6D558E337677201DB9AFE894860FC1611C37E2CC982C270ED229FFF52E22280\
54F20E1604728E3FB0151D56EBC4E3C6355A2323D173DCB87B4051B9F49C910CF95E2877F776D8\
AA6174C7AA573506D98DA1EAC6431C61638A15B8EE3F47E28D1DEFC4412AE701FB25A851F2F938\
D58C85E5BCFEAD0CE5E70AF8374E00F0D94FDF42494CFFDFA568AECDF2A9A9C7F2407FF9325B9C\
3AF3CAB637B1244D7B76FC1C111A926D81E50203010001A375307330710603551D01046A306880\
10F5B1894ED1B26862F1C18732AE25474CA1423040310B3009060355040613025553311B301906\
0355040A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C43\
4C5220436572748210D87AE74FDC0D1F924C0B5DAF6A2ABF9F300906052B0E03021D0500038201\
01004C42778D2DB0252540965178BAD4EB40CA21610AF6EDA61A1C5E5B88ECCA9E2DD123B483A0\
FAF284211AD6C468CCEEA4D380533F404A3274A9501C7B6B1E395D47D69CA729F6CC4B1C25ED7A\
DD5230BC6FEFAA53D94A8EA0754CC7C737671F2A1C779AD5A29125A68ABC4AFFA32A129121D4EF\
FE239C2033AADFFCA8D11D9B12630A87EBA6DD09F0B1FB8A0075DE1A1029BB9462974BA802F0A8\
D663E4BB8AAE60E3E8D2DA04E5DEF567D461735DDE6B6DD89980A8491A18FBD0609B2E7C0453CD\
9FD2DDF664F97073B12362857238B9C1129E244F120B8A7FA0BD6D1D192DCAF9B8530A829BED27\
B000EAA55FDFD0A9FE776F05096865790A1F7D2ABFAEB95F318201CF308201CB02010130543040\
310B3009060355040613025553311B3019060355040A131253716C5175616E74756D4C6561702E\
636F6D311430120603550403130B53514C434C5220436572740210D87AE74FDC0D1F924C0B5DAF\
6A2ABF9F300906052B0E03021A0500A0523010060A2B06010401823702010C3102300030190609\
2A864886F70D010903310C060A2B060104018237020104302306092A864886F70D010904311604\
14F8B4B4F7EDDFC2330F0B832B384141CAB27B6072300D06092A864886F70D0101010500048201\
003D30C3664E5F6636DD410F127C06E2389F8130AB911B326CC1F563260353BDD233C5B6636A16\
9905D7AEDA707EAF4C86034824977B9C28AB8C67DE891171FB3AB56B48AC0DF02FF558A73E71FD\
4509FFDCC6C88A943E755177015C301AD72FF4F34C922FFFE74D6DA5237B59333D1DF749C016F3\
72B844B93E3ED6C9464323D28D7EE242EBA36F7233FBC4ABD8796740A3308AB7E989B58FF7CF1B\
FB0679C0E6DB7A2ECA328621FC3783204B6D0A4C9A03C39B110C1C3768175E79D23DC1005AFF44\
977E2A1F174B9A5C5CD54FA1EBD064B3AAF98626B656B5FD98E6065695A83AD56B52EDD9C5C7FB\
037824FA63787EEBC748A1E649357FA3DC69B992931E6200000000000000
            WITH PERMISSION_SET = SAFE;


ALTER DATABASE [SQLCLRvsClrStrictSecurity]
    SET TRUSTWORTHY OFF;
GO



 

Once again we need to register the SHA-512 hash of the Assembly as a “Trusted Assembly”, and then create the Stored Procedure T-SQL wrapper object. We also display the size, Assembly version, and hash value again so that we can compare with the values we saw the first time we did this (with the unsigned version of this Assembly).

DECLARE @Hash BINARY(64),
        @ClrName NVARCHAR(4000),
        @AssemblySize INT,
        @MvID UNIQUEIDENTIFIER;

SELECT  @Hash = HASHBYTES(N'SHA2_512', af.[content]),
        @ClrName = CONVERT(NVARCHAR(4000), ASSEMBLYPROPERTY(af.[name],
            N'CLRName')),
        @AssemblySize = DATALENGTH(af.[content]),
        @MvID = CONVERT(UNIQUEIDENTIFIER, ASSEMBLYPROPERTY(af.[name], N'MvID'))
FROM    sys.assembly_files af
WHERE   af.[name] = N'SQL2017_NeedsModuleSigning'
AND     af.[file_id] = 1;

SELECT  @ClrName, @AssemblySize, @MvID, @Hash;

EXEC sys.sp_add_trusted_assembly @Hash, @ClrName;
GO

CREATE PROCEDURE [dbo].[SomeValue_Insert]
(
    @ValueToInsert NVARCHAR(500)
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SQL2017_NeedsModuleSigning].[Test].[InsertSomeValue];
GO

That SELECT statement returns:

  • ClrName = sql2017_needsmodulesigning, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
  • AssemblySize = 7128 bytes
  • MvID = FED3262B-A1FA-4C38-8425-287B08FF1926
  • Hash = 0xFFF251E344CD94F29EE587A7B5AAE8AF3E9472D14493F6F88409A1D84FD0BBCC59F4320EBA874572737BAD8EF5056F403FAA5CFBE5E4C1DA246DEFD5E0ABDE21

If you check the values that were returned the first time (up above), you will notice that the Assembly size is larger here (a result of adding the Certificate), yet the MvID (i.e. the Assembly ID that changes upon each compilation) is the same! The same MvID means that this is the exact same Assembly that we had before, yet due to signing it the size is larger and the hash value is naturally different.

Now we will again grant the User that does not own the objects permission to execute the Stored Procedure:

/** TEST D (Running as [AvgJoe]) **/

GRANT EXECUTE ON [dbo].[SomeValue_Insert] TO [AvgJoe];

EXECUTE AS USER = N'AvgJoe';

EXEC [dbo].[SomeValue_Insert] N'fourth test';
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 229, Level 14, State 5, Line XXXXX
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
Msg 6522, Level 16, State 1, Procedure SomeValue_Insert,
     Line 0 [Batch Start Line YYYYY]
A .NET Framework error occurred during execution of user-defined routine or
     aggregate "SomeValue_Insert": 
System.Data.SqlClient.SqlException: The SELECT permission was denied on the
     object 'InsertTarget', database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
The INSERT permission was denied on the object 'InsertTarget',
     database 'SQLCLRvsClrStrictSecurity', schema 'dbo'.
*/
REVERT;
GO

Again we get the errors. But wait, we forgot to sign the Stored Procedure. If you recall, we did try to sign it last time, but got an error stating that the underlying Assembly had not been signed. Will it work this time? Remember, the only difference this time is that the Assembly was signed with a Certificate before being loaded into SQL Server.

/** TEST E (Running as [AvgJoe]) **/

ADD SIGNATURE
    TO [dbo].[SomeValue_Insert]
    BY CERTIFICATE [SQL2017-TrustedAssemblies-NeedsSigning-Cert]
    WITH PASSWORD = 'YaddaYaddaYadda!';


EXECUTE AS USER = N'AvgJoe';

EXEC [dbo].[SomeValue_Insert] N'fifth test';
-- Success !!!!!!!

REVERT;
GO

Dude!!!! It works. Finally, it works. This means that all that is required is to sign the Assembly prior to loading it into SQL Server. And while this was not shown in this demo, using a Strong Name Key (via sn.exe) would have worked just the same.

Conclusion

As shown here, the “Trusted Assemblies” feature does not, by itself, allow for module signing of the T-SQL wrapper objects of an Assembly. In order for T-SQL wrapper objects to be signed, which is required for maintaining proper security practices, Assemblies need to be signed prior to being loaded into SQL Server. AND, (and herein lies the reality of the situation), if you are going to sign the Assembly before loading it into SQL Server, then you might as well just create that same Certificate and its associated Login. It makes little to no sense to sign the Assembly with a Certificate (which allows for the ideal, highly secure setup) just to then skip creating the Certificate in SQL Server and instead register its hash as a “Trusted Assembly” (which is a non-ideal, far less secure setup).

Once again we see that “Trusted Assemblies” are an inadequate and inappropriate solution to a problem that had already been solved 12 years (and 6 versions) ago. Certificates and module signing have been around since SQLCLR was introduced in SQL Server 2005, and are a truly secure mechanism for solving any problem that one believes that they need “Trusted Assemblies” for. And they don’t have any of the drawbacks that “Trusted Assemblies” have.

“Trusted Assemblies” should be fully removed (not just deprecated) ASAP before any lasting damage is done, either to SQL Server users / systems or the legitimacy of SQLCLR as a solid, viable feature / tool for solving certain types of problems. To that end, please support my request to have “Trusted Assemblies” removed from SQL Server: Trusted Assemblies are more problematic yet less functional than Certificates – Please Remove.

10 thoughts on “SQLCLR vs. SQL Server 2017, Part 6: “Trusted Assemblies” – Whitelisted Assemblies can’t do Module Signing”

  5. Hallo Solomon,

    I am “fighting” against CLR STPs in SQL 2017 now. I have certificate und signature for my assemblies. So far so good. However, I cannot create assembly in spite of DB Trustworthy is ON so long CLR STRICT SECURITY = 1. You wrote CLR STRICT SECURITY setting can leave on 1, and if TRUSTWORTHY is ON it can be possible to CREATE ASSEMBLY without signature. After CREATE ASSEMBLY (from binaries) statement fulfilled, the TRUSTWORTHY can be set OFF and then ADD SIGNATURE TO ASSEMBLY statement can take place. It was my plan also. However, it does not work.
    I do not want to set CLR STRICT SECURITY = 0, so I should add CLR STP in the trusted list, add signature and then drop STP from trusted list. It is not so simple as TRUSTWORTHY ON \OFF and so on…
    SQL 2017 Version I have is – 14.0.3238.1 Express.
    Can it be – MS has changed hear something?

    Best Regards
    Vladi Chernov

    1. Hello Vladi. You have not provided enough specific information for me to be able to help. Is the assembly one that you created, a Microsoft .NET framework library, a 3rd party assembly? If you have the certificate, then is the assembly already signed with it before executing CREATE ASSEMBLY? If yes, then there is no need to set TRUSTWORTHY to OFF. You only need to ADD SIGNATURE to an Assembly if it is not signed and you have no easy way of signing it outside of SQL Server.

      What specific error (number and message) do you get? What does “it does not work” really mean? Did you create the certificate in the [master] DB? Did you create the Login from that Certificate? Did you grant that Login the UNSAFE ASSEMBLY permission?

      You definitely do not need to add it to “trusted assemblies”, and also will not need to set CLR strict security to 0.

  6. Hallo Solomon,

    hear is Vladi again. It works if DBO has sysadmin rights. Unfortunately, it is not a case by our clients – DBO has no sysadmin rights. I am going to do it via sp_add_trusted_assembly.
    Thanks a lot for your articles. They are excellent.

    Regards.
    Vladi

    1. Hi Vladi. The only reason that dbo would need sysadmin rights is if you are relying on TRUSTWORTHY ON. If you follow the steps I outlined in Parts 2, 3, or 4, then you definitely would not need dbo to have sysadmin rights. There must be some simple step that is missing. Again, if you could answer any of those questions then I’m sure we could figure it out. I even just ran the test script from Step 4 again, this time changing dbo to a new login that has no permissions to anything, and it still works just fine.

    1. Hi there, and thanks for mentioning this. Yes, MakeCert.exe is deprecated and does not do nearly as much as the PowerShell function, but it is still available, will be for a long time, and does exactly what’s needed for our purposes with SQLCLR. However, in reviewing this post I did notice a typo and a missing section, so I will add those.

Leave a Reply