Bharat T. Motwani

Wednesday, July 22, 2009

SQL CLR Stored procedure deployment

"CREATE ASSEMBLY for assembly 'CLRAssembly' failed because assembly 'CLRAssembly' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission."

Have you got this error while deploying an SQL CLR stored procedure ?
If yes, then look no further and check out the steps below to deploy a sample SQL CLR Stored procedure.


1/ Create a New SQL Server 2005 Database and name it "CLRTest"

2/ In order to set the correct permissions for the database to execute the SQL CLR stored procedure we need to set the Trustworthy property of the database to "true". By default the Trustworthy property is set to "false".
Execute the following statement to set the Trustworty property to "true"

ALTER DATABASE CLRTest SET TRUSTWORTHY ON

3/ Create a new user in SQL Server named "clrtestuser" and use the authentication type as "SQL Server Authentication".
Assign the db_owner permission to the "clrtestuser" on the "CLRTest" database.

4/ The next step is to grant the "external access assembly" permission to the "clrtestuser" user.
This can be done by running the following statement.

USE master
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO clrtestuser
GO

5/ Create a new "SQL Server project" in Visual Studio 2005 named "CLRAssembly"
Add a new user-defined function to the project which will have the following code.
"UserDefinedFunctions.vb"

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
<microsoft.sqlserver.server.sqlfunction()>_
Public Shared Function MyFirstCLRFunction() As SqlString
' Add your code here
Return New SqlString("Hello")
End Function
End Class


6/ Change the database settings for the project "CLRAssembly" to point to the "CLRTest" database.
To make this change in Visual Studio 2005 go to Project --> CLRAssembly Properties
Go to the "Database" tab and set the Connection String to point to the "CLRTest" database. The connection string should use SQL Authentication and the user name "clrtestuser"
Go to the "Database" tab and set the Permission Level to "External"

7/ If you want to debug the SQL CLR stored procedure from Visual Studio 2005, then assign the "sysadmin" role to the "clrtestuser".

8/ The SQL CLR stored procedure can now be deployed using the "Build-->Deploy CLRAssembly" menu.

9/ If you want to deploy the SQL CLR stored procedure manually then build the "CLRAssembly" project.
Log on to SQL Server using windows authentication and run the following statement.

DROP FUNCTION fnMyFirstCLRFunction
GO
DROP ASSEMBLY CLRAssembly
go
CREATE ASSEMBLY CLRAssembly
FROM 'C:\Projects\CLRAssembly\bin\CLRAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION fnMyFirstCLRFunction() returns nvarchar(200)
AS EXTERNAL NAME [CLRAssembly].[CLRAssembly.UserDefinedFunctions].[MyFirstCLRFunction]
GO


Note: If a database has been backed up and restored and the above steps for creating an assembly are run on that database, then the assembly will not be installed and it will throw an error.

11 Comments:

  • Actually, setting TRUSTWORTHY ON is the last thing you should do. Microsoft's own recommended best practice is to sign the assembly with a strong key, then import the key into the server, and use a certificate to allow the EXTERNAL_ACCESS. You can see an example of how to do this:

    http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx

    And some example Articles showing the right way to do this are:

    http://www.sqlservercentral.com/articles/SQLCLR/65656/

    http://www.sqlservercentral.com/articles/SQLCLR/65657/

    By Blogger Jonathan Kehayias (MCITP, MVP), At 8:10 AM  

  • magnificent publish, very informative. I wonder why the opposite
    experts of this sector don't notice this. You should proceed your writing. I'm sure, you have
    a huge readers' base already!

    My web site: Check This Site

    By Anonymous Anonymous, At 7:12 AM  

  • I'm not sure exactly why but this weblog is loading extremely slow for me. Is anyone else having this issue or is it a problem on my end? I'll check back later and see if the problem
    still exists.

    Feel free to visit my web-site; buy followers on instagram

    By Anonymous Anonymous, At 2:56 AM  

  • Hey there would you mind letting me know
    which hosting company you're working with? I've loaded your blog in 3 completely different web browsers and I must say this blog
    loads a lot quicker then most. Can you suggest a good hosting provider at a reasonable price?
    Thank you, I appreciate it!

    My site ... music download torrents

    By Anonymous Anonymous, At 12:05 PM  

  • Hello mates, pleasant article and fastidious arguments commented at this place, I am in
    fact enjoying by these.

    My blog post :: twitter retweets free way to get more instagram followers

    By Anonymous Anonymous, At 12:55 PM  

  • I'm not sure exactly why but this web site is loading incredibly slow for me. Is anyone else having this issue or is it a issue on my end? I'll check back later on and see if the problem still exists.


    my page: statics to marketing social media services

    By Anonymous Anonymous, At 1:58 PM  

  • Fantastic goods from you, man. I have understand your stuff previous to
    and you are just too fantastic. I really like what you've acquired here, really like what you're saying
    and the way in which you say it. You make it entertaining and you still care for to keep it smart.
    I can't wait to read far more from you. This is really a tremendous website.

    Here is my homepage - how to buy real cheap twitter followers

    By Anonymous Anonymous, At 5:24 PM  

  • My family members every time say that I am killing my time here at net, but I know I am getting know-how daily by reading
    such nice articles.

    Feel free to visit my web-site: buy bulk twitter followers

    By Anonymous Anonymous, At 3:39 PM  

  • Fantastic, I'm enjoyed read this post, Every time search this type of post. Really I enjoyed. This article is really very interesting and effective. I think its must be helpful for us. Thanks for sharing your informative. Buy Twitter Followers

    By Anonymous Buy Twitter Followers, At 5:26 AM  

  • Thanks for your marvelous posting! I quite enjoyed reading it, you happen to be a great author. I will remember to bookmark your blog and will eventually come back very soon. Go to best social plan for get more related topic. Have a nice evening!

    By Blogger Best Social Plan, At 12:00 PM  

  • Really good job.. Truly inspiring post as well as very much informative. it always very nice to following your posts. your posts are always very impressive & always very helpful for me. as following your previous comments i hope that you will be interested about the link bellow.. and it's also very informative for others also..thank you
    buy facebook likes

    By Blogger Shuvo Debnath, At 12:02 PM  

Post a Comment

Subscribe to Post Comments [Atom]



<< Home