How to grant the database owner (DBO) the EXTERNAL ACCESS ASSEMBLY permission?

Question

When I try to create assembly in SQL 2008 from .Net assembly (.Net 3.5) I am getting the below error, error says that I have to set either of the below properties as true, how can I do that?

The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission as TRUE

The database has the TRUSTWORTHY database property on

The assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

The complete error is below,

CREATE ASSEMBLY for assembly 'SQLLogger' failed because assembly 'SQLLogger' 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.

Thanks in advance!

Answer 1

This worked for me:

EXEC sp_changedbowner 'sa'
ALTER DATABASE [dbase] SET trustworthy ON

and I also did this:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Answer 2

⚠ ‼ Please do not set TRUSTWORTHY ON ... ⚠

...unless absolutely necessary‼ And, it should pretty much never be "necessary", even when loading an Assembly that you did not build (you can always add another certificate, or worst-case: sign after loading into SQL Server), or when loading .NET Framework libraries that aren't "supported" and hence aren't already in SQL Server's CLR host (you can use the certificate they are signed with, or worst-case: sign after loading into SQL Server). Setting the database to TRUSTWORTHY ON opens up a security hole, and for more info on that, please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

Instead,

it is much better to do the following:

USE [master];

CREATE ASYMMETRIC KEY [SomeKey]
  AUTHORIZATION [dbo]
  FROM EXECUTABLE FILE = 'C:\path\to\Some.dll';

CREATE LOGIN [SomeLogin]
  FROM ASYMMETRIC KEY [SomeKey];

GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLogin]; -- or "UNSAFE" instead of "EXTERNAL ACCESS"

The above only needs to be done once per Instance, per key. So if you use the same snk / pfx file for all of your assemblies, then the steps shown above only need to be done once per SQL Server Instance; the number of Assemblies and databases containing those Assemblies does not matter. Or, if signing with a Certificate, then just replace ASYMMETRIC KEY with CERTIFICATE in the example code shown above.

This approach allows you to keep better security on the database (by keeping TRUSTWORTHY set to OFF) and allows for more granular control of which assemblies are even allowed to be set to EXTERNAL_ACCESS and/or UNSAFE (since you can separate by using different keys for signing and Logins based on those different keys).

However, if you must use the TRUSTWORTHY ON method, then the database owner does not need to be sa. The requirement is merely that the Login registered as the database owner has been granted either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (same two permissions shown above for the Asymmetric Key-based Login). Meaning:

USE [master];
GRANT UNSAFE ASSEMBLY TO [{Login-that-is-dbo-for-DB-containing-Assembly}];

For a more detailed walk-through of the security options, please see the following article that I wrote on SQL Server Central: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies).

---

For a detailed walk-through of how to automate this via Visual Studio / SSDT, please see the following 3 articles (a 3-part series), also on SQL Server Central:

• Stairway to SQLCLR Level 6: Development Tools Intro
• Stairway to SQLCLR Level 7: Development and Security
• Stairway to SQLCLR Level 8: Using Visual Studio to work around SSDT

Also, since writing those 3 articles, I have come up with an easier method using T4 templates but have not had time to write that up yet. When I do, I will update this answer with a link to that article.

UPDATE

SQL Server 2017 introduced a new complication in the form of a server-level configuration option named "CLR strict security". It is enabled by default and requires that ALL Assemblies, even those marked as SAFE, be signed with a Certificate or Asymmetric Key, have the associated Login, and that the Login has the UNSAFE ASSEMBLY permission granted (not good enough to grant EXTERNAL ACCESS ASSEMBLY). Please see my answer to the following S.O. question for more details on this new "feature":

CLR Strict Security on SQL Server 2017