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

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