Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to create a CLR assembly on a remote server with limited permissions

By Pavel Sinkevich,

Let's imagine this situation: there is a remote SQL Server. We have a SQL login for it. We don't have access to the file system of that server, and we need to install an assembly with CLR code in it on that instance. By the way, the assembly requires the external_access permission, and the trustworthy database option is forbidden to be turned on by company security policy.

Let's start from the beginning: how will we create an external_access assembly on a local SQL Server, where we have access to the file system?

Usually, we can create an assembly by using the CREATE ASSEMBLY structure:

CREATE ASSEMBLY myAssembly
FROM 'd:\Temp\myAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

By default, we will receive an error:

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

The easiest and most popular way to avoid this error is to use the TRUSTWORTHY option:

alter database TestDatabase
set trustworthy on
go

Then if we try to create assembly with PERMISSION_SET = EXTERNAL_ACCESS, it will be successfully created.

But using TRUSTWORTHY in such situation is a 'fast and dirty' method, which is not recommended for security reasons. In our imaginary case, it is also forbidden by the company security policy.

An alternate way is to use a ASYMMETRIC KEY. First our assembly must be strong name signed. If we create it with Visual Studio, we can sign assembly through the Properties->Signing menu:

 

After that, we create the asymmetric key in Master database using the signed assembly:

use master;
go
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'd:\Temp\myAssembly.dll'
go

If assembly was not strong name signed, we will receive an error here:

Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

Now we need to create a login. We can only create a login from an asymmetric key, if that key is in the Master database (that's why we had to use Master in previous step):

CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey 

Now, grant the necessary assembly permissions to that login:

GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin 

This permission will only work for our strong name signed assembly. Now we can create the assembly in our database:

use TestDatabase;
go
CREATE ASSEMBLY myAssembly
FROM 'd:\Temp\myAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

Ok. Now let's return to our imaginary task. We only have a SQL login to the remote server and no access to the file system. If we will try to create an asymmetric key or assembly from a file, we will receive this error:

Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation.

We can't ask the administrator to give us a Windows login with network access, so we could share the .dll file through the network. What can we do?

Surprising, but we can still add assembly to database using the SQL Server Management Studio UI like this. First, open object explorer. Expand the database name->Programmability->Assemblies. In right click context menu click "New Assembly" as shown below:

Find the path on your local computer, and press OK.

Great. But how will we create asymmetric key? The answer is simple: from the assembly itself! Here are the steps

  1. Add Assembly through SQL Server Management Studio to the Master database
  2. Create the Asymmetric key based on this assembly
         use master;
    go
    CREATE ASYMMETRIC KEY SQLCLRTestKey FROM ASSEMBLY myAssembly;
    go
  3. Delete the assembly from the Master database, as we don't need it here anymore
        drop assembly myAssembly;
    go
  4. Create a login based on the asymmetric key.
        CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey 
    go
  5. Grant the EXTERNAL ACCESS ASSEMBLY permission to this login. This permission will only work for our strong name signed assembly
        GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin 
    GO
  6. Add the Assembly in SQL Server Management Studio to the destination database with default options (permission set=safe)
  7. Alter this assembly with PERMISSION_SET = EXTERNAL_ACCESS. It will not raise an error, as there is an appropriate login on server
        use TestDatabase;
    go
    ALTER ASSEMBLY myAssembly WITH PERMISSION_SET = EXTERNAL_ACCESS
    go

That is the method to solve our initial task. It is not very elegant, but it works. I hope in future versions of SQL Server it would be easier to do the same thing.

 

Total article views: 2759 | Views in the last 30 days: 7
 
Related Articles
FORUM

Permissions to execute Assemblies...

Permissions to execute Assemblies...

ARTICLE

Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)

In the fourth level of our Stairway to SQLCLR series we take a detailed look at the EXTERNAL_ACCESS ...

FORUM

Creating assembly

I can no longer create assemblies on the sql server

FORUM

Cannot create assembly referencing system.core v3.5.0.0

Fail to create assembly using Linq

FORUM

Function of Login Created from Asymmetric Key in External Access Assemblies?

One of the ways of creating an EXTERNAL_ACCESS assembly is to: (1) CREATE ASYMMETRIC KEY from the...

Tags
sqlclr    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones