In the previous Level—Level 3: Security (General and SAFE Assemblies)—we started taking a focused look into the Security mechanisms in place for CLR code running within SQL Server.  We talked about Code Access Security, Programming Model Restrictions, Host Protection Attributes, and the PERMISSION_SET property of Assemblies. Whereas we had stuck to the PERMISSION_SET of SAFE, we will now turn our attention to the EXTERNAL_ACCESS and UNSAFE levels. These levels allow for doing some interesting things, such as accessing external resources, but do require that some additional steps be taken before any Assemblies can be set to either EXTERNAL_ACCESS or UNSAFE. And, if we do reach outside of SQL Server, then we have a choice as to who the user performing the action appears to be to any particular external resource.

Less Restricted Security

Assemblies that have a PERMISSION_SET of SAFE cannot interact with the outside system nor can they even interact with SQL Server outside of the current session.  While there is quite a bit of functionality that can be accomplished in SAFE mode, there are some very interesting, and useful, things that can be done when an Assembly is marked as either EXTERNAL_ACCESS or UNSAFE. Before we get into specifics, let’s quickly review the distinction between the three PERMISSION_SET options.  The following level descriptions (also shown in Level 3) are taken from the “CLR Hosted Environment” page (emphasis added):

• SAFE is the most reliable and secure mode with associated restrictions in terms of the allowed programming model. SAFE assemblies are given enough permission to run, perform computations, and have access to the local database. SAFE assemblies need to be verifiably type safe and are not allowed to call unmanaged code
• EXTERNAL_ACCESS provides an intermediate security option, allowing code to access resources external to the database but still having the reliability guarantees of SAFE.
• UNSAFE is for highly trusted code that can only be created by database administrators. This trusted code has no code access security restrictions, and it can call unmanaged (native) code.

The EXTERNAL_ACCESS level allows for some access of some external resources. The chart below details what can be done in which particular resource. But certain resources still require the UNSAFE level. For example, we can see that “RegistryPermission” allows for read-only access when using the EXTERNAL_ACCESS level, which means that writing to the Registry would require that the Assembly be set to UNSAFE. The UNSAFE level allows one to do almost anything outside of dynamically loading another Assembly. But all of the stated restrictions of the SAFE and EXTERNAL_ACCESS levels are removed such that you can do things like: store information in static variables, use finalizers, call unmanaged code, write to the Registry, etc. Of course, while it might be obvious, it should still be noted that just because an Assembly is marked as either EXTERNAL_ACCESS or UNSAFE does not imply that any code within that Assembly is requiring that level. However, it is best if Assemblies are set to the most restrictive level that still allows them to accomplish what they need to do.

The following chart summarizes the restrictions and permissions granted for the EXTERNAL_ACCESS and UNSAFE permission sets. The information is taken directly from the MSDN pages on Code Access Security and Programming Model  Restrictions.

Accessing External Resources

If any SQLCLR code is going to access resources outside of SQL Server (e.g. File System, Network, EventLog, etc.) then a Windows / Active Directory security context will be used. There are two options, each with its benefits and drawbacks: the SQL Server service account or the current user context (assuming that the current user context is: not the result of an EXECUTE AS clause or statement, and is a Windows Login).

By default external calls are made with a security context of the logon account for the SQL Server process. This is similar to xp_cmdshell. There are no specific limitations in using the security context of the SQL Server process, and in some cases there might not be a choice given some of the limitations of the other method. However, having all external access going through this account means that it needs to have access to any resources that it needs to interact with, such as read and/or write access to particular areas of the File System or Registry or something else. Given that it is generally best to restrict access for the SQL Server service account as much as possible, it is not ideal to increase that access.

It is possible to allow non-sysadmin Logins to run xp_cmdshell in the security context of a single proxy account—a Windows account that is hopefully only allowed to do what it needs to do. This is certainly a step in the right direction, but SQLCLR can do better. SQLCLR allows for code to impersonate the current Login and use its security context. Unlike the single proxy account for xp_cmdshell (or even multiple stored Credentials for SQL Server Agent), this allows Active Directory / NTFS permissions to be managed more naturally and allow only a subset of Logins that have permission to run the SQLCLR code from accessing certain external resources though that code. This is a very nice feature, but it has some restrictions:

• You cannot interact with SQL Server in-process: the Context Connection and SqlContext are not available. All database interaction has to be done through a regular / external connection which means no access to the current Session / SPID and no ability to stream a result set back from a Stored Procedure or Table-Valued Function (though to be fair, depending on the operation it might be possible to end the impersonation before sending any results backs).
• Only Windows Logins can use impersonation as SQL Server Logins and Users do not have a Windows SID. SQL Server-based accounts have to use the default security context of the SQL Server service account.
• By default, impersonated credentials can only be passed to a single machine. This is by design as a security feature. Anyone who has set up Linked Servers across more than two servers, using the “current security context” option, and has attempted to query from Server A to Server B which in turn accessed Server C, has likely encountered the “Login failed for Anonymous/NT AUTHORITY” error. This is commonly known as the Kerberos “double-hop” limitation and the only remedy is to configure Delegation, which allows a Windows account to pass its security credentials beyond the initial “hop”. For detailed information regarding Impersonation and Delegation, including an excellent article here on SQL Server Central regarding how to get Delegation working, please see the “Additional Reading” section at the end of the article.

The example script, StairwayToSQLCLR-04-04-ExternalAccessTests.sql, illustrates how both the standard SQL Server service account and impersonation work with both internal (i.e. Context Connection and SqlContext) connections and regular / external connections.

Restricting Users Outside of SQL Server

It was stated in the previous level that there are three areas where security can be controlled: the code itself, the SQLCLR objects within SQL Server, and users outside of SQL Server. Given that we were only looking at SAFE Assemblies and could not reach outside of SQL Server, we skipped the section on Restricting Users Outside of SQL Server. But now that we are experimenting with EXTERNAL_ACCESS and UNSAFE Assemblies we can discuss that third area.

