How to deploy CLR Stored Procedure with external access

CLR Stored Procedures can be deployed within the Visual Studio environment. However CLR-SP has no access to external sources by default. You must give exclusive permission to your CLR-SP in your database. I did not manage to deploy SP in the Visual Studio.Therefore i choose to deploy my sp within the SQL Server after building it with Visual Studio.The following steps explain how to configure SQL Server for external access:

1)Enable CLR:

SP CONFIGURE 'clr_enabled', 1

GO

RECONFIGURE

GO

OR

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

2)Set database trushworthy:

ALTER DATABASE master SET trustworthy ON

3)Authorization:

If database is authorized by another user you should run this statement. Dont write your db name or user name with ' '

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'user name'

OR

ALTER AUTHORIZATION ON DATABASE: 'db name' TO 'DOMAIN/user name'

In some cases you may wonder who is the owner of a database. You can use the following function to learn SID and name:

SELECT SD.[SID]

,SL.Name as [LoginName]

FROM master..sysdatabases SD inner join master..syslogins SL

on SD.SID = SL.SID

Where SD.Name = 'db name'

4)Create Assembly from dll file produced in .NET:

CREATE ASSEMBLY 'file name'

from 'dll path'

WITH PERMISSION_SET = EXTERNAL_ACCESS

5)Create Stored Procedure using the assembly file above.

CREATE PROCEDURE 'procedure name'

@var1 NVARCHAR(128)

AS

EXTERNAL NAME 'assembly name'.'project namespace'.'function name'

Web services may require more than one assembly reference. For instance XML.Serialization is one of the reference that is automatically added to the project when a web service is used. Therefore you may need to consider additional assemblies. Since i do not want to deal with such references, i used httprequest object to consume web service.

Sources:

http://kevine323.blogspot.com/2011/03/database-owner-sid-recorded-in-master.html

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/