Sql-server – Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

compressionsql serversql-clrsql-server-2012

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?

Best Answer

Your GRANT statement is incorrect. You only granted the ability to set Assemblies to EXTERNAL_ACCESS, not to UNSAFE. UNSAFE is less restricted than EXTERNAL_ACCESS, and so granting UNSAFE ASSEMBLY to a Login includes – implicitly – the EXTERNAL ACCESS ASSEMBLY permission.

You need to use:

GRANT UNSAFE ASSEMBLY TO [clr_key1];

P.S. You need to check the two DLLs you are importing for why they need to be marked as UNSAFE. If it is due to storing values in static variables then that could result in unexpected results if two sessions execute this code at the same time.

Related Question