4

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?

  • Just out of curiosity, what particular feature of SharpZipLib are you using? – Solomon Rutzky Apr 13 '17 at 19:01
7

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.

  • Due to this: Msg 6216, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because type "ICSharpCode.SharpZipLib.BZip2.BZip2OutputStream" in safe assembly "ICSharpCode.SharpZipLib" has a finalizer. Finalizers are not allowed in safe assemblies. – ajeh Apr 13 '17 at 21:19
  • @ajeh are you using their BZip2 class? If not then you should be fine. Of course, if you aren't using it then you might even be able to update the source code to remove the finalizer / bzip2 stuff and recompile and use in EXTERNAL_ACCESS. – Solomon Rutzky Apr 13 '17 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.