SSDT deploy CLR issue: .Net SqlClient: Assembly could not be installed because existing policy would keep it from being used

Question

I have a DB in SQL 2008 R2, I created an SSDT SQL database project by importing an existing DB. This DB has some CLR routines that are using System.Drawing V2.0.

When deploying the project locally, I get the following error.

SQL72014: .Net SqlClient Data Provider: Msg 6586, Level 16, State 1, Line 1 Assembly 'SystemDrawing' could not be installed because existing policy would keep it from being used.

I'm not sure what policy to look for.

Answer 1

Your t-sql script should be the following

CREATE DATABASE [test]
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
GO
USE [test]
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON
GO
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
CREATE ASSEMBLY [System.Drawing]
AUTHORIZATION [dbo]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll'
WITH PERMISSION_SET = UNSAFE
GO

But note if you have installed .net 4 you have to use that one and its probably that you are using the wrong dll for the sql server.