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.
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.
posted by Bharat T. Motwani at
6:02 AM
|
9 Comments
