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!

score 43 · Answer 1 · answered Jun 30 '14 at 10:37

43

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

answered Jun 30 '14 at 10:37

hoggar

3,241
5
25
32

@gotqn and hoggar: yes, this works, but should be avoided unless absolutely necessary. Please see my [answer](http://stackoverflow.com/a/32169896/577765) for more details. – Solomon Rutzky Aug 23 '15 at 18:29

Solomon Rutzky · Answer 2 · 2020-01-17T15:00:21.507

⚠ ‼ 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:

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

How can we use signed assemblies if our project requires (reference) .NET framework assemblies ? My project needs System.Runtime.Serialization but when I try to run `CREATE ASYMMETRIC KEY [SystemRuntimeSerializationKey] AUTHORIZATION [dbo] FROM EXECUTABLE FILE = 'C:\path\to\System.Runtime.Serialization.dll';`, I get this error: `An error occurred during the generation of the asymmetric key` — MaxiWheat, Mar 15 '16 at 17:25
@MaxiWheat Hey, I realize that you asked this separately here ( http://stackoverflow.com/a/36020428/577765 ), but to answer it here as well: I do not believe that is possible. Your custom Assemblies should be signed and using an Asymmetric Key, but I don't think there is anything you can do with unsupported .NET Framework DLLs or any 3rd Party DLL that is signed and you don't have access to the private key. I give more details in your other question that I linked to above. — Solomon Rutzky, Mar 15 '16 at 22:48
This really should be marked as the answer. The 'SET TRUSTWORTHY ON' solution is a quick-and-dirty or a last resort. — Frank Hagenson, Jun 23 '16 at 08:26
is there a way to automate this process adding it to the publish script that visual studio makes? — Gonza Piotti, Aug 09 '16 at 16:14
@GonzaPiotti Yes, there is. I just updated my answer with links to articles that I had not yet written when I posted this answer that walk you through one method of doing it. As I mention in the update, I had come up with an ever better / easier method since writing those articles but haven't had time to document it yet. I am working on that and will hopefully have it done soon. — Solomon Rutzky, Aug 09 '16 at 19:34
If you get error 'The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.' then verify SQL can access it by running xp_cmdshell 'DIR "C:\path\to\"' — brewmanz, Mar 23 '17 at 21:49

score 14 · Accepted Answer · edited May 23 '17 at 11:47

14

You must set these settings in the project file! When you right click on your project, click the Database Settings from the project configuration and select the miscellaneous tab. You should see something similar to what I have here: Project Settings

This is the same question as: Error Running CLR Stored Proc

edited May 23 '17 at 11:47

Community

1
1

answered Mar 26 '13 at 18:10

bleepzter

8,463
11
38
62

Also this applies only to SQL Server Database Project types, which I am assuming you are working with. – bleepzter Mar 26 '13 at 18:13
4

USE database_name GO EXEC sp_changedbowner 'sa' ALTER DATABASE database_name SET TRUSTWORTHY ON which is also outlined here: http://stackoverflow.com/questions/72281/error-running-clr-stored-proc – bleepzter Mar 26 '13 at 18:19
3

Which option (in the picture) are we supposed to choose? – as9876 Apr 13 '15 at 23:14
@AYS and bleepzter: yes, this works, but should be avoided unless absolutely necessary. Please see my [answer](http://stackoverflow.com/a/32169896/577765) for more details. – Solomon Rutzky Aug 23 '15 at 18:30
1

"Trustworthy" was the switch that made it work for me. – Mariusz Mar 14 '17 at 09:53
1

Trustworthy is not a fix. You are effectively telling SQL Server to access anything so long as it has a strong name or certificate, which defeats the purpose of a ASYMMETRIC key in the first place. – clifton_h May 06 '17 at 03:53
1

`Trustworthy` is **bad**, read @SolomonRutzky answer. Besides, if you detach DB and reattach it (or restore a backup), `Trustworthy` will be `Off` again, meaning users might be unhappy, because you forgot to set it to `Yes`. – Gabrielius Oct 05 '20 at 17:45

score 6 · Answer 4 · answered Oct 20 '14 at 12:38

6

Following code worked for me for integrated security:

ALTER DATABASE dtabasename SET TRUSTWORTHY ON;
GO

ALTER AUTHORIZATION ON DATABASE::dtabasename TO [DOMAIN\UserName]
GO

answered Oct 20 '14 at 12:38

Stefan Michev

3,938
2
32
29

Contango · Answer 5 · 2020-01-17T07:55:29.910

This works for:

Visual Studio 2015 Update 2.
Visual Studio 2017.
Visual Studio 2017 and SQL Server 2019 (thanks @Ramkumar Sambandam).

In your project settings, select "External Access":

On publish, the error message says that it cannot accept "EXTERNAL_ACCESS" unless the assembly is set to "Trustworthy".

So, in the project settings, set the assembly to "Trustworthy":

This meant that I was able to run a sample user defined function that listed files on the local hard drive.

If the security is still too restrictive, add the attribute DataAccess = DataAccessKind.Read to your UDF, e.g.:

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FindFiles", DataAccess = DataAccessKind.Read, TableDefinition = "FileName nvarchar(500), FileSize bigint, CreationTime datetime")]

Update 2017-07-02

On SQL Server 2016 + Visual Studio 2015, you might also have to do the following:

use master;grant unsafe assembly to [Domain\Username];
Run any programs (such as Visual Studio or any C# utilities) in Administrator mode to give them sufficient permissions to publish UNSAFE assemblies.

If nothing works, try connecting using username sa and your administrator password. This will always work, regardless of whether Visual Studio is run in Administrator mode or not.

Update 2020-01-17

Updated list of compatible VS + SQL Server combinations.

Looks like I accidentally down-voted this and didn't realize it until now. If you make a small edit and unlock my vote--I'll change that to an up-vote. — Jay Cummins, May 10 '18 at 13:00
@RamkumarSambandam Thanks! I've updated the answer to reflect this. — Contango, Jan 17 '20 at 07:55

score 3 · Answer 6 · edited Sep 30 '14 at 12:08

3

This is how I managed to make it work:

ALTER DATABASE databasename SET trustworthy ON

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

/
DROP ASSEMBLY assemblyname

GO


CREATE ASSEMBLY assemblyname
FROM 0x4D5A9000.....
WITH PERMISSION_SET = EXTERNAL_ACCESS

edited Sep 30 '14 at 12:08

Lucas Zamboulis

2,486
5
22
26

answered Sep 30 '14 at 11:05

Michael Nielo

31
1

Iman · Answer 7 · 2015-04-23T09:55:21.807

2

this single line solves the problem for me

use master;
grant external access assembly to [domain\username]

edited Apr 23 '15 at 09:55

answered Oct 03 '13 at 08:08

Iman

15,560
6
70
83

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

7 Answers7

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

Instead,

UPDATE

Update 2017-07-02

Update 2020-01-17

Linked

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

7 Answers7

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

Instead,

UPDATE

Update 2017-07-02

Update 2020-01-17

Linked

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