If CLR code accesses any external resources then Active Directory permissions come into play. By default, external resources are accessed within the context of the account running the SQL Server process.  However, it is possible to use Impersonation which works for Windows Logins, in which case the security context will be the Windows Login that executed the SQLCLR code. In either case, Active Directory / NTFS permissions control what can be done when reaching outside of the database. Meaning, if the Logon As account for the SQL Server process is properly restricted to only the directories that SQL Server itself needs to use, then having a SQLCLR assembly marked as EXTERNAL_ACCESS that has code in it that can delete files does not mean your system has a security hole and is unsafe: the account doesn’t have access to your entire network and/or File System. And if you are using a Windows Login and the SQLCLR code is using impersonation such that the external access is being done via your Windows Login and not the service account for SQL Server, you are still confined to the local machine (or one external connection, such as to SQL Server). In this case of the code using Impersonation, if you try to make another “trusted” connection to another machine you will get dreaded “Login failed for Anonymous” error that usually indicates running into the Kerberos double-hop limitation. You can get around this by enabling Delegation, but that has to be configured though Active Directory by an administrator.

Requirements for EXTERNAL_ACCESS and UNSAFE modes

In order to run CLR methods that require the Assembly have a PERMISSON_SET of either EXTERNAL_ACCESS or UNSAFE, one of the following two sets of conditions must be met:

• The database owner has the EXTERNAL ACCESS ASSEMBLY and/or UNSAFE ASSEMBLY permission(s)
• The database is marked as TRUSTWORTHY ON

-- or --

• The Assembly is signed

• An Asymmetric Key or Certificate exists (in the [master] database) that was created from the same private key that was used to sign the DLL / Assembly
• A Login (not User) exists that was created from that Asymmetric Key or Certificate
• The Asymmetric Key / Certificate –based Login has the EXTERNAL ACCESS ASSEMBLY and/or UNSAFE ASSEMBLY permission(s)

Changing the TRUSTWORTHY setting of the database (it is OFF by default) is a simple, one-step ALTER DATABASE command so it is likely the more commonly used method of allowing EXTERNAL_ACCESS and UNSAFE Assemblies. But easier doesn’t always equate to better and in this case it is preferred to stay away from the easier method as setting TRUSTWORTHY to ON has database-wide security implications. You should only use the SET TRUSTWORTHY ON method when you are initially developing / prototyping something new.

But even then it would be wise to get in the habit of creating the Asymmetric Key and Login the first time you load your Assembly and saving the SQL script of those commands so that you can have your deploy / CI process run them automatically as code is promoted to higher environments through the development life-cycle. If you are going to sign your Assemblies with the same certificate then you don’t need to go through that process again. And if you will have different certificates for different Assemblies, then the SQL script you develop the first time will serve as a template for newer code since you will just need to change a few things: Assembly name or DLL location, Asymmetric Key name, and Login name.

It is important to keep in mind that prior to going through this exercise—creating the Key, the Login, and issuing the GRANT—it will seem more complicated than it truly is and hence it will be tempting to just go with the TRUSTWORTHY ON method. Fortunately, you will see in the examples for this level that choosing the preferred, more secure method is not too complex or time-consuming, and in fact you can just copy / paste most, if not all, of what you need. The only step to doing the Asymmetric Key-based Login method that is not covered in this level is signing the .Net Assembly / DLL with a certificate and password. The signing of the .Net code step will be covered in the next level when we start looking at development and Visual Studio. For now, if you have dabbled in SQL CLR code before or are working with developers who are submitting code, just make sure that the DLL was signed and protected with a password (the “Signing” option is found on the “SQLCLR” tab of the project properties within Visual Studio).

The advantages of the Asymmetric Key-based Login method are that: the database itself is not made less secure, you can manage permissions for Assemblies individually or in groups, and per each Assembly or group of Assemblies you can specify a maximum PERMISSION_SET.  To better explain that last benefit, consider that by setting TRUSTWORTHY to ON, you are enabling all Assemblies to be able to be set to EXTERNAL_ACCESS or UNSAFE; there is no fine-grained control.

On the other hand, by keeping TRUSTWORTHY set to OFF and setting up at least one Asymmetric Key-based Login, you can let one Assembly be set to either SAFE or EXTERNAL_ACCESS, another group of three Assemblies can be set to any of the three levels including UNSAFE, and the rest of the Assemblies that don’t have an associated Asymmetric Key-based Login will be stuck at SAFE—they can’t be set to either EXTERNAL_ACCESS or UNSAFE. Several of the examples illustrate this granular control.

Additional Notes

The following are a few notes related to subjects discussed above:

• The AUTHORIZATION clause of the CREATE ASSEMBLY statement does not need to be set to any particular User. This clause is not related to security but instead to processing / memory isolation as it determines which App Domain (to be discussed in a future article) the Assembly belongs to: App Domains are per-Database, per-User. So, there is no need—Security-wise—to create a User based on the Login. This is shown in the example scripts by each of the Assemblies being set to AUTHORIZATION [dbo].
• The UNSAFE ASSEMBLY server-level permission that can be granted to a Login implicitly includes the EXTERNAL ACCESS ASSEMBLY permission. There is nothing wrong with granting both permissions to a Login, but technically, if a Login has UNSAFE ASSEMBLY, then it doesn’t need EXTERNAL ACCESS ASSEMBLY in order to set any Assembly as EXTERNAL_ACCESS. This is shown in the StairwayToSQLCLR-04-07-UnsafeTests2.sql example script.
• Code that requires a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE, even if validated during CREATE ASSEMBLY, will always re-check to make sure that the conditions for being set to one of those two levels are still valid. This means that if the conditions that allowed for an Assembly to be set to either EXTERNAL_ACCESS or UNSAFE are no longer true, then any code that required either of those security levels will stop working immediately upon those conditions becoming false; there is no such thing as grandfathered permissions. This is shown in the StairwayToSQLCLR-04-08-PointsOfInterestTests.sql example script.
• While it is possible to create a database User based on an Asymmetric Key or Certificate, doing so will not allow for any Assemblies to be set to EXTERNAL_ACCESS or UNSAFE, even if you specify that User in the AUTHORIZATION clause of CREATE ASSEMBLY (remember, that clause affects process isolation, not security). The EXTERNAL ACCESS ASSEMBLY and UNSAFE ASSEMBLY permissions are at the server level and can only be assigned to a Login (server-level), not to a User (database-level).

Examples

The example code—in a zip file (StairwayToSQLCLR-04-ExampleCode.zip) attached to this article at the bottom in the “Resources” section—consists of:

• A folder named “Level-04” containing:
• Ten SQL scripts
• A subfolder named “Assemblies” containing:
• Three DLLs
• A subfolder named “SourceCode” containing:
• Three C# .cs files

Example Setup

