So you want to load a CLR assembly into SQL Server. What’s that you say? You DONT want to set your database to TRUSTWORTHY? Well look no further, there is a much more secure alternative to the old TRUSTWORTHY fallback: sign your assemblies with a secure certificate.
While this sounds like a walk in the park, it’s not very straight-forward. In this post I will walk you through the steps necessary to get up and running with a signed assembly. First you’ll need some additional tools. You’ll need the makecert.exe tool, which is part of the Microsoft Windows SDK. Also needed are the signtool and pvk2pfx tool, both of which are also part of the SDK. You can download the Windows SDK here: http://go.microsoft.com/fwlink/?linkid=84091.
Once you’ve downloaded and installed the SDK, you’re ready to begin. First, create the root authority certificate:
makecert -sv SignRoot.pvk -cy authority -r signroot.cer -n "CN=Cert Authority"
Once you’ve created this certificate, use it to create an additional certificate and private key pair. This is what will be loaded into SQL server. It is also what you will use to sign compiled DLL files.
makecert -m 360 -n "CN=Signing Cert" -iv signroot.pvk -ic signroot.cer -cy end -pe -sv signcert.pvk signcert.cer
Now convert this certificate into a PFX file for signing assemblies:
pvk2pfx -pvk signcert.pvk -spc signcert.cer -pfx signcert.pfx
Once you have the PFX file, you can use it to sign assemblies:
signtool sign /f signcert.pfx "YOUR_DLL_HERE"
Load the certificate file created above into SQL Server. Be sure to use the .cer file:
USE [master]; GO CREATE CERTIFICATE CLRCertificate FROM FILE = 'signcert.cer' WITH PRIVATE KEY ( FILE = 'signcert.pvk' , DECRYPTION BY PASSWORD = 'password' , ENCRYPTION BY PASSWORD = 'password' )
Now that the certificate has been loaded into SQL Server, you need to create a login from that certificate. This login will need to have the necessarey assembly permissions (unsafe, external_access).
CREATE LOGIN AssemblyLoader FROM CERTIFICATE CLRCertificate REVOKE connect sql FROM AssemblyLoader GRANT external access assembly TO AssemblyLoader GRANT unsafe assembly TO AssemblyLoader
By creating a certificate-based login that has the unsafe assembly and external_access assembly permissions, you need not grant any other login these permissions. When you go to load a signed assembly, SQL Server sees that there is a login associated with the signing certificate and that the login has the necessary permissions to load assemblies of any permission level.
There you have it, the assembly is successfully loaded into SQL Server without compromising security by using the TRUSTWORTHY flag. Enjoy.