- If you are using Visual Studio to build and deploy the SQL CLR solution ensure you have below settings
2) First things first, Ensure CLR Integration is enabled at instance level.
EXEC sp_configure 'show advanced options' , '1'; reconfigure; EXEC sp_configure 'clr enabled' , '1' ; reconfigure; EXEC sp_configure 'show advanced options' , '0'; reconfigure;
3) Ensure Appropriate CLR Integration Code Access Security levels are set when creating assembly’s in SQL Server
CREATE ASSEMBLY [SOAPSQLSPCLR] AUTHORIZATION [dbo] FROM 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WMVDSQL01\MSSQL\Binn\SQLCLRSoapConsumer.dll' WITH PERMISSION_SET = [EXTERNAL_ACCESS,SAFE,UNSAFE] GO
- SAFE : Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry
- EXTERNAL_ACCESS: These assemblies have the same permissions as SAFE assemblies, with the additional ability to access external system resources such as files, networks, environmental variables, and the registry
- UNSAFE: unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can also call unmanaged code.
Please refer to msdn documentation here https://msdn.microsoft.com/en-us/library/ms345101.aspx
4) Ensure the database is setup correctly
If you are using assemblies with External access and Unsafe the database property TRUSTWORTHY needs to be turned on.
USE database_name GO EXEC sp_changedbowner 'sa' ALTER DATABASE database_name SET TRUSTWORTHY ON
Refer to MSDN documentation on TRUSTWORTHY https://msdn.microsoft.com/en-us/library/ms187861.aspx
