Signing unsafe assemblies with asymmetric key

Question

I'd like to deploy my own CLR, which would require unsafe permissions and setting the database as trustworthy.

I found that it's possible to sign assemblies with external access with an asymmetric key, but I didn't find a way to sign an unsafe assembly with it.

Is that possible? If yes, what would be the steps to do it?

Answer 1

Thank you for digging a little deeper to avoid setting the database to TRUSTWORTHY ON, which is a far too common practice, unfortunately.

The same steps are taken to sign Assemblies and create the Asymmetric Key in master from the Assembly DLL and then create the Login from that Asymmetric Key. The only difference is to then grant that Key-based Login the UNSAFE ASSEMBLY permission instead of the EXTERNAL ACCESS ASSEMBLY permission.

You do not ever need both permissions as the UNSAFE ASSEMBLY permission allows for setting Assemblies to EXTERNAL ACCESS, though it does not hurt to have both.

For more info, in general, related to working with SQLCLR, please see the series I am writing on SQL Server Central (free registration is required to read their content):

Stairway to SQLCLR

If you are using Visual Studio / SSDT to deploy / publish your SQLCLR project, please see "Stairway to SQLCLR Level 7: Development and Security" in that Stairway series as it shows a technique for automating the creation of the Asymmetric Key and Key-based Login, neither of which can be handled using regular SSDT objects. Another, even easier technique will be shown in the next (to be published) article.

---

UPDATE Regarding SQL Server 2017

SQL Server 2017 introduced a new security feature ("CLR strict security", an advanced option) that is enabled by default and requires that ALL Assemblies, even those marked as SAFE, be signed with either an Asymmetric Key (i.e. strong name) or Certificate and have a Login (based on whatever was used to sign the Assembly) that has the UNSAFE ASSEMBLY permission. For details on how to make this work, with or without Visual Studio / SSDT, please see the following two posts of mine:

• SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
• SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

Please avoid the new Trusted Assemblies "feature" as it has many more flaws than benefits, not to mention it being entirely unnecessary in the first place given that existing functionality already handled the situation "Trusted Assemblies" was meant to address. For full details on that and a demo of the proper way to handle existing, unsigned Assemblies, please see: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment.

Answer 2

For anyone who wants to sign and deploy a CLR project using Visual studio

1. Open visual studio and go to the project properties.

   • Change the 'Permission level' to EXTERNAL_ACCESS or UNSAFE depending on what you want.
   • Press on the ‘Signing’ button, generate a new key and sign the project as shown in the following picture

2. Build the project

3. Open SQL Management Studio and run the following scripts by replacing the placeholders.

   Before running the following, make sure you have permission to access the MASTER database and the CLR project directory.

   USE master;
   GO

   -- Create asymmetric key
   CREATE ASYMMETRIC KEY [PROJECT_NAME]_Key 
   FROM EXECUTABLE FILE 
   = [CLR_PROJECT_DIRECTORY]\bin\Debug\[PROJECT_NAME].dll'

   -- Create a new login with the asymmetric key
   CREATE LOGIN [PROJECT_NAME]_Login 
   FROM ASYMMETRIC KEY [PROJECT_NAME]_Key

   -- Grant External access to login
   GRANT EXTERNAL ACCESS ASSEMBLY TO [PROJECT_NAME]_Login
   GO

   -- Uncomment the following for UNSAFE assembly
   -- GRANT UNSAFE ASSEMBLY TO [PROJECT_NAME]_Login

5. Publish your project from Visual Studio. Done :)

The scripts are taken from Creating an assembly