Random Thunks: Deploying signed assemblies in a single script

I’ve been writing a set of routines to control my database backups recently and one part of this was a CLR User Defined Data Type I’d created that allows some rudimentary file handling (with the appropriate security). By it’s very nature this assembly required the EXTERNAL ACCESS privilege and this is where the head-scratching came about.

Liking clean install scripts with minimal payloads (single scripts are easier to check into Source Code and deploy across multiple environments) I needed some method of encapsulating the deploy and ensuring the appropriate asymmetric key, users etc. were created. However normally this would require either the key file or the assembly DLL to be copied along with the .Sql script – and this would break my 1 script rule! So next idea was to create the asymmetric key from the assembly directly within Sql server – but I couldn’t do that until I’d installed the assembly which would fail because it required the EXTERNAL ACCESS privilege; Thus leading to another game of Chicken and Egg.

The solution was remarkably obvious – use a temporary SAFE assembly to create the asymmetric key then dispose of it directly afterwards – after all the assembly is only required at this juncture to create the asymmetric key.

The first section of the script deals with previous execution cleanups etc:

 Use RandomThunks
 
------------/ Start stage 1 : Drop previous objects /--------------------------------------------------
 
If Exists ( Select 1 From sys.assembly_types As T Inner Join sys.schemas As S On T.schema_id = S.schema_id Where T.name = N'SqlFile' And S.Name = N'Maintenance.Database.Archives')
    Drop Type [Maintenance.Database.Archives].SqlFile;
 
If Exists (Select 1 From sys.assemblies Where name = N'RandomThunks.CLR.SqlServerFileUDT' and is_user_defined = 1)
    Drop Assembly [Skipjack.CLR.SqlServerFileUDT];
 
-- This next key should never exist when we run this as it's only use to create the asymmetric key before we Grant the External Access right to it.
 
If Exists (Select 1 From sys.assemblies Where name = N'TEMPORARY. DO NOT USE!' and is_user_defined = 1)
    Drop Assembly [TEMPORARY. DO NOT USE!];
    
If Exists ( Select 1 From sys.server_principals Where name = 'SqlServerFileUDTUser' )
    Drop Login SqlServerFileUDTUser;
    
If Exists ( Select 1 From sys.asymmetric_keys Where name = 'SqlServerFileUDTKey' )
    Drop Asymmetric Key SqlServerFileUDTKey;
 
------------/ End stage 1 : Drop previous objects /----------------------------------------------------
 

And the second part deploys the assembly with the requisite privilege:

 ------------/ Start stage 2 : Object Creation /--------------------------------------------------------
 
Declare @Assembly VarBinary(MAX);
Set @Assembly = 0x4D5A9000030000000...
 
If Not Exists ( Select 1 From sys.schemas Where Name = 'Maintenance.Database.Archives')
    Exec sp_ExecuteSql N'Create Schema [Maintenance.Database.Archives]';
 
-- Create a temporary assembly that we'll use to build the Asymmetric Key from
 
Create Assembly [TEMPORARY. DO NOT USE!] Authorization dbo
    From @Assembly
    With Permission_Set = Safe;
 
Create Asymmetric Key SqlServerFileUDTKey
    From Assembly [TEMPORARY. DO NOT USE!];
 
-- Drop the temporary assembly - it's use is over.
 
Drop Assembly [TEMPORARY. DO NOT USE!];
 
Create Login SqlServerFileUDTUser
    From Asymmetric Key SqlServerFileUDTKey;
 
-- This needs to be run at the Server level - encapsulating it in a sp_ExecuteSql allows the script to be DB name agnostic.
 
Exec sp_executeSql N'
    Use master;
 
    Grant External Access Assembly
        To SqlServerFileUDTUser;';
 
-- This is the real assembly
 
Create Assembly [Skipjack.CLR.SqlServerFileUDT] Authorization dbo
    From @Assembly
    With Permission_Set = External_Access;
 
Create Type [Maintenance.Database.Archives].SqlFile
    External Name [RandomThunks.CLR.SqlServerFileUDT].SqlFile;
 
------------/ End stage 2 : Object Creation /----------------------------------------------------------
 