How to add a signed CLR assembly?

Question

I'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly.  I can't figure out how to get this to work from the SSDT project.

Answer 1

In the project properties, on the SQLCLR tab there is a "Signing..." button.

---

-GertD @ www.sqlproj.com

Answer 2

I did this already, which does create a signed assembly... however, it doesn't get deployed to SQL server properly.

In order to deploy a signed assembly, the corresponding keys need to be created as well, which the SSDT project doesn't do.

Here's an MSDN blog article that describes the process to manually add a signed CLR:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx

According to the article, an asymmetric key on the database is generated from the dll file.  However, SSDT doesn't use a dll file and instead serializes the dll as a string and uses that as part of the SQL deployment file.  

CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'C:\CLR_SP\bin\Debug\CLR_SP.dll'

Answer 3

Hi Makoto,

Unfortunately, there isn't a straight forward way to add an assembly marked with EXTERNAL ACCESS if you want to do it using an asymmetric key.

What I had to do was the following:

1. Create a separate SSDT project for the master database.  In this project, I defined the asymmetric key and login like the following:

CREATE ASYMMETRIC KEY [AsymmetricKey1]
    FROM EXECUTABLE FILE = 'C:\Users\alex\Documents\Visual Studio 2010\Projects\SQLCLR1\SQLCLR1\bin\Debug\SQLCLR1.dll'
GO

CREATE LOGIN [CLR_SP_Login]
    FROM ASYMMETRIC KEY [AsymmetricKey1];
GO

2. Add a database reference (same server/different database) to the above project from the database project with the signed SQLCLR assembly.

3. Add a database user to the project with the SQLCLR assembly.

CREATE USER CLR_SP_User FOR LOGIN CLR_SP_Login 
GO

4. In the SQLCLR tab under project properties change the "Assembly Owner" property to the user created in Step 3.

5. Add a Predeployment script that grants the login (created in the master database) external access.

USE [Master]
 
GO
 
GRANT EXTERNAL ACCESS ASSEMBLY TO CLR_SP_Login
 
GO

Once all of the above was done, I was able to publish both projects to a SQL Server instance successfully.

Thanks,

a/c

Answer 4

Thank you! That was very helpful.

Answer 5

Hi Makoto,

Unfortunately, there isn't a straight forward way to add an assembly marked with EXTERNAL ACCESS if you want to do it using an asymmetric key.

What I had to do was the following:

1. Create a separate SSDT project for the master database.  In this project, I defined the asymmetric key and login like the following:

CREATE ASYMMETRIC KEY [AsymmetricKey1]
    FROM EXECUTABLE FILE = 'C:\Users\alex\Documents\Visual Studio 2010\Projects\SQLCLR1\SQLCLR1\bin\Debug\SQLCLR1.dll'
GO

CREATE LOGIN [CLR_SP_Login]
    FROM ASYMMETRIC KEY [AsymmetricKey1];
GO

2. Add a database reference (same server/different database) to the above project from the database project with the signed SQLCLR assembly.

3. Add a database user to the project with the SQLCLR assembly.

CREATE USER CLR_SP_User FOR LOGIN CLR_SP_Login 
GO

4. In the SQLCLR tab under project properties change the "Assembly Owner" property to the user created in Step 3.

5. Add a Predeployment script that grants the login (created in the master database) external access.

USE [Master]
 
GO
 
GRANT EXTERNAL ACCESS ASSEMBLY TO CLR_SP_Login
 
GO

Once all of the above was done, I was able to publish both projects to a SQL Server instance successfully.

Thanks,

a/c

Hi,

I have followed the steps you listed above but when publishing my SSDT project to a SQL Server 2012 SP1 instance it fails.

If I try publishing the SSDT project which has the signed SQLCLR assembly (Project A which has a database reference (same server, different database) to Project B. Project B has the create Login and Asymmetric Key objects as defined in your Step 1), it fails to publish as it trys to run the pre-deployment script (Step 5) and the login has yet to be created. The error message returned:

(46,1): SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 2 Cannot find the login 'BOB', because it does not exist or you do not have permission.

If I try to publish Project B (targeting the master database on the SQL Server instance) it fails with the following error:

(47,1): SQL72014: .Net SqlClient Data Provider: Msg 15208, Level 16, State 45, Line 1 The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

(47,0): SQL72045: Script execution error.  The executed script:

CREATE ASYMMETRIC KEY [AsymKey]

    AUTHORIZATION [dbo]

     FROM EXECUTABLE FILE = N'C:\Users\myuser\Documents\Visual Studio 2010\Projects\ExternalAssembly\ExternalAssembly\ExternalAssembly\bin\Debug\ExternalAssembly.dll';

The annoying thing is, I can publish successfully if the SQL Server instance already has the login, asymmetric key and assembly present (I'm in the process of migrating our existing database into a SSDT project) so my solution only fails to publish when it's to a new SQL Server instance.

I'm running SQL Server Data Tools 10.3.21208.0 and my SQLCLR Target Framework is .Net Fraemwork 3.5.

Any ideas what I might have missed so I can get this to work successully?

Thanks,

Answer 6

Have you made sure that you are creating the asymmetric key in the master database. I had his error until I realised that I had not used the master database. For my pre-deployment script I include:

USE [master];
GO

IF EXISTS(SELECT * FROM sys.server_principals
          WHERE name = 'SQLCLRLogin' )
  DROP LOGIN SQLCLRLogin
  
IF EXISTS(SELECT * FROM sys.asymmetric_keys
          WHERE name = 'SQLCLRKey')
  DROP ASYMMETRIC KEY SQLCLRKey

PRINT N'Creating [SQLCLRKey]...';

GO
CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE 
FILE = '\\MyPath\MyClr.dll'  

PRINT N'Creating [SQLCLRLogin]...';

GO
CREATE LOGIN SQLCLRLogin FROM ASYMMETRIC KEY SQLCLRKey   
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRLogin 

GO

USE [$(DatabaseName)];

GO

Answer 7

Just thought I'd add a link to a similar thread I just replied to in case the above doesn't work for someone 100%.  I was trying to avoid hard-coded file paths and found kind of a creative way around it I thought.

Hope this helps someone:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8098da5c-9e15-499f-8a4a-33d0e719e128/how-do-you-declare-objects-in-the-master-database?forum=ssdt

Answer 8

This is a far better method, as Ryan's solution uses a hexadecimal representation of the assembly and there is no need for file path references at all. Far more flexible solution, especially in an environment with multiple developers who may have the source code mapped to different paths.