none
CREATE ASSEMBLY failed: not authorized for PERMISSION_SET = EXTERNAL_ACCESS RRS feed

  • 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.

    Wednesday, April 19, 2006 8:27 PM

Answers

  • 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
    --- 

    Wednesday, April 19, 2006 10:05 PM
    Moderator
  • 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.

    Monday, July 21, 2008 2:08 AM
    Moderator

All replies

  • 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
    --- 

    Wednesday, April 19, 2006 10:05 PM
    Moderator
  • 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.

    Thursday, April 20, 2006 2:05 PM
  • 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?
    Monday, July 21, 2008 1:36 AM
  • 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.

    Monday, July 21, 2008 2:08 AM
    Moderator
  • 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.

    Wednesday, September 10, 2008 10:05 PM
  • Thanks Big B. It's working.

    Regards
    Sri
    CNU
    Wednesday, February 3, 2010 5:23 PM