Articles

Current Articles | Categories | Search | Syndication

By Jonathan Kehayias @ Thursday, April 10, 2008 9:44 PM :: 14153 Views :: 1 Comments

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.

    USE master
    GO 
    -- 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'
    GO
     
  • 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
    GO
     
  • 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
    GO
     
  • Then add a Database User mapped to the new Login in your database.

    USE [SQLCLR_NET]

    GO
    -- Add a database user in the SQLCLR_Net Database for the Login
    CREATE USER [SQLCLR_ExampleLogin] FOR LOGIN [SQLCLR_ExampleLogin]
    GO
  • 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.

     

     

Posted @ Thursday, April 10, 2008 9:44 PM by Jonathan Kehayias
Previous Page | Next Page
Comments
By Keith Garcia on Friday, March 20, 2009
Great article. Provided exactly what I was looking for. Thank you.

You must be logged in to post a comment. You can login here

Survey

Which of the following CLR objects are you currently using in SQL Server?




Submit Survey  View Results

Links


Copyright 2007 by SQLCLR.net Terms Of Use Privacy Statement
Website graphics provided by Matt Green Designs
Page generated in 0.1092063 seconds.

All information and example code on this site is provided 'as-is', without any express or implied warranty. In no event will the authors be held liable for any damages arising from its use.

This site is in no way affiliated with Microsoft. Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.