When deploying a CLR Assembly that requires EXTERNAL_ACCESS or UNSAFE as the Permission Set you have the option of either Signing the Assembly with a strong key or setting the database to TRUSTWORTHY ON. Microsoft's recommendation is that you sign the assembly with a strong key as a best practice. To do this, simply follow these steps:
- Right click on the Solution in the Solution Explorer in Visual Studio 2005.
- Click Properties. (View Screenshot 1)
- When the Solution Properties Box comes up click Signing.
- Check the box for Sign the Assembly (View Screenshot 2)
- Click the DropDown and select <New>.
- Name the Key File, and provide a password to use it. (View Screenshot 3)
- When you click ok, a new PFK file will be added to your Solution. (View Screenshot 4)
- Build the solution.
- Copy the DLL to a folder on the SQL Server that will host this Assembly.
- Now create an Assymetric Key in the master database from the Assembly.
-- First Create the Asymmetric Key from the Assembly
CREATE ASYMMETRIC KEY SQLCLR_ExampleKey
FROM EXECUTABLE FILE = 'C:\SQLCLR_CSharp_Examples\bin\Release\SQLCLR_CSharp_Examples.dll'
- Now create a SQL Login from the Assymetric Key.
-- Create the Login from the Asymmetric Key
CREATE LOGIN SQLCLR_ExampleLogin FROM ASYMMETRIC KEY SQLCLR_ExampleKey
- Now grant either the CREATE EXTERNAL ACCESS ASSEMBLY or CREATE UNSAFE ASSEMBLY right to this user.
-- Grant the External Access Priviledge to the Login
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLR_ExampleLogin
- Then add a Database User mapped to the new Login in your database.
-- Add a database user in the SQLCLR_Net Database for the Login
CREATE USER [SQLCLR_ExampleLogin] FOR LOGIN [SQLCLR_ExampleLogin]
- Now go back to Visual Studio 2005, and set the Assembly Owner to the Database User that you just created (View Screenshot 5)
That is it. Now you can deploy the Assembly from inside of Visual Studio to the database as a signed assembly.