How to Grant the Database Owner (Dbo) the External Access Assembly Permission

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

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:

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

Trustworthy is ON with UNSAFE assemblies not running

1. If TRUSTWORTHY is ON, then module signing isn't required.

   Also, assemblies do not contain .pfx files. The pfx file is a password-protected file that contains the private key. An assembly, if signed with a certificate (or snk / asymmetric key), contains the public key, which is used for validation.

2. No, you needn't worry about the certificate's expiration date (especially not when merely validating something that has been signed, which is all that is happening here).

   If you want to know if an assembly has been signed by a strong-name key (snk) / asymmetric key, just look at the assembly properties (e.g. SELECT name, clr_name FROM sys.assemblies;). If "publickeytoken" (in clr_name) has a value (i.e. not "null"), then the assembly has been strongly-named (i.e. signed with an asymmetric key).

   If you want to know if an assembly has been signed by a certificate, simply try to create a certificate from that assembly:

   CREATE CERTIFICATE [_TempTest] FROM ASSEMBLY [Microsoft.SqlServer.Types];
   -- DROP CERTIFICATE [_TempTest];
   

   If the assembly was signed with a certificate, the CREATE statement will succeed. Then, drop the certificate.

   If the assembly was not signed with a certificate, you will get the following error:

   Msg 15208, Level 16, State 31, Line XXXXX
   The certificate, asymmetric key, or private key file is not valid or
     does not exist; or you do not have permissions for it.
   

   Please note that it is possible to sign an assembly with an asymmetric key and one or more certificates.

How to grant unsafe assembly to a username found dynamically?

--You Have to use Dynamic SQL 

USE [master]
DECLARE @CurrentUserName VARCHAR(MAX),@String_SQL NVARCHAR(MAX)
SET @CurrentUserName = SUSER_SNAME()
SELECT @CurrentUserName
--GRANT UNSAFE ASSEMBLY TO @CurrentUserName

SET @String_SQL=''
SET @String_SQL =N'GRANT UNSAFE ASSEMBLY TO '+@CurrentUserName+''

PRINT @String_SQL
EXEC (@String_SQL)

Cannot add a SQLCLR assembly to database, Msg 300

I think you have a problem because the login is not a member of sysadmin. MSDN says, "If PERMISSION_SET = UNSAFE is specified, membership in the sysadmin fixed server role is required"

Update

As it was mentioned in the comment, it could be and should be done without assigning logins to sysadmin role . Unfortunately , I can't delete this answer since it's accepted, so in case anyone still has SQLServer 2008 , I recommend to refer to http://stackoverflow.com/a/38213540/577765 that has detailed explanation

Avoiding TRUSTWORTHY ON and PERMISSION_SET = UNSAFE using System.Net.Http

All .NET Framework libraries are already signed. The issue is that you need to create an Asymmetric Key or Certificate in [master] from the Public Key of either the Strong Name Key (SNK) or Certificate (CER) that was used to sign that Assembly (and often an Assembly has been both strongly named and signed with a certificate).

You can do the following to avoid TRUSTWORTHY (which is absolutely the right thing to do):

USE [master];

CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';

CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];

GRANT UNSAFE ASSEMBLY TO [MS.NETcer];

Then you can do this:

USE [SomeDatabase];

CREATE ASSEMBLY [System.Net.Http]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;

Beyond that, you will not be able to use PERMISSION_SET = SAFE with System.Net.Http due to the error message that you posted:

type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

since you cannot mark that static field as readonly and recompile, you are stuck with UNSAFE.

For more information on working with SQLCLR in general (including instructions on setting Visual Studio up to handle the certificate signing, and even having that work in SQL Server 2017, which is more restrictive than prior versions):

SQLCLR.org

What access does a Windows authenticated user in T-SQL need to create an External_Access or Unsafe Assembly?

First of all, did you mean to run this on master (as per the error message)?

