Create an asymmetric key over a network

Question

I am trying to add an assembly to a database in SQL2008 using an asymmetric key .

We add the assembly using a hexadecimal string (we add assemblies to the servers using sql queries only)

USE [master] GO IF NOT EXISTS (SELECT * from sys.asymmetric_keys where name = 'ManagedAsymmetricKey') BEGIN CREATE ASYMMETRIC KEY ManagedAsymmetricKey FROM FILE = 'C:\Managed.dll' CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY ManagedAsymmetricKey GRANT UNSAFE ASSEMBLY TO CLRLogin END GO USE [$dbName] GO CREATE ASSEMBLY [Managed] AUTHORIZATION [dbo] FROM 0x4D5A.... WITH PERMISSION_SET = UNSAFE GO 

This will work on the local instance, but on the network we receive; The certificate, asymmetric key, or private key file does not exist or has invalid format.

Perhaps I am mistaken that I must first add the key, should I add the assembly, and then do something along the lines of CREATE ASYMMETRIC KEY ManagedAsymmetricKey FROM ASSEMBLY [workingDB].[dbo].[Managed] ?

Answer 1

You can use the following steps to make it work:

• run the assembly creation instruction with SAFE permission_set (even if the assembly needs UNSAFE to execute)
• create an asymmetric key from the assembly
• remove your assembly
• create login with asymmetric key

• grant rights to unsafe login assembly

   CREATE ASSEMBLY [Managed] AUTHORIZATION [dbo] FROM 0x4D5A.... WITH PERMISSION_SET = SAFE CREATE ASYMMETRIC KEY ManagedAsymmetricKey FROM ASSEMBLY [Managed] DROP ASSEMBLY [Managed] CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY ManagedAsymmetricKey GRANT UNSAFE ASSEMBLY TO CLRLogin 

Answer 2

FROM FILE = always from the perspective of SQL Server. Copy the certificate to a local disk on the database server.