asymmetric key creation over network

Question

I'm attempting to add an assembly to a database in SQL2008 using an asymmetric key.

We're adding the assembly using a hex string (adding assemblies to servers through 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 a local instance however over the network we receive; The certificate, asymmetric key, or private key file does not exist or has invalid format.

I may be assuming wrongly that I should be adding the key first, should I be adding the assembly then doing something along the lines of CREATE ASYMMETRIC KEY ManagedAsymmetricKey FROM ASSEMBLY [workingDB].[dbo].[Managed] ?

Answer 1

You can use the following steps to get it working:

• run your create assembly statement with SAFE permission_set (even if the assembly needs UNSAFE for execution)
• create the asymmetric key from the assembly
• drop your assembly
• create a login from the asymmetric key

• grant the login unsafe assembly rights

  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

The FROM FILE = is always from the perspective of SQL Server. You would have copy the certificate to a local drive on the database server.