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 

There you have it, the assembly is successfully loaded into SQL Server without compromising security by using the TRUSTWORTHY flag. Enjoy.

Overview

When scripting stored procedure code for deployment to several servers, there are a few things to keep in mind.  One of those things is the permissions associated with the procedure you’re deploying.  When deploying a brand new stored procedure, the process of assigning permissions is fairly straightforward.  If you need to deploy an update to an existing stored procedure, things get a bit more complicated.  If you simply issue a DROP PROCEDURE call followed by the CREATE PROCEDURE code, you will lose the permissions that are associated with the stored procedure.  Issuing an ALTER PROCEDURE command will preserve the permissions but will cause you trouble if there are places where the procedure doesn’t yet exist.  To solve this issue, you should script out the permissions on a procedure to a temp table before dropping the procedure.  If there is a chance that the batch will fail, use a persisted table or a global temp table instead. Once the proc is recreated, run through the permissions and apply them to the proc again.  This way, you’ve preserved the permissions on the proc.

Now, there are some pitfalls to this approach.  If the proc didn’t already exist when you run the deploy script, there will obviously be no permissions to script out to a temp table.  So, you might run into the problem where the same proc on two different databases has two sets of permissions.  This might be an issue, but considering the proc didn’t exist in the first place, it’s doubtful that it would be an issue.  Nevertheless it is something to keep in mind.

The way to get around this pitfall is to include desired the permissions in the SQL script used to create the procedure. This way, the newly created proc will have the permissions it had before, in addition to any included in the deployment script.

The Code

Here is some sample SQL for scripting the permissions into a temp table:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SampleProc')
BEGIN
	IF OBJECT_ID('tempdb..#tmp_PermissionsWork') IS NOT NULL DROP TABLE #tmp_PermissionsWork
	CREATE TABLE #tmp_PermissionsWork (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL 
		, Permission NVARCHAR(MAX) NULL
	)
	
	INSERT #tmp_PermissionsWork (Permission)
	SELECT state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + QUOTENAME(USER_NAME(grantee_principal_id)) + ';' FROM sys.database_permissions 
	WHERE major_id = OBJECT_ID('SampleProc')

	DROP PROCEDURE SampleProc
END
GO

CREATE PROCEDURE SampleProc
AS
...
GO

DECLARE @cmd NVARCHAR(MAX)

WHILE EXISTS(SELECT 1 FROM #tmp_PermissionsWork)
BEGIN
	SELECT TOP 1 @cmd = Permission
	FROM #tmp_PermissionsWork
	
	EXEC sp_executesql @cmd;
	
	DELETE FROM #tmp_PermissionsWork 
	WHERE Permission = @cmd
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SampleProc')
BEGIN
	IF OBJECT_ID('tempdb..#tmp_PermissionsWork') IS NOT NULL DROP TABLE #tmp_PermissionsWork
	CREATE TABLE #tmp_PermissionsWork (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL 
		, Permission NVARCHAR(MAX) NULL
	)
	
	INSERT #tmp_PermissionsWork (Permission)
	SELECT state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + QUOTENAME(USER_NAME(grantee_principal_id)) + ';' FROM sys.database_permissions 
	WHERE major_id = OBJECT_ID('SampleProc')

	DROP PROCEDURE SampleProc
END
GO

CREATE PROCEDURE SampleProc
AS
...
GO

DECLARE @cmd NVARCHAR(MAX)

WHILE EXISTS(SELECT 1 FROM #tmp_PermissionsWork)
BEGIN
	SELECT TOP 1 @cmd = Permission
	FROM #tmp_PermissionsWork
	
	EXEC sp_executesql @cmd;
	
	DELETE FROM #tmp_PermissionsWork 
	WHERE Permission = @cmd
END
GO

To use this code, swap out ‘SampleProc’ with your stored procedure and put your procedure code in place of lines 17 – 20.

*This code is provided “AS-IS” with no warranty of any kind.