3rd party dll in SQL Server CLR

Question

I need to use a third party DLL in a trigger c# code in SQL Server CLR

But when I try to add reference it just shows some DLLs from SQL Server.

How can I add my third party dll to SQL Server?

Answer 1

You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog.

There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database:

ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Once this is done, Sql Server is CLR enabled. Next, you'll need to register your assembly:

CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [MyUser]
FROM 'C:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = SAFE

If this last script runs correctly, the assembly is now registered, and will appear in the Add References dialog.

What you will need to consider though, is the application security of your Sql Server CLR configuration:

1. Prefer to register an assembly as SAFE, only in exceptional circumstances should you use EXTERNAL_ACCESS or UNSAFE.
2. Don't expect to be able to do everything you can on Full-trust CLR (i.e., not the CLR hosted by Sql Server) - the SQLCLR is a sandboxed runtime.
3. Don't try and load assemblies dynamically, as Assembly.Load() is purposefully restricted.
4. You may need to ensure the 3rd party library is signed with a public key if you plan on using UNSAFE.
5. Code executing runs in the context of the identity of the service running Sql Server (I think!)
6. Database access made from a hosted assembly (e.g. via context connection = true;) runs in the context of the connected user, so you need to make sure you are aware what access that library has to your data.

Answer 2

I am assuming you are asking about alternatives to installing SQL CLR assemblies from Visual Studio.

Having the code in Visual Studio is not required.

Deploying CLR Database Objects on MSDN details the options, including SQL statements and deployment scripts.