How to sign an unsafe C# CLR and assemblies that I'm using instead of putting database trustworthy on

Question

I am using .NET DirectoryServices and DirectoryServices.AccountManagement in my C# CLR code. I want to publish to my SQL Server database as an Unsafe CLR stored procedure. In Visual Studio, I signed my CLR project. I want to avoid turning Trustworthy ON. How do I also sign the assemblies? I know this sounds pretty simple, but I have searched 4 hours on google without coming up with an answer. I thought I was pretty close when I downloaded Stairway to CLR, but couldn't find the answer there.

Answer 1

We have several older projects for which this is needed. Up until this week when I took the time to figure this out, we had been using the dreaded Trustworthy flag.

After a bunch of research I was able to set this up to be fairly painless by using a Pre-Deployment sql script in my Visual Studio project and some one-time setup. This article helped me the most.

I have tested and confirmed this works in:

• SQL Server 2008 R2
• SQL Server 2012
• SQL Server 2014
• SQL Server 2016

Steps:

1. Create a new strong name key using Developer command prompt for Visual Studio and typing:

   sn -k ".\CLRKey.snk"
   

2. In your Visual Studio project settings, in the SQLCLR area, click the Signing button and choose to sign your assembly by browsing to the key file you just created:

3. Copy CLRKey.snk to C:\ of each target SQL server, or some other path that the server can read.

4. Add this Pre-Deployment script to your Visual Studio project, adjusting the key path and password to fit your environment:

   DECLARE @sp nvarchar(255) = '$(DatabaseName)..sp_executesql';
   DECLARE @create_user nvarchar(4000) = N'CREATE USER [CLRExtensionLogin] FOR LOGIN [CLRExtensionLogin]'
   DECLARE @MSG varchar(8000)
   
   use master;
   BEGIN TRY
   BEGIN TRAN
   
       IF NOT EXISTS (select 1 from sys.asymmetric_keys where name=N'CLRExtensionKey') BEGIN
           create asymmetric key CLRExtensionKey
           from file = 'c:\CLRKey.snk'
           encryption by password = '<invent a password>'
           RAISERROR( 'Key created.', 0, 1 ) WITH NOWAIT
       END ELSE BEGIN
           RAISERROR( 'Key exists.', 0, 1 ) WITH NOWAIT
       END
   
       IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name=N'CLRExtensionLogin') BEGIN
           CREATE LOGIN CLRExtensionLogin FROM ASYMMETRIC KEY CLRExtensionKey;
           EXEC @sp @create_user
           RAISERROR( 'User created.', 0, 1 ) WITH NOWAIT
       END ELSE BEGIN
           RAISERROR( 'User exists.', 0, 1 ) WITH NOWAIT
       END
   
       GRANT EXTERNAL ACCESS ASSEMBLY, UNSAFE ASSEMBLY TO [CLRExtensionLogin];
       COMMIT TRAN
   
       SET @MSG = 'Assembly access granted.' + CHAR(10) + 'Done.'
       RAISERROR( @MSG, 0, 1 ) WITH NOWAIT
   END TRY
   BEGIN CATCH
       SET @MSG = ERROR_MESSAGE() + CHAR(10) + 'Aborted.'
       RAISERROR( @MSG, 16, 1 )
       IF @@TRANCOUNT > 0 ROLLBACK
   END CATCH
   
   USE [$(DatabaseName)];
   

As long as SQL Server can read in that key file the first time the script runs, you should be able to Publish to the same server freely without having to worry about all of this again.

If you have other projects that publish to the same server, all you have to do is sign them with the same key like you did in step 2, but it might be a best practice to link to that same Pre-Deployment script in all your projects in case you want to publish one to a different server at some point. Hope this helps!

Answer 2

First: Thank you for not going the easy route and enabling TRUSTWORTHY. Seriously. It is not that much work to do this correctly, so I (and your employer) appreciate you doing more research, and when you couldn't get any farther, reaching out for help 😺.

If you are referring to the "Stairway to SQLCLR" series on SQL Server Central, I am the author of those articles. I am sorry if the information was not easily understood. I will outline the steps here:

1. In Visual Studio, sign the Assembly with a Strong Name Key, and protect it with a password (you have already done this, according to the question)

2. Depending on what you are doing and the overall requirements, you have some options here:

   1. If your Assembly is using code that does flag a violation on the verification step when being created via CREATE ASSEMBLY, then you can technically load the Assembly into [master], marked as SAFE, to create the Asymmetric Key from it (you won't execute the code here so it doesn't need to be marked as UNSAFE here).
   2. If your code does not pass verification (meaning: it can only be created when marked as UNSAFE), then I find it best to create a separate, empty Assembly, also signed with the same Strong Name Key file (i.e. the .pfx file) used for the main Assembly. I then load that, empty yet signed, Assembly into [master], marked as SAFE
   3. If you need to deal with SQL Server 2017, then that requires an extra step which I will mention in a moment.

3. Once you have an Assembly created in [master] that was signed with the same .pfx file that was used to sign your main, UNSAFE Assembly, then you can simply create, in [master], an Asymmetric Key from that Assembly.

4. Create a Login from that Asymmetric Key

5. Grant that Key-based Login the UNSAFE ASSEMBLY permission

These steps work for SQL Server 2005 - 2016, and I provide instructions on how to automate this (Option 2 above -- using a separate, empty, signed Assembly) in Visual Studio in the Stairway to SQLCLR Level 7: Development and Security article. (btw, I do realize that the steps described in Level 7 are admittedly a bit much, but they do provide a means of automation using Visual Studio AND they can be simplified by using T4 templates -- which comes with VS -- but I haven't had time yet to write up the simplified approach -- hopefully soon, though).

IF, however, you need to account for SQL Server 2017 (or newer, presumably), then the steps above will not work as not even SAFE Assemblies can be created anymore without being signed and having the corresponding signature-based Login that has the UNSAFE ASSEMBLY permission. And, unfortunately, CREATE ASYMMETRIC KEY does not allow for creating from a hex bytes string / VARBINARY literal, so you need to use a Certificate which does allow for being created that way. Along those lines, I have two blog posts which detail, step-by-step, how to accomplish this, fully automated via Visual Studio (or even not using Visual Studio):

• SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 — this one uses the approach described in Stairway to SQLCLR Level 7: Development and Security as the basis, and updates it to work within the new contraint.
• SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2 — this one is a greatly simplified approach, using Certificates only

Whatever you do, do not get fooled into using the new "Trusted Assemblies" feature of SQL Server 2017 as there are numerous problems with it, as described here: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment and one more post that I am working on now.

To help improve this needlessly painful process, please support my enhancement requests to improve CREATE ASYMMETRIC KEY and SSDT:

• Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE (SQLCLR) — this one alone would reduce the majority of steps in each of the approaches noted above, and would even reduce the need to have the Certificate-based options since the Asymmetric Key option would work for everything, even within the new "clr strict security" constraint starting in SQL Server 2017.
• Add function to extract Asymmetric Key similar to CERTENCODED for Certificates (SQLCLR / SSDT)
• Add MSBuild predefined Targets for "BeforeSqlBuild" and "BeforePublish" to SSDT SQL Server Database Projects