CODEWITHSUNDEEP
c#sql-serverassembliessqlclrencryption-asymmetric
cdub
cdub

Reputation: 25711

Creating asymmetric keys in SQL Server 2014 for unsafe assemblies

I have 2 DLLs signed in Visual Studio (VS) 2015. A former employee created an asymmetric key and login and I ran one of the assemblies in UNSAFE mode correctly.

I get the following error with the second one:

Msg 10327, Level 14, State 1, Line 27
CREATE ASSEMBLY for assembly 'TableFile' failed because assembly 'TableFile' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

I can't ask the former employee so how do I find out how to get this to run? I tried this too:

USE master; 
GO  

CREATE ASYMMETRIC KEY AProjectKey FROM EXECUTABLE FILE = 'E:\sqldlls\TableFile.dll' 
CREATE LOGIN AProjectLogin FROM ASYMMETRIC KEY AProjectKey ;  
GRANT UNSAFE ASSEMBLY TO AProjectLogin ;
GO

This gives the following errors:

Msg 15396, Level 16, State 1, Line 9
An asymmetric key with name 'AProjectKey' already exists or this asymmetric key already has been added to the database.

Msg 15151, Level 16, State 1, Line 10
Cannot find the asymmetric key 'AProjectKey', because it does not exist or you do not have permission.

Msg 15151, Level 16, State 1, Line 11
Cannot find the login 'AProjectLogin', because it does not exist or you do not have permission.

How do I get both these assemblies running in unsafe mode? Thanks in advance.

Upvotes: 2

Views: 2159

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Regarding those 3 error messages:

  1. The first could be due to the Asymmetric Key already existing, but under a different name. Keys and Certificates need to be unique in terms of their public key, not just the name (though that obviously needs to be unique as well). Each Key and Certificate has a hash of the public key which is referred to as the "thumbprint". The thumbprints of existing Keys / Certificates are checked when creating a new one and will prevent the creation, even of a differently named Key / Certificate, if the thumbprint already exists. That is what the error message means by "or this asymmetric key already has been added to the database".

    OR:

    It could mean that a different Asymmetric Key (i.e. different "thumbprint") exists in master, but with the name AProjectKey

  2. Because you couldn't create the same key under a different name, no Asymmetric Key exists with that new name, hence you can't create a Login from it (since, again, the CREATE ASYMMETRIC KEY statement failed).
  3. Because you couldn't create the Login, it doesn't exist to be granted any permissions.

Error # 2 helps narrow down the issue. If Error # 1 was caused by an existing Asymmetric Key having the same name but different "thumbprint", then you either would have been able to create the Login (if it did not already exist), or you would have gotten an error stating that the Login (i.e. "server principal") already exists. But the error is that an Asymmetric Key by that name cannot be found. This should mean that the Asymmetric Key itself does already exist, but under a different name. You can see what Asymmetric Keys have been created by executing the following:

SELECT * FROM sys.asymmetric_keys;

But that doesn't tell you which one came from that Assembly (or an Assembly signed with the same Strong Name Key used to sign this one). For that you need to know the "thumbprint", and for that you need to open a Command Prompt (preferably a "Developer Command Prompt" which Visual Studio sets up when installed, as it has the correct path set up upon opening it). Then, run the following:

CD /D E:\sqldlls\
sn -T TableFile.dll

You should see:

Public key token is XXXXXXXXXXXXXXXX

Copy and paste that XXXXX "token" (i.e. thumbprint) into the following query:

SELECT ak.[name], ak.[sid]
FROM   sys.asymmetric_keys ak
WHERE  ak.[thumbprint] = 0x{XXXXXXXXXXXXXXXX}; -- remove the { and }

Assuming that you get a row returned, we need to see if the Login exists. Simply trying to create a Login from the Asymmetric Key won't get us the Login's name if it does exist since you can only have 1 Login created per Key / Certificate, and the error message only reports back the name you are trying to create as the one that already exists, even though it can be a different name for the same Key. So, take the SID from the returned row and paste it in the following query:

SELECT sp.*
FROM   sys.server_principals sp
WHERE  sp.[sid] = 0x{SID_from_sys_asymmetric_keys}; -- remove the { and }

If no row is returned then you should create a Login from that Asymmetric Key.

At this point a Login should exist, so grant it the UNSAFE ASSEMBLY permission.

Now try creating the Assembly again.

Upvotes: 1

Related Questions