The login running all these commands is a member of
sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to.
EXTERNAL ACCESS ASSEMBLY permission has been granted to the login in this DB. Also tried everything below as the
sa login to no avail.
alter database test set trustworthy on go create assembly [icsharpcode.sharpziplib] from 'C:\Workspace\111\icsharpcode-SharpZipLib-4f2d664\bin\Release\ICSharpCode.SharpZipLib.dll' with permission_set = UNSAFE --< This works! go create assembly OutOfRowCompression from 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll' with permission_set = UNSAFE --< This fails! go
The last command fails with:
Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'OutOfRowCompression' failed because assembly 'OutOfRowCompression' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE 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 UNSAFE ASSEMBLY permission.
After getting the error I signed the assembly with a new asymmetric key
key1.pfx and added it to the master DB:
CREATE ASYMMETRIC KEY key1 FROM executable FILE = 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'
Verified that the key exists:
Created a login from this key and granted God mode:
create login clr_key1 from asymmetric key key1 go GRANT EXTERNAL ACCESS ASSEMBLY TO clr_key1 go
Still the same error from the assembly creation command.
Tried building the assembly
OutOfRowCompression with SAFE and UNSAFE access in Visual Studio 2012 - no difference.
I feel I have satisfied all requirement presented by the error message. It should work. What am I missing?