To use TRUSTWORTHY, the user needs sysadmin as per here, but it is generally a bad idea to use TRUSTWORTHY.

To create an assembly with EXTERNAL_ACCESS the login needs EXTERNAL ACCESS ASSEMBLY permission on the server. To create the assembly as UNSAFE the login needs UNSAFE ASSEMBLY permission on the server. You can read more about that here.

Niels

Create assembly With PERMISSION_SET = unsafe throws an exception

If this is a long-term database (that is, one that's been around a while), one possibility for this error is that the Windows domain account to which the "dbo" database identity was originally mapped no longer exists. You might be able to determine if this is the problem by running the following in sys.databases:

SELECT name, suser_sname(owner_sid) FROM sys.databases

If the second column is null, the described problem may be at hand.

The information for this notion came from this post on the MSDN forums.

SQLCLR - How can I register referenced assemblies of my CLR assembly without setting TRUSTWORTHY ON?

You can sign the referenced Assembly(ies) with a Certificate that you create, load that Certificate into SQL Server before you attempt to create the Assembly in SQL Server, create a Login from the Certificate, and grant that Login the desired permission, either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY. And in fact, using this same Certificate for your own Assembly will allow it to be loaded from a VARBINARY literal / hex bytes string instead of requiring the DLL on the file system, which means this solution will work just fine in SQL Server 2017 and newer.

The following steps should do it:

1. Create the Certificate via command prompt:

   MAKECERT -r -pe -n "CN=some name,O=your company,C=US" ^
   -e "12/31/2099" -sv private_key_file.pvk ^
   public_key_file.cer
   

   You will be prompted via modal pop-up to enter in a password. You should get 2 prompts. Use the same password both times.

2. Combine the public key and private key files into a single PFX file via command prompt:

   PVK2PFX -pvk private_key_file.pvk -pi password ^
   -spc public_key_file.cer ^
   -pfx signing_certificate.pfx
   

3. Sign the DLL(s) via command prompt:

   signtool.EXE sign /v /p password ^
   /f signing_certificate.pfx ^
   NewtonSoft.dll
   
   signtool.EXE sign /v /p password ^
   /f signing_certificate.pfx ^
   MyProject.dll
   

4. Convert the public key into a VARBINARY literal so that it can be created from a SQL script with no external dependency on the file system, via command prompt (BinaryFormatter is an open source utility I wrote, available on GitHub, that converts the binary file into the hex bytes string, including wrapping at 80 characters per line so you don't have one stupid-long line):

   BinaryFormatter.exe public_key_file.cer ^
   create_certificate_script.sql 40
   

5. Convert each DLL into a VARBINARY literal so that it can be created from a SQL script with no external dependency on the file system, via command prompt:

   BinaryFormatter.exe Newtonsoft.DLL ^
   create_Newtonsoft_script.sql 40
   
   BinaryFormatter.exe MyProject.DLL ^
   create_MyProject_script.sql 40
   

6. In SQL Server, create the Certificate, the associated Login, and grant it the required permission:

   USE [master];
   
   CREATE CERTIFICATE [MySqlclrStuff]
   FROM BINARY = 0x{contents_of_create_certificate_script.sql};
   
   CREATE LOGIN [MySqlclrStuff]
   FROM CERTIFICATE [MySqlclrStuff];
   
   GRANT UNSAFE ASSEMBLY TO [MySqlclrStuff];
   

7. In SQL Server, in your target DB (not in master), create the two Assemblies:

   USE [SomeDB];
   
   CREATE ASSEMBLY [Newtonsoft]
   FROM 0x{contents_of_create_Newtonsoft_script.sql}
   WITH PERMISSION_SET = UNSAFE;
   
   CREATE ASSEMBLY [MyProject]
   FROM 0x{contents_of_create_MyProject_script.sql}
   WITH PERMISSION_SET = UNSAFE;
   

If you want to know how to automate this within Visual Studio / SSDT, please see the following blog post of mine detailing how to do that:

SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

---

Related Topics