CREATE ASSEMBLY failed: not authorized for PERMISSION_SET = EXTERNAL_ACCESS

Question

CREATE ASSEMBLY ClrSql from 'C:\ClrSql.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS

gives me this . . .

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'ClrSql' failed because assembly 'ClrSql' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS 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 EXTERNAL ACCESS ASSEMBLY permission.

How do I allow EXTERNAL_ACCESS and UNSAFE permissions? The property appears grayed out, and I am the dbo.

Answer 1

Hi,

the quickfix would be to set the database to trustworthy:

ALTER DATABASE SomeDatabase SET TRUSTWORTHY ON

Another option would be to make the assembly conformable to the requirements as mentioned in the error message.

 

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
--- 

Answer 2

Thanks.  I'm teaching myself CLR stored procs and SQL Server 2005 at the same time, so I have a hard time telling if the problem is my ignorance of .NET or SQL admin.

Making trusted assemblies is on the "to-do" list.

Answer 3

Thanks very much. The command 'ALTER DATABASE SomeDatabase SET TRUSTWORTHY ON' fixed the problem. It is strange that the error message came up in the first place as last week everything was working and this week it's not. Do you know what changes could cause this error to come up?

Answer 4

If it worked last week, were you cataloging the Assembly as SAFE?  This message wouldn't be thrown for a SAFE assembly.  Changing to a EXTERNAL_ACCESS or UNSAFE CAS requires additional trusts be setup in the database engine since these CAS levels allow operations outside of SQL Server or operations that could be potentially hazardous to SQL Server.

 

However, setting trustworthy on is not a recommended best practice.  It is better to instead sign the assembly with a certificate.

Answer 5

Do the following:

 

1. Sign your dll with strong name using Signing tab from your Visual Studio project properties window. You can create new .pfx file from there.

2. Build your CLR assembly with Pemission Level: External or Unsafe. Do not deploy yet.

 

3. In SLQ Management Studio open New Query and execute:

 

USE master

GO

CREATE ASYMMETRIC KEY AsymmetricKeyName FROM EXECUTABLE FILE = '{put your .dll file path here}'

CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY AsymmetricKeyName

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin

GO

 

Be sure that SQL Service account has access to your dll file folder.

New login with name CLRLogin will be created. May be is possible to use exsisting login but not sure how yet.

4.Go back to your Visual Studio project and deploy assembly.

 

I think that is all and it work for me.

Answer 6

Thanks Big B. It's working.

Regards
Sri

---

CNU