If you have not already done so via prior “Stairway to SQLCLR” level examples, please create a C:\TEMP\StairwayToSQLCLR folder either in Windows Explorer or from a command prompt with the following command:

MKDIR C:\TEMP\StairwayToSQLCLR

Extract the contents of the zip file, starting with the “Level-04” folder itself, into C:\TEMP\StairwayToSQLCLR such that you now have C:\TEMP\StairwayToSQLCLR\Level-04\... .

The relevant portions of the source code are shown in each of the example summaries below. For those who want to see all of the C# source code, there are three files in the C:\TEMP\StairwayToSQLCLR\Level-04\SourceCode folder. The Visual Studio solution file and project files that contain the configuration for building and signing the source code are not included; the purpose of this article and the examples contained herein is to gain a better and clearer understanding of how security works for SQLCLR code, not how to build SQLCLR code. The focus here needs to stay on studying the behavior and we will start covering development in the next level. Along those lines, the C# source code will only be explained in brief.

Run the first script, StairwayToSQLCLR-04-01-CreateDatabase.sql, even if you already have the test database, [StairwayToSQLCLR], created from prior level examples, and then close it. This script also creates a database User, [TestUser], which is used in the StairwayToSQLCLR-04-04-ExternalAccessTests.sql script.

Run the second script, StairwayToSQLCLR-04-02-EnableCLR.sql, and then close it. This script will enable “CLR Integration” if it is not already enabled. It will also create a User-Defined Function in the [StairwayToSQLCLR] database which returns a BIT value denoting whether or not this script enabled “CLR Integration” or not.  This function, [DidWeEnableCLR], is used in the cleanup script, StairwayToSQLCLR-04-09-DisableCLR.sql, as a means of determining whether or not to actually disable “CLR Integration”. If “CLR Integration” was enabled prior to running the StairwayToSQLCLR-04-02-EnableCLR.sql script, then the [DidWeEnableCLR] function will return 0 and “CLR Integration” won’t be disabled as it might be in use for something other than these examples.

Internal and External Connections, and Impersonation

This first set of tests explores the differences between the various ways in which we can interact with the system. We will look at the internal connection (i.e. Context Connection—the focus of Level 3) as well as external connections, both Impersonated and non-Impersonated.

Run the third script, StairwayToSQLCLR-04-03-CreateObjectsForExternalTests.sql, which sets up the requirements for the first two tests, and then close it. This script creates the [StairwayToSQLCLR-04-Security2_ExternalAccess] Assembly, the [StairwayToSQLCLR_04_WhoAmI] CLR Stored Procedure, and the [WhoAmI] T-SQL Inline TVF. The [WhoAmI] TVF displays current process and connection info, and is defined as follows:

CREATE FUNCTION [dbo].[WhoAmI] ()
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT @@SERVERNAME AS [@@SERVERNAME],
              DB_NAME() AS [DB_NAME()],
              DB_ID() AS [DB_ID()],
              '---' AS [---],
              PROGRAM_NAME() AS [PROGRAM_NAME()],
              @@SPID AS [@@SPID],
              CONTEXT_INFO() AS [CONTEXT_INFO()],
              ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN()],
              '----' AS [----],
              SUSER_ID() AS [SUSER_ID()],
              SUSER_SID() AS [SUSER_SID()],
              SUSER_NAME() AS [SUSER_NAME()],
              SUSER_SNAME() AS [SUSER_SNAME()],
              SYSTEM_USER AS [SYSTEM_USER],
              '-----' AS [-----],
              USER_ID() AS [USER_ID()],
              USER_SID() AS [USER_SID()],
              USER_NAME() AS [USER_NAME()], -- same as CURRENT_USER
              SESSION_USER AS [SESSION_USER];

The CLR Stored Procedure, [StairwayToSQLCLR_04_WhoAmI], is nothing more than a simple wrapper around the T-SQL TVF, [WhoAmI]. It allows for testing various combinations of internal vs. external connection with impersonation vs. regular connections. The full C# code can be found in the C:\TEMP\StairwayToSQLCLR\Level-04\SourceCode\ExternalAccess.cs file. These two objects were used in the previous level’s examples.

The following section of code declares a variable to hold the connection string along with a default value. If a non-NULL, non-empty value for the @ServerName parameter is passed in, that value is concatenated into a connection string that uses a trusted connection (i.e. the “-E” option for SQLCMD.EXE). A hard-coded query is defined (_query) along with variables needed to connect to a server (_sqlConnection) and execute a query (_sqlCommand) that receives a result set (_dataReader).

The following section of code first determines whether or not to impersonate the security credentials of the user executing the code (the default credentials will be that of the Logon As account for the SQL Server service), based on the value passed in via the @Impersonate parameter. If @Impersonate is set to 1 then the code also checks to make sure that the user executing the code does in fact have security credentials that can be impersonated; Windows Logins have a security context while SQL Server Logins do not. If @Impersonate is set to 2 then the code skips the security credential existence check; this is a non-standard approach but is helpful here in that it allows us to see differences between the two types of Logins.

The connection to SQL Server is then opened. It is important to note that the call to Impersonate() needs to happen before the external resource is accessed (i.e. the _sqlConnection.Open() in this case). Once the connection has been made, the code will send a PRINT message if the @Message parameter has a non-NULL, non-empty value. We will see in a moment that SqlContext.Pipe is not available while Impersonating. The next if block turns off Impersonation if it is currently in use. Next execute the query via ExecuteReader() and get a pointer to the result set. The final step is to pass the result set pointer to SQL Server so it can start retrieving rows, if any exist, and sending them to the client.

Please note that when Impersonation is requested, it is enabled prior to making the connection to SQL Server, but disabled prior to executing the query. Once the connection to SQL Server is established, disabling Impersonation has no effect on that connection. At the same time, Impersonation has to be disabled before calling SqlContext.Pipe.Send() else an error will occur.

The following section of code does the cleanup of the resources. A finally block runs after the try, even if an error occurs. It is always import to clean up disposable objects so that the Garbage Collector can free up that memory. But it is especially important to close and free up external resources so that those network ports and file handles are released. Not doing so can lead to locked resources.

For example, if you open a file and do not make sure that it is closed in a catch or finally block and an error occurs, that file will be locked by the SQL Server process and won’t be released until you restart SQL Server or possibly cause the App Domain to end. While this cleanup is not functionality that we will be testing, it is important to show because it is all too common for people to copy and paste example code and not know what has been left out for the sake of convenience.

Open the StairwayToSQLCLR-04-04-ExternalAccessTests.sql script in SSMS and run the USE and SET statements towards the top of the script. Please note that this step will be the same for each of the following test scripts.

USE [StairwayToSQLCLR];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;

Run the following query to get a baseline set of values that we will use to compare against each of the subsequent "WhoAmI" tests. Record the values of the following fields: "PROGRAM_NAME()", "@@SPID", and "ORIGINAL_LOGIN". If you are using SSMS to run the tests, the "PROGRAM_NAME()" field should have the following value: Microsoft SQL Server Management Studio - Query

SELECT * FROM dbo.WhoAmI();

The following query makes an internal connection (i.e. "Context Connection = true;") which behaves like any regular T-SQL Stored Procedure or Function call:

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = NULL, @Impersonate = 0, @Message = NULL;

The values for those three fields should be the same; it is the same SPID so everything about it should be the same.

The following query is mostly the same as the previous query except that it also sends a message (similar to the T-SQL PRINT command) via SqlContext.Pipe.Send():

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = NULL, @Impersonate = 0, @Message = 'test';

The values for the fields should again be the same. But this time you can check the "Messages" tab to see the that value of @Message did come through. This test shows that when using the Context Connection, we have access to SqlContext.Pipe which is used to pass back messages as well as result sets.

Now let's try a regular, external connection which reaches outside of SQL Server and comes back in, just like any external client connection. If you are using a named instance (i.e. "ServerName\InstanceName" vs "ServerName") then you will probably need to replace all instances of '(local)' with '(local)\InstanceName' in this script and in scripts 05-TrustworthyVsAsymmetricKeyTests and 08-PointsOfInterestTests (e.g. "(local)\sqlexpress").

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 0, @Message = NULL;

You should get the following error:

That error is due to the Assembly still having a PERMISSION_SET of SAFE. Let's change that so the CLR code is allowed to reach outside of SQL Server:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

You should now receive the following error:

Msg 10327, Level 14, State 1, Line 1

ALTER ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' failed because assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' 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 error message is fairly clear in what the problem is and how to fix it (much more helpful than the typical .Net error message of "Object reference not set to an instance of an object"!). We will first try the easier approach:

ALTER DATABASE [StairwayToSQLCLR] SET TRUSTWORTHY ON;

And if we try to change the PERMISSION_SET now?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

This time it succeeds.

Now we can try connecting via an external / regular connection again:

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 0, @Message = NULL;

And now this query works as well. Take a look at the "PROGRAM_NAME()", "@@SPID", and "ORIGINAL_LOGIN" fields. The values for all three should be different from the initial run. The value for the "ORIGINAL_LOGIN" field will be the account that the "SQL Server" service (commonly MSSQLSERVER in the NT Services list) logs on as, and the value for the "PROGRAM_NAME()" field should be: .Net SqlClient Data Provider

Now that we are connecting remotely (even if it is to the local instance), do we still have access to SqlContext.Pipe?

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 0, @Message = 'test';

The result set should be the same as the previous query's but now the value of @Message should be displayed in the "Messages" tab. This means that we can use SqlContext.Pipe to pass back messages and result sets.

One of the interesting (and quite nice) features of SQLCLR is the ability to Impersonate the current users security context:

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 1, @Message = NULL;

The result set should show that:

• "@@SPID" is different than the initial run (it may or may not be the same as the previous run)
• "PROGRAM_NAME()" is still: .Net SqlClient Data Provider
• "ORIGINAL_LOGIN" should now be your account if you are using a Windows Login, else it will still be the SQL Server service's Log On account if you are using a SQL Server Login.

The ability to Impersonate for any Windows Login is something that cannot be done with xp_cmdshell, even with its optional, singular proxy account. This gives more flexibility in terms of managing permissions on those external resources.

Can we still pass back messages?

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 1, @Message = 'test';

You should now get the following error:

Data access is not allowed in an impersonated context.

This error is due to one of the restrictions when using Impersonation: no access to SqlContext.Pipe. Of course, you can still call the PRINT command in the query that is executed on the external connection. However, there is no work-around for passing back result sets while Impersonation is enabled.

Can the internal connection (i.e. same SPID) be used when Impersonation is enabled?

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = NULL, @Impersonate = 1, @Message = NULL;

You should get the same "Data access..." error that happened in the last query; if Impersonation is enabled, you cannot use the Context Connection.

Since most people are using a Windows Login (which is good), it would help to see how these connections behave when using a SQL Server login. While many systems are configured for "Windows Authentication" and not "SQL Server and Windows Authentication", we can fortunately use a Login-less User. And not cooincidentally, such a User was created in the first example script (the script that created the [StairwayToSQLCLR] database) via the following query:

CREATE USER [TestUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo];

Run the following query to temporarily assume the identity of the Login-less User:

EXECUTE AS USER = 'TestUser';

Now that we are acting as [TestUser], let's see how that shows up when we go back to the initial query and run the T-SQL function without going through the SQLCLR Stored Procedure:

SELECT * FROM dbo.WhoAmI();

The result set should show that the "@@SPID", "PROGRAM_NAME()", and "ORIGINAL_LOGIN" fields should have the same values as reported on the initial run. But if we take a look at the "SESSION_USER" field, the value should now be: TestUser

Let's try the SQLCLR Stored Procedure again, using the Internal / Context Connection:

EXEC dbo.StairwayToSQLCLR_04_WhoAmI @ServerName = NULL, @Impersonate = 0, @Message = NULL;

The result set should be exactly the same as you got when running the T-SQL function.

What if we try an external connection?

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 0, @Message = NULL;

The result set should be the same as the first time we ran this query, when we were not acting as [TestUser]; not being a Windows Login doesn't change how the CLR code interacts with external resources.

But what happens if we try to enable Impersonation?

EXEC dbo.StairwayToSQLCLR_04_WhoAmI
     @ServerName = '(local)', @Impersonate = 2, @Message = NULL;

You should get the following error:

Object reference not set to an instance of an object.

The error occurred because the current security context is not based on a Windows Login. In this scenario the WindowsIdentity property of SqlContext is null (i.e "no value", which is different than a database NULL which means "unknown") due to there not being a Windows SID. This is an easy error to avoid, but if you look back at the source code you will recall that setting @Impersonate to 2 bypasses the check, which was only done so that we could do this particular test.

The following command / query ends the temporary acting as [TestUser]:

REVERT;

Trustworthy DB Setting vs. Asymmetric Key-based Login

This set of tests shows the practical differences between the two methods of configuring SQL Server to allow Assemblies to be set to either EXTERNAL_ACCESS or UNSAFE. We will try setting the Assembly that was created for the prior set of tests, [StairwayToSQLCLR-04-Security2_ExternalAccess], as well as a new Assembly, [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck], to both EXTERNAL_ACCESS and UNSAFE under both configurations.

We will go over the source code of the new Assembly in the next section of tests. For our purposes here, we only need it to help us distinguish the differences between the two configuration methods. The new Assembly requires a PERMISSION_SET of UNSAFE just to be created while the other Assembly only requires EXTERNAL_ACCESS under certain conditions.

Open the StairwayToSQLCLR-04-05-TrustworthyVsAsymmetricKeyTests.sql script in SSMS and run the USE and SET statements towards the top of the script.

In the previous set of tests we set the [StairwayToSQLCLR-04-Security2_ExternalAccess] Assembly to EXTERNAL_ACCESS.  Can we set it to UNSAFE as well?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = UNSAFE;

The above query should run successfully. This is because we had changed the database-level setting of TRUSTWORTHY to ON.

CREATE ASSEMBLY [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck]
     AUTHORIZATION [dbo]
     FROM 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_CreateAssemblyCheck.dll'
     WITH PERMISSION_SET = UNSAFE;

The above query should run successfully. When TRUSTWORTHY is to set ON, any Assembly can be set to either EXTERNAL_ACCESS or UNSAFE. There are no restrictions, which is why setting TRUSTWORTHY ON is the less preferred method.

DROP ASSEMBLY [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck];
ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = SAFE;
ALTER DATABASE [StairwayToSQLCLR] SET TRUSTWORTHY OFF;

The first step of the preferred method is to create an Asymmetric Key based on info stored in the Assembly's source DLL file. Execute the following query in the [master] database:

CREATE ASYMMETRIC KEY [StairwayToSQLCLR-04-Key1]
     AUTHORIZATION [dbo]
     FROM EXECUTABLE FILE = 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_ExternalAccess.dll';

Are we there yet? Run the following query to try to set the Assembly that we have been using back to EXTERNAL_ACCESS:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

You should get the following error message:

Msg 10327, Level 14, State 1, Line 1

ALTER ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' failed because assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' 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 second step is to create a Login based on the Asymmetric Key we just created.

CREATE LOGIN [MrStairwayToSQLCLR]
     FROM ASYMMETRIC KEY [StairwayToSQLCLR-04-Key1];

Are we there yet? Run the following query to try to set the Assembly that we have been using back to EXTERNAL_ACCESS:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

You should get the same error that you received in the previous attempt.

The third step is to Grant the Login we just created the desired server-level permission. Execute the following query in the [master] database:

GRANT EXTERNAL ACCESS ASSEMBLY TO [MrStairwayToSQLCLR];

Are we there YET? Run the following query to try to set the Assembly that we have been using back to EXTERNAL_ACCESS:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

The query should have completed successfully! Now we can test it by attempting the External / Regular connection (with Impersonation) that requires EXTERNAL_ACCESS:

EXEC dbo.StairwayToSQLCLR_04_WhoAmI @ServerName = '(local)', @Impersonate = 1;

This query should have also completed successfully, showing that an external connection was made as indicated by the values of the PROGRAM_NAME(), @@SPID, and ORIGINAL_LOGIN fields.

At this point nothing is apparently different between the two configuration methods; we have been able to set this Assembly to EXTERNAL_ACCESS in both cases. As we saw at the beginning of this set of tests, we were also able to set this Assembly to UNSAFE. Are we able to set it to UNSAFE now that we are using the other method?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = UNSAFE;

You should get the following error message:

Msg 10327, Level 14, State 1, Line 1

ALTER ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' failed because assembly 'StairwayToSQLCLR-04-Security2_ExternalAccess' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE 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 UNSAFE ASSEMBLY permission.

This is a new behavior. As we can see, one benefit of using an Asymmetric Key-based or Certificate-based Login, instead of setting TRUSTWORTHY to ON, is that an Assembly can be allowed to be set to EXTERNAL_ACCESS without being allowed to be set to UNSAFE!

UNSAFE Mode, Part 1

This next set of tests show how to set an Assembly to UNSAFE when using the Asymmetric Key-based Login method as well as how multiple Assemblies can be tied to a single Login.

The source code for the [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck] Assembly is shown below. The full C# code can be found in the C:\TEMP\StairwayToSQLCLR\Level-04\SourceCode\CreateAssemblyCheck.cs file. Unlike the first Assembly we used, this one can only be created with a PERMISSION_SET of UNSAFE. Let’s review the code to see why UNSAFE is required.

Looking at the first line of code within the SqlClrSecurity_CreateAssemblyCheck class we see why the Assembly requires the UNSAFE level: it has a variable (_SharedMemory) declared as static that is not also readonly. For those who do not understand, a static variable exists as long as the class is loaded in the App Domain; the class is loaded the first time any of the methods inside of it are called and can stay loaded for quite a long time. Unlike variables in all T-SQL Stored Procedures and User-Defined Functions, this static variable is not created at the beginning of a function call and destroyed as the function exits.

Also, SQL Server creates a single instance of this class that all SPIDs share. Having a shared variable stay resident in memory allows two or more SPIDs to access that value, similar to a Global Temporary Table (i.e. ##TableName). There is no process separation, similar to a Local Temporary Table (i.e. #TableName), so it is not possible to guarantee behavior or have predictable results. Having different users overwrite a variable that others are using (i.e. not “threadsafe”) is typically an undesirable behavior. But in this case, we can see that it is possible to have a value in memory that is shared across SPIDs, which is essentially a cache. If the variable were marked as readonly, it could be initialized to a value but not changed after that. In that case it would return a consistent and predictable value, which is why a static readonly variable is ok in SAFE and EXTERNAL_ACCESS Assemblies.

There are two methods and both are fairly simple. The SetSharedValue method accepts a string. It gets the current value from the static variable (_SharedMemory, which is declared outside of the two methods), sets the variable to the value of the @ValueToSet parameter, and returns the now previous value of the static variable. The GetSharedValue method just returns the current value of the static variable.

Please note that both methods have the IsDeterministic property of the SqlFunction attribute set to false. This is because there is no way to know if the return value will be the same for the same input parameter value across executions.

Open the StairwayToSQLCLR-04-06-UnsafeTests1.sql script in SSMS and run the USE and SET statements towards the top of the script.

We can't load the [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck] Assembly as either SAFE or EXTERNAL_ACCESS (due to it containing code that requires UNSAFE and is checked at CREATE ASSEMBLY time) so we need to try loading it as UNSAFE:

CREATE ASSEMBLY [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck]
     AUTHORIZATION [dbo]
     FROM 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_CreateAssemblyCheck.dll'
     WITH PERMISSION_SET = UNSAFE;

The above query should result in the following error:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_CreateAssemblyCheck' failed because assembly 'StairwayToSQLCLR-04-Security2_CreateAssemblyCheck' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE 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 UNSAFE ASSEMBLY permission.

That error is due to the Login, [MrStairwayToSQLCLR], only being granted permission to have EXTERNAL_ACCESS Assemblies but not UNSAFE Assemblies. Execute the following query in the [master] database:

GRANT UNSAFE ASSEMBLY TO [MrStairwayToSQLCLR];

And try again:

CREATE ASSEMBLY [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck]
     AUTHORIZATION [dbo]
     FROM 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_CreateAssemblyCheck.dll'
     WITH PERMISSION_SET = UNSAFE;

This time the query should complete successfully.

At the end of the previous test section we couldn't set the [StairwayToSQLCLR-04-Security2_ExternalAccess] Assembly to UNSAFE.  Can we now?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_ExternalAccess]
     WITH PERMISSION_SET = UNSAFE;

This time the query should complete successfully. It works because the Login was created from an Asymmetric Key that was created from the Private Key that was used to sign the [StairwayToSQLCLR-04-Security2_ExternalAccess] source DLL. The [StairwayToSQLCLR-04-Security2_CreateAssemblyCheck] Assembly was allowed to be set to UNSAFE because it (the source DLL) was signed with the same Private Key.

Of course, given that the signing of the Assemblies was done outside of these examples, the only indication (so far) that they used the same Private Key is the fact that we were able to set this Assembly to UNSAFE without having to first create the Asymmetric Key and Login. In the following section of tests we will see an indicator of which Assemblies are using the same Private Key.

Now we can create the two T-SQL wrapper objects to expose the CLR functions:

CREATE FUNCTION [dbo].[StairwayToSQLCLR_04_SetSharedValue]
(
   @ValueToSet NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME
[StairwayToSQLCLR-04-Security2_CreateAssemblyCheck].[SqlClrSecurity_CreateAssemblyCheck].[SetSharedValue]

and:

CREATE FUNCTION [dbo].[StairwayToSQLCLR_04_GetSharedValue] ()
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME
[StairwayToSQLCLR-04-Security2_CreateAssemblyCheck].[SqlClrSecurity_CreateAssemblyCheck].[GetSharedValue]

Well, we have come this far so we might as well play with these functions to see what all the fuss is about.

The first time [StairwayToSQLCLR_04_SetSharedValue] is run it stores the passed-in value 'test' but has nothing to return:

SELECT dbo.StairwayToSQLCLR_04_SetSharedValue('test');

The second time it is run it stores the passed-in value 'what now?' but this time does have something to return, which should be: test

SELECT dbo.StairwayToSQLCLR_04_SetSharedValue('what now?');

Running [StairwayToSQLCLR_04_GetSharedValue] does not make any changes, it just returns the current stored value:

SELECT dbo.StairwayToSQLCLR_04_GetSharedValue();

You should get back the value: what now?

UNSAFE Mode, Part 2

This set of tests will demonstrate how Assemblies can be separated into groups to allow for more granular control over which PERMISSION_SET levels they can be set to. The full C# code can be found in the C:\TEMP\StairwayToSQLCLR\Level-04\SourceCode\RuntimeCheck.cs file.

Looking at the code below, we can see a single method, Beep, that will be called by a T-SQL wrapper Stored Procedure, [StairwayToSQLCLR_04_Beep]. If the input parameter, @NoElBeepo, is set to 0 (which it is defaulted to), then System.Console.Beep() is called. While this System method requires a PERMISSION_SET of UNSAFE, that cannot be checked until runtime.

CREATE ASSEMBLY [StairwayToSQLCLR-04-Security2_RuntimeCheck]
     AUTHORIZATION [dbo]
     FROM 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_RuntimeCheck.dll'
     WITH PERMISSION_SET = SAFE;

The query should succeed because the only code within the Assembly that violates any of the restrictions can only be detected at runtime.

Now that the Assembly (containing code that should not be allowed to run) is loaded, we should be able to create the T-SQL wrapper object that will let us execute the .Net code within the Assembly.

CREATE PROCEDURE [dbo].[StairwayToSQLCLR_04_Beep]
   @NoElBeepo BIT = 0
AS EXTERNAL NAME
[StairwayToSQLCLR-04-Security2_RuntimeCheck].[SqlClrSecurity_RuntimeCheck].[Beep];

We have loaded an Assembly containing unsafe code and created the T-SQL wrapper object that references that unsafe code. Does this mean that we were able to bypass security? Let's see.

EXEC dbo.StairwayToSQLCLR_04_Beep;

You should receive the following error:

Msg 6522, Level 16, State 1, Procedure StairwayToSQLCLR_04_Beep, Line 0

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: UI

Notice that the error is a "HostProtectionException". That relates to the Host Protection Attributes that were discussed in the previous level and mentioned again at the beginning of this level. To see one of the lists of these forbidden methods, just go to the Disallowed Types and Members in mscorlib.dll page.

Search for "Beep" and you should find it in the left column, shown as "System.Console.Beep()". In the right column you should see the value "UI" which matches the "demanded resource" mentioned at the end of the error message shown immediately above.

But what if that line of C# code is never executed? Let's try the [StairwayToSQLCLR_04_Beep] Stored Procedure again, but this time we will try to exit the code before the call to the System.Console.Beep() method by setting the input parameter to 1:

EXEC dbo.StairwayToSQLCLR_04_Beep @NoElBeepo = 1;

You should receive the exact same error message as you did when not passing in the input parameter (which used the default value of 0). It seems that SQL Server scans the whole method for runtime checks, and not just the specific / requested code path.

The .Net method with the forbidden function call can't be executed while the Assembly is set to SAFE, but now that the Assembly exists, can it be ALTERed to be UNSAFE?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_RuntimeCheck]
     WITH PERMISSION_SET = UNSAFE;

You should receive the following error:

Msg 10327, Level 14, State 1, Line 1

ALTER ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_RuntimeCheck' failed because assembly 'StairwayToSQLCLR-03-Security_RuntimeCheck' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE 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 UNSAFE permission.

Why did we get an error? We were able to set the other two Assemblies to UNSAFE. Can we at least set this new Assembly to EXTERNAL_ACCESS, just to see if it will work?

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_RuntimeCheck]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

You should receive the following error:

Msg 10327, Level 14, State 1, Line 1

ALTER ASSEMBLY for assembly 'StairwayToSQLCLR-04-Security2_RuntimeCheck' failed because assembly 'StairwayToSQLCLR-04-Security2_RuntimeCheck' 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 Asymmetric Key and Login are still in place, yet we cannot set this Assembly to either EXTERNAL_ACCESS or UNSAFE. Let's take a look at the Assembly meta-data for clues:

SELECT [name], [clr_name]
FROM sys.assemblies
WHERE is_user_defined = 1
ORDER BY [name] ASC;

Your results should be the following:

Pay attention to the "publickeytoken=" portion of the [clr_name] field. The "publickeytoken" value is the same for the CreateAssemblyCheck and ExternalAccess Assemblies. But the value for the RuntimeCheck Assembly is different.

The Login, [MrStairwayToSQLCLR], is linked to the Asymmetric Key that was created from the Certificate used to sign the ExternalAccess and CreateAssemblyCheck Assemblies. But the [StairwayToSQLCLR-04-Security2_RuntimeCheck] Assembly was signed with a different Certificate. In order for this new Assembly to be able to be set to either EXTERNAL_ACCESS or UNSAFE, we need to create another Asymmetric Key and then another Login based on that Key.

First, we create an Asymmetric Key based on info stored in the Assembly's source DLL file. Execute the following query in the [master] database:

CREATE ASYMMETRIC KEY [StairwayToSQLCLR-04-Key2]
     AUTHORIZATION [dbo]
     FROM EXECUTABLE FILE = 'C:\TEMP\StairwayToSQLCLR\Level-04\Assemblies\StairwayToSQLCLR-04-Security2_RuntimeCheck.dll';

Second, we create a Login based on the Asymmetric Key that we just created. Execute the following query in the [master] database:

CREATE LOGIN [MrsStairwayToSQLCLR]
     FROM ASYMMETRIC KEY [StairwayToSQLCLR-04-Key2];

Third, we Grant the Login (that we just created) the desired permission. Execute the following query in the [master] database:

GRANT UNSAFE ASSEMBLY TO [MrsStairwayToSQLCLR];

Please note that we only granted UNSAFE ASSEMBLY permission and not the EXTERNAL ACCESS ASSEMBLY permission. Even though we are only truly interested in the UNSAFE level for this Assembly, would we be able to set the an Assembly to EXTERNAL_ACCESS (in the case where we had another Assembly that only needed EXTERNAL_ACCESS)? Running the following query:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_RuntimeCheck]
     WITH PERMISSION_SET = EXTERNAL_ACCESS;

The query above succeeds due to the UNSAFE ASSEMBLY permission implying the EXTERNAL ACCESS ASSEMBLY permission.

Are we sure that we need the UNSAFE level for this Assembly? Let's try to get away with leaving it at EXTERNAL_ACCESS:

EXEC dbo.StairwayToSQLCLR_04_Beep;

You should receive the following error:

Msg 6522, Level 16, State 1, Procedure StairwayToSQLCLR_04_Beep, Line 0

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: UI

Ok, fine. So we really do need UNSAFE:

ALTER ASSEMBLY [StairwayToSQLCLR-04-Security2_RuntimeCheck]
     WITH PERMISSION_SET = UNSAFE;

That should have succeeded. Now we can run the CLR Stored Procedure:

EXEC dbo.StairwayToSQLCLR_04_Beep;

You may or may not hear a beep, but you definitely shouldn't be getting an error anymore either.

Points of Interest

This last set of tests is designed to address any potential concerns regarding the permanence of granting elevated permissions to Assemblies. The question is primarily: Once an Assembly is set to either EXTERNAL_ACCESS or UNSAFE, will it always be able to execute that code?

Open the StairwayToSQLCLR-04-08-PointsOfInterestTests.sql script in SSMS and run the USE and SET statements towards the top of the script.

First, let's remove the permissions that allowed the Logins to set the Assemblies to either EXTERNAL_ACCESS or UNSAFE. Execute the following two queries in the [master] database:

REVOKE EXTERNAL ACCESS ASSEMBLY, UNSAFE ASSEMBLY TO [MrStairwayToSQLCLR];
REVOKE EXTERNAL ACCESS ASSEMBLY, UNSAFE ASSEMBLY TO [MrsStairwayToSQLCLR];

The following CLR Stored Procedure needs UNSAFE, resides in an Assembly that is currently set to UNSAFE, is a runtime check, and worked prior to running the above REVOKE.

EXEC dbo.StairwayToSQLCLR_04_Beep;

You should receive the following error:

Msg 10314, Level 16, State 11, Line 1

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65547. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly'stairwaytosqlclr-04-security2_runtimecheck, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6c7843d081193a36' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

Is it possible that the error was a result of the code being checked at runtime? What about code that is checked during the CREATE ASSEMBLY?

The following CLR Function needs UNSAFE, resides in an Assembly that is currently set to UNSAFE, is a CREATE ASSEMBLY check, and worked prior to running the above REVOKE.

SELECT dbo.StairwayToSQLCLR_04_GetSharedValue();

You should receive the following error:

Msg 10314, Level 16, State 11, Line 1

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65548. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'stairwaytosqlclr-04-security2_createassemblycheck, Version=1.0.0.0, Culture=neutral, PublicKeyToken=147e18278b82e982' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

Apparently there is at least some validation that occurs per each execution, even if the code can be detected at CREATE ASSEMBLY time (which is good). In order to function again, do we just need to re-GRANT the permission, or do we also need to re-ALTER the Assembly to the desired level? Execute the following query in the [master] database:

GRANT UNSAFE ASSEMBLY TO [MrStairwayToSQLCLR];

And then:

SELECT dbo.StairwayToSQLCLR_04_GetSharedValue();

The query above should complete successfully.

Now, what if we remove the Login rather than just the permission?

DROP LOGIN [MrStairwayToSQLCLR];

And then:

SELECT dbo.StairwayToSQLCLR_04_GetSharedValue();

You should get the same "Msg 10314, Level 16, State 11" error that we saw above. Hence, setting an Assembly to EXTERNAL_ACCESS or UNSAFE is not a permanent guarantee of being able to run that code! The same holds true if you are using the TRUSTWORTHY method and alter it back to OFF. Assemblies cannot be "grandfathered" into a PERMISSION_SET.

Example Cleanup

When you are completely finished working with the example scripts, if you want to clean up any objects that were added and undo any configuration changes, then the final two scripts will take care of that. The scripts—StairwayToSQLCLR-04-09-DisableCLR.sql and StairwayToSQLCLR-04-10-RemoveExampleDBandLogins.sql—are each meant to be run in a single execution and should be run in order (i.e. script 10 is last). The StairwayToSQLCLR-04-09-DisableCLR.sql script will check the [DidWeEnableCLR] Function to see if it should disable CLR Integration or not. That function was created by the StairwayToSQLCLR-04-02-EnableCLR.sql script when doing the Example Setup. And finally, the StairwayToSQLCLR-04-10-RemoveExampleDBandLogins.sql script removes both Logins and both Asymmetric Keys.

Summary

It has been 9 years since Microsoft introduced the ability to integrate CLR code directly in SQL Server and yet it remains a somewhat controversial feature. There is a good amount of misinformation, misunderstanding, and lack of information which makes it more difficult to both discuss the viability of using this feature as well as use it properly (and to its full potential). Security is of the utmost concern (for good reason) and so it is important to know what the various options are and their behaviors across a range of scenarios. In this article we took a deeper look into the PERMISSION_SET options of EXTERNAL_ACCESS and UNSAFE to see how they differed from SAFE. We also covered how to allow for EXTERNAL_ACCESS and UNSAFE Assemblies, and why the preferred method of using a Login based on an Asymmetric Key or Certificate is better than setting the database to TRUSTWORTHY. Lastly, we saw the distinction between accessing external resources through Impersonation of the current login as opposed to using the default SQL Server service account. The next step in our journey will be to transition from concepts to practice; we will learn the basics of developing and deploying CLR objects using Microsoft Visual Studio.

Additional Reading

General Security info:

• CLR Hosted Environment: http://technet.microsoft.com/en-us/library/ms131047.aspx
• CLR Integration Security: http://msdn.microsoft.com/en-us/library/ms131071.aspx
• Code Access Security (within SQL Server): http://msdn.microsoft.com/en-us/library/ms345101.aspx
• Host Protection Attributes: http://msdn.microsoft.com/en-us/library/ms403276.aspx
• CLR Integration Programming Model Restrictions: http://technet.microsoft.com/en-us/library/ms403273.aspx
• Impersonation: see section below on Impersonation
• Code Access Security (full .Net info): http://msdn.microsoft.com/en-us/library/c5tk9z76.aspx
• Key Security Concepts: http://msdn.microsoft.com/en-us/library/z164t8hs.aspx
• Summary of Changes in Code Access Security: http://msdn.microsoft.com/en-us/library/ff527276.aspx
• Code Access Permissions: http://msdn.microsoft.com/en-us/library/h846e9b3.aspx

Impersonation info:

• Impersonation and CLR Integration Security: http://technet.microsoft.com/en-us/library/ms345105.aspx
• Impersonation and Credentials for Connections: http://technet.microsoft.com/en-us/library/ms131068.aspx
• Kerberos Protocol Transition and Constrained Delegation (white paper): http://technet.microsoft.com/en-us/library/cc739587.aspx
• Breaking Changes to Database Engine Features in SQL Server 2008: http://technet.microsoft.com/en-us/library/ms143179(v=sql.100).aspx   (go to 2^nd section—Common Language Runtime / CLR, and then 3^rd item—CLR Routines)
• “Removing the Linked Server 2 hop Limitation” (i.e. Kerberos Delegation): http://www.sqlservercentral.com/articles/Authentication/104979/

T-SQL:

• ALTER ASSEMBLY: http://technet.microsoft.com/en-us/library/ms186711.aspx
• ALTER DATABASE SET Options: http://technet.microsoft.com/en-us/library/bb522682.aspx (in relation to the TRUSTWORTHY external access option)
• CREATE ASSEMBLY: http://technet.microsoft.com/en-us/library/ms189524.aspx
• CREATE ASYMMETRIC KEY: http://technet.microsoft.com/en-us/library/ms174430.aspx
• CREATE LOGIN: http://technet.microsoft.com/en-us/library/ms189751.aspx
• CREATE USER: http://technet.microsoft.com/en-us/library/ms173463.aspx
• GRANT Assembly Permissions: http://technet.microsoft.com/en-us/library/ms189479.aspx
• GRANT Server Permissions: http://technet.microsoft.com/en-us/library/ms186717.aspx (in relation to the EXTERNAL ACCESS ASSEMBLY and UNSAFE ASSEMBLY permissions)
• ORIGINAL_LOGIN: http://technet.microsoft.com/en-us/library/ms189492.aspx
• sys.assemblies: http://technet.microsoft.com/en-us/library/ms189790.aspx
• sys.asymmetric_keys: http://technet.microsoft.com/en-us/library/ms188399.aspx
• sys.server_principals: http://technet.microsoft.com/en-us/library/ms188786.aspx
• sys.database_principals: http://technet.microsoft.com/en-us/library/ms187328.aspx
• EXECUTE AS Statement: http://technet.microsoft.com/en-us/library/ms181362.aspx
• EXECUTE AS Clause: http://technet.microsoft.com/en-us/library/ms188354.aspx
• REVERT: http://technet.microsoft.com/en-us/library/ms178632.aspx
• xp_cmdshell: http://technet.microsoft.com/en-us/library/ms175046.aspx
• sp_xp_cmdshell_proxy_account: http://technet.microsoft.com/en-us/library/ms190359.aspx

About the Author

Solomon Rutzky has been working with databases for 18 years and specifically with SQL Server for 12 years. He has written several articles for SQL Server Central and Simple-Talk. Notable companies that he has worked for include PLATINUM technology and ChannelAdvisor. Solomon is also the author of the popular SQLCLR library of functions: SQL# (SQLsharp).