So far in this series we have explored what SQLCLR can do (Level 1) and even saw working examples of some of those interesting capabilities (Level 2). In both talking about and testing those capabilities, we came across some functionality that requires some degree of security configuration changes. For example, in Level 2 when we tried to make an external connection to the database for the purpose of inserting a row from within a function, we got an error and had to set the Assembly to EXTERNAL_ACCESS and set the database to TRUSTWORTHY. For those who plan on working with CLR code within SQL Server, or are at least researching the possibility, it is important to understand the security restrictions and capabilities so that SQLCLR can be used properly and to the best of its abilities, or possibly not used at all, depending on requirements. There is also an unfortunately common misperception of SQLCLR as being a security risk that prevents this feature from being used in situations where it would be of great benefit. Given the importance of this topic, we will explore the various aspects of security before learning how to develop SQLCLR objects.
In order to understand SQLCLR security as fully as we really should, we need to discuss several concepts. There is probably too much information for a single article so we will approach this in two steps. First we look at the overall structure of SQLCLR security and the various mechanisms in place to restrict what CLR code is allowed to do. We will focus on the most restrictive mode to show how safe it really is. This part will be a little heavier on the information with some examples at the end. Then, in Level 4, we will explore what can be done if we aren't restricted. That part will be lighter on the information but will have several examples.
What is Security?
The concepts of security and permissions in SQL Server refer to who can take certain actions. Even a general configuration setting such as "cross-database ownership chaining" controls who someone is in a particular context. On the other hand, there are actually a few different ways that security is managed in SQLCLR, depending on context. There are three different contexts in which to control what SQLCLR code can do: the CLR, SQL Server, and potentially external resources.
Restricting the Code Itself
The main context for controlling SQLCLR code is the CLR code itself, regardless of who is running that code. Rather than preventing users and/or roles from performing specific operations, the code is prevented from performing specific operations; security in this context refers to what actions can be taken. Thinking in terms of using your favorite desktop spreadsheet program and trying to open the payroll file that is in the HR area of the network share: we usually think in terms of the payroll file itself being protected and you not having access to it, but with Code Access Security you would not be able to do File | Open in the first place.
There are a couple of different mechanisms which control what the .Net code is allowed to do. In either case, these controls are managed on a per-assembly basis: all code within an assembly will have the same permissions and restrictions.
Code Access Security grants permissions to various resources, such as the network, file system, registry, etc. There are three areas in which this control is defined. The following bullet-point description is taken from the CLR Integration Code Access Security MSDN page (also found in the "Additional Reading" section):
- Machine policy: This is the policy in effect for all managed code running in the machine on which SQL Server is installed.
- User policy: This is the policy in effect for managed code hosted by a process. For SQL Server, the user policy is specific to the Windows account on which the SQL Server service is running.
- Host policy: This is the policy set up by the host of the CLR (in this case, SQL Server) that is in effect for managed code running in that host.
Access to a resource can only be granted if allowed by all three policy types. If the Host policy—determined by the PERMISSION_SET of the Assembly—allows accessing the file system but the either Machine policy or the User policy does not allow it, then the code will not be able to access the file system. Of course, rarely, if ever, will this aspect of security (i.e. Machine and User Policies) be something that you need to deal with so it shouldn't have any practical bearing on SQLCLR projects. It is being mentioned here for the sake of completeness and to indicate that there are means outside of SQL Server for controlling what .Net code is allowed to do. Please see the "Additional Notes" section at the bottom for some related info (#1).
Programming Model Restrictions are additional constraints placed on the CLR code that focus on various types of operations that might be performed, such as using updateable static variables, using finalizers, etc. These prohibit various operations depending on what they are doing or what side-effects they might have. Also, while Code Access Security checks are all done at run-time, there are some Programming Model Restrictions checks that are done when loading the assembly via CREATE ASSMEBLY and some checks done at run-time.
Host Protection Attributes are a means of marking .Net framework methods with specific effects and/or behaviors that different CLR hosts (SQL Server in this case) may find undesirable. There are various HPA's to prevent sharing memory, spinning up additional threads, accessing the UI, and more. HPA's are an aspect of Programming Model Restrictions and are checked at run-time. To be clear, these attributes are not something that people developing SQLCLR code would mark their own code with; these attributes have already been used to mark the underlying functionality that comes with .Net that developers of SQLCLR projects might try to utilize.
Technically, this is not security in the way that we usually define it since we are not protecting a valuable resource, such as data; we are neither denying access to an object nor encrypting anything. The MSDN page for Host Protection Attributes states:
The HostProtectionAttribute is not a security permission as much as a way to improve reliability, in that it identifies specific code constructs, either types or methods, that the host may disallow. The use of the HostProtectionAttribute enforces a programming model that helps protect the stability of the host.
That same Host Protection Attributes MSDN page (also linked at the end of the article) has links to the lists of disallowed classes and variables.
How do you control the enforcement of some, all, or none of these restrictions? SQLCLR projects are controlled via the Assembly's permission set. Meaning, Assemblies with a PERMISSION_SET of either SAFE or EXTERNAL_ACCESS are not permitted to make use of certain functionality within .Net, and in addition to that, Assemblies marked as SAFE are not allowed to access external resources. The PERMISSION_SET property of an assembly can be set during CREATE ASSEMBLY and can be changed via ALTER ASSEMBLY. The following level descriptions 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 following chart summarizes the restrictions and permissions granted from the perspective of just the SAFE permission set. The information is taken directly from the MSDN pages on Code Access Security and Programming Model Restrictions. We will explore the EXTERNAL_ACCESS and UNSAFE permission sets in Level 4.
What you CAN do in SAFE mode | |
| SecurityPermission |
|
| SqlClientPermission |
|
What you CANNOT do in SAFE mode | |
| General |
|
| CREATE ASSEMBLY Checks | The following custom attributes are disallowed:
|
| Runtime Checks | All types and methods annotated with the following host protection attribute (HPA) values in the supported list of assemblies are disallowed:
|
Restricting Access to SQLCLR Code
SQLCLR security within SQL Server is mainly managing permissions on Assemblies and the T-SQL wrapper objects. You can restrict anyone from creating Assemblies in the first place, and you can restrict any login or role from using any of the T-SQL wrapper objects; if you don't want people running certain stored procedures or functions, then don't grant them EXECUTE or SELECT permissions on those objects.
In terms of controlling assemblies, the database-level permissions of CREATE ASSEMBLY and ALTER ANY ASSEMBLY allow for restricting users from either adding any new SQLCLR code or changing existing SQLCLR code respectively. If a finer-grained control is desired, you can restrict users to only being able to change existing SQLCLR code on a per-Assembly basis by using the ALTER ON ASSEMBLY permission.
Even if users are allowed to create Assemblies, you can still restrict them from running EXTERNAL_ACCESS and/or UNSAFE code via either the TRUSTWORTHY database setting or the UNSAFE ASSEMBLY / EXTERNAL ACCESS ASSEMBLY Login Permissions. Simply allowing users to create Assemblies does not give them the ability to load functionality into SQL Server that does things that are not considered to be "safe" (i.e. thread-safe operations that are confined within SQL Server). By default, if there are any violations (refer to the chart above) that can be caught during CREATE ASSEMBLY, then the Assembly simply will not be created, even if it is set to PERMISSION_SET = SAFE. And if the only violations are ones that can only be caught at runtime, then the Assembly can be created with PERMISSION_SET = SAFE but any code that requires a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE will error if accessed. Setting an Assembly to be either EXTERNAL_ACCESS or UNSAFE requires that certain conditions be met first or else the user will get an error when trying to CREATE or ALTER the Assembly. The two options for allowing Assemblies to be set to EXTERNAL_ACCESS or UNSAFE are discussed in detail in Level 4.
It is also possible to have SQLCLR code that should only be executed by certain users. In this case, the standard EXECUTE and SELECT permissions can be granted or denied, just as you would for T-SQL objects. Users can even be prevented from using User Defined Types (UDTs) via DENY EXECUTE ON TYPE::{TypeName} TO {UserName}. When denying access to a UDT, they can still be used in a DECLARE statement, but attempting to SET that variable will result in an error.
Please see the "Additional Notes" section at the bottom for some related info (#2).
Restricting Users Outside of SQL Server
When an Assembly has a PERMISSION_SET of SAFE the code inside the Assembly cannot reach outside of SQL Server. In this case there are no external / OS security concerns or considerations to make. We will, however, explore this topic in Level 4.
Examples
The example code—in a zip file attached to this article at the bottom in the "Resources" section—consists of nine SQL scripts and three DLLs. Extract the zip file into a C:\TEMP\StairwayToSQLCLR folder. You should now have a C:\TEMP\StairwayToSQLCLR\Level-03 folder with the SQL scripts and 2 subfolders in it. All of the "test" SQL scripts have full descriptions of what each test is doing. They are essentially self-guided examples and are intended to have each command run individually by highlighting it and then executing.
The relevant portions of the source code for each Assembly are shown in the example summaries below. For those who want to see all of the C# source code, there are three files in the Level-03\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 Level 5. Along those lines, the C# source code will only be explained in brief.
If you do not have "CLR Integration" enabled, or are not sure, run the first script, StairwayToSQLCLR-03-01-EnableCLR.sql, and then close it. That script essentially does the following:
EXEC sp_configure 'clr enabled', 1 RECONFIGURE --WITH OVERRIDE
Run the second script, StairwayToSQLCLR-03-02-CreateDatabase.sql, which just creates a database to load the objects in and a database-only User, and then close it. That script essentially does the following:
CREATE DATABASE [StairwayToSQLCLR]; ALTER DATABASE [StairwayToSQLCLR] SET RECOVERY SIMPLE, DISABLE_BROKER WITH ROLLBACK IMMEDIATE; CREATE USER [TestUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo];
All of the tests, at least here in Level 3, assume that our desire is to only allow SQLCLR code (i.e. Assemblies) that has a PERMISSION_SET of SAFE. This is a good policy for those who want some of the benefits of running .Net code (functionality, performance, etc) but, for whatever reason, do not want to deal with any security implications. The questions then become: can we be sure that a) no code gets in that would be a security risk, and that b) even if questionable code is loaded, it cannot run and nobody can change the PERMISSION_SET to allow it to run. Please note that we have not done any configuration outside of simply enabling CLR Integration; we are starting at the default state and have not done anything regarding security outside of doing nothing (meaning that the default state is secure and we would have to do additional work in order to be less secure).
Create Assembly Check
The tests here show the effect of the checks being done when the Assembly is loaded via CREATE ASSEMBLY. A static variable, _SharedMemory, is declared but it is not declared as "readonly" so it is updateable. This keeps the value in memory across all Sessions that access that code. While there are situations when this might be beneficial, it does still introduce a degree of unpredictability to the code and makes it so that the code is not fully independent of other callers. The violating code, found in C:\TEMP\StairwayToSQLCLR\Level-03\SourceCode\CreateAssemblyCheck.cs, is:
Because this check is done during CREATE ASSEMBLY, the Assembly can only be loaded if the PERMISSION_SET is set to UNSAFE; it is not possible to create it as SAFE and then ALTER it to UNSAFE. But, due to not setting up the environment to allow EXTERNAL_ACCESS or UNSAFE Assemblies, no Assembly can be created or altered to either EXTERNAL_ACCESS or UNSAFE. Hence, there is no way to load this Assembly.
Open the StairwayToSQLCLR-03-03-CreateAssemblyCheckTest.sql script in SSMS and run the USE and SET statements towards the top of the script. The first attempt someone will make is to load the Assembly with the needed—UNSAFE—setting:
CREATE ASSEMBLY [StairwayToSQLCLR-03-Security_CreateAssemblyCheck] AUTHORIZATION [dbo] FROM 'C:\TEMP\StairwayToSQLCLR\Level-03\Assemblies\StairwayToSQLCLR-03-Security_CreateAssemblyCheck.dll' WITH PERMISSION_SET = UNSAFE;
Running the above will get the following error due to us not doing any configuration that is needed in order for Assemblies to be set to either UNSAFE or EXTERNAL_ACCESS:
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'StairwayToSQLCLR-03-Security_CreateAssemblyCheck'
failed because assembly 'StairwayToSQLCLR-03-Security_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.
The next test is to change only the PERMISSION_SET to be EXTERNAL_ACCESS instead of UNSAFE. But running that just produces the same error. So the only option left for someone to try is to attempt PERMISSION_SET = SAFE, which is the third, and final, test. Running that CREATE ASSEMBLY statement gets a slightly different error:
Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'SetSharedValue' on type
'SqlClrSecurityCreateAssembly' in safe assembly
'StairwayToSQLCLR-03-Security_CreateAssemblyCheck' is storing to a static
field. Storing to a static field is not allowed in safe assemblies.
This time, due to being allowed to create Assemblies set to SAFE, SQL Server first inspects the Assembly to make sure that there are no violations. It finds a method (i.e. function), SetSharedValue, that uses the static variable we saw declared above on line 17 and returns an error stating the exact violation and where it occurred. And so our desire to be as secure as possible has been maintained. Please see "Additional Notes" section at the end for a minor "however" (#3).
Runtime Check
The tests here show the effect of the checks being done when executing the SQLCLR code. A method is called that should make a sound by calling Console.Beep(). This is a UI function that a backend-only service like SQL Server has no purpose in calling. The violating code, found in C:\TEMP\StairwayToSQLCLR\Level-03\SourceCode\RuntimeCheck.cs, is:
Just like using an updateable static variable, this too requires that the PERMISSION_SET be set to UNSAFE. But, unlike that previous situation, simply having this code in the Assembly is acceptable for SAFE and EXTERNAL_ACCESS Assemblies because it is possible that this code is never called. This means that the Assembly can be loaded if it is set to SAFE. But, if the method with the offending code is called, it will cause an exception due to the Assembly not being set to UNSAFE. However, it is not possible to ALTER any Assemblies in this database to have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE. Hence, there is no way to execute the offending code, even if the Assembly has been loaded into SQL Server and might even contain other functions that do not have offending code and can be executed.
Open the StairwayToSQLCLR-03-04-RuntimeCheckTest.sql script in SSMS and run the USE and SET statements towards the top of the script. The first attempt someone will make is to load the Assembly with the needed—UNSAFE—setting:
CREATE ASSEMBLY [StairwayToSQLCLR-03-Security_RuntimeCheck] AUTHORIZATION [dbo] FROM 'C:\TEMP\StairwayToSQLCLR\Level-03\Assemblies\StairwayToSQLCLR-03-Security_RuntimeCheck.dll' WITH PERMISSION_SET = UNSAFE;
Running the above will get the following error due to us not doing any configuration that is needed in order for Assemblies to be set to either UNSAFE or EXTERNAL_ACCESS:
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'StairwayToSQLCLR-03-Security_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 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.
The next test is to change only the PERMISSION_SET to be EXTERNAL_ACCESS instead of UNSAFE. But running that just produces the same error. So the only option left for someone to try is to attempt PERMISSION_SET = SAFE. Running that CREATE ASSEMBLY statement, the third test, succeeds due to the violating code not being something that can be detected during the loading of the assembly. Now that we have an Assembly loaded we can try creating the T-SQL wrapper object that will let us execute the code within the Assembly. Running the fourth statement, shown immediately below, should also succeed:
CREATE PROCEDURE [dbo].[StairwayToSQLCLR_03_Beep] @NoElBeepo BIT = 0 AS EXTERNAL NAME [StairwayToSQLCLR-03-Security_RuntimeCheck].[SqlClrSecurityRuntime].[Beep]
Does loading an Assembly with "unsafe" code and creating the T-SQL wrapper object for it without getting any errors allow us to execute that code? Run the fifth statement to find out:
EXEC dbo.StairwayToSQLCLR_03_Beep;
Running the above will get the following error due to the Assembly not being set to UNSAFE (being set to UNSAFE means being given "full trust"):
Msg 6522, Level 16, State 1, Procedure StairwayToSQLCLR_03_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
How did the CLR know that System.Console.Beep was forbidden outside of UNSAFE Assemblies? It was marked with a Host Protection Attribute. You can see the list if you go here, http://msdn.microsoft.com/en-US/library/ms403285.aspx. If you search for "beep" (towards the top) you will see it shows "UI" in the "HPA Value(s)" column, which matches the "demanded resources" note in the error message above.
In the C# source code above, we saw that passing in @NoElBeepo = 1 would exit the function before the call to System.Console.Beep. Would this satisfy the runtime check since the offending code won't actually get executed? Run the next statement to find out:
EXEC dbo.StairwayToSQLCLR_03_Beep @NoElBeepo = 1;
Running the above statement returns the error that we got from the prior test when we didn't pass in the @NoElBeepo parameter and it used the default of 0. Here we see that executing a function with offending code, even if that offending code is not executed, is not allowed. But again, another function in this same Assembly, assuming it does not have any offending code in it, would execute.
Given that we have the Assembly—containing a function that has offending code—at least loaded into SQL Server, can we ALTER the Assembly to set the PERMISSION_SET to UNSAFE (we are not doing CREATE ASSEMBLY and we are not running the offending code)? That test is:
ALTER ASSEMBLY [StairwayToSQLCLR-03-Security_RuntimeCheck]
WITH PERMISSION_SET = UNSAFE;Running the above ALTER statement gets the same error that we got when we tried the CREATE ASSEMBLY statements that had PERMISSION_SET being set to either EXTERNAL_ACCESS or UNSAFE. So, even though we were able to load the Assembly due to the offending code not being something that can be caught during CREATE ASSEMBLY, our desire to be as secure as possible is still maintained.
What Can We Do In SAFE Assemblies?
There are quite a few things that can be done in Assemblies marked as SAFE: string manipulation, date manipulation, formulas, Regular Expressions, and a lot more. However, it is quite common to reference other database objects in Functions and Stored Procedures. We can't connect to external SQL Server instances unless the Assembly is set to either EXTERNAL_ACCESS or UNSAFE, but can we at least connect to the current SQL Server?
Yes, there is a special connection string to use when setting up the connection: context connection = true. Using this connection string allows the CLR code to access the current SQL Server session (i.e. SPID), which is sometimes referred to in this Stairway series as the internal, or "in-process", connection. The term "in-process" is used to make it clear that a separate SQL connection is not being made and any assumptions about temp tables, CONTEXT_INFO, etc. that are valid for T-SQL Functions and Stored Procedures are also valid for SQLCLR Functions and Stored Procedures. While the Context Connection can be used regardless of what PERMISSION_SET the Assembly is set to, it is the only connection string that will work when the PERMISSION_SET is set to SAFE.
To set up the next two tests, run the StairwayToSQLCLR-03-05-CreateObjectsForNextTwoTests.sql script and then close it. That script creates several objects. Most of the objects pertain to either the Internal Data Access test or the Ownership Chains test, but the following Assembly is used for both tests:
CREATE ASSEMBLY [StairwayToSQLCLR-03-Security] AUTHORIZATION [dbo] FROM 'C:\TEMP\StairwayToSQLCLR\Level-03\Assemblies\StairwayToSQLCLR-03-Security.dll' WITH PERMISSION_SET = SAFE;
Internal Data Access
These tests show what type of data access is allowed in SAFE mode (i.e. internal vs. external). We start with the following T-SQL Inline-TVF, WhoAmI (created in script 05), which returns a single row of various connection and session properties. The purpose of the function is to help identify differences in connection types and authentication options.
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 and USER
SESSION_USER AS [SESSION_USER]The SQLCLR Stored Procedure, StairwayToSQLCLR_03_WhoAmI (source code shown below and in its entirety in the C:\TEMP\StairwayToSQLCLR\Level-03\SourceCode\TheCode.cs file), is nothing more than a wrapper around the WhoAmI TVF. Together they allow for testing various combinations of internal vs. external connections with impersonation vs regular connections. Here in Level 3 we are only concerned with the internal connection, but we will make use of the other options in Level 4.
The following section of code establishes the default connection string (line 23) being the internal / in-process connection: the Context Connection. If a @ServerName value is passed in, it is used and Windows Authentication is assumed (lines 26 - 32). The query is statically defined on line 34. Please note that while it is a best-practice to explicitly state the fields in a SELECT clause, for testing / demo purposes it is easier to do "SELECT *" so that the T-SQL TVF can be updated to include additional columns and have those come through the SQLCLR Stored Procedure without having to recompile and redeploy it.
In the following section, a connection is made (line 56) and the query is executed (line 68). A pointer to the result set—dataReader—is passed back (line 70) at which point the results are displayed. We will go over the impersonation (lines 46 - 54 and 63 - 66) and message related statements (lines 58 - 61) in Level 4 as we will not be making use of them here.
The following T-SQL wrapper object (created in script 05), allows us to access the .Net code:
CREATE PROCEDURE [dbo].[StairwayToSQLCLR_03_WhoAmI] ( @ServerName NVARCHAR(200) = NULL, @Impersonate TINYINT = 0, @Message NVARCHAR(4000) = NULL ) AS EXTERNAL NAME [StairwayToSQLCLR-03-Security].[SqlClrSecurity].[WhoAmI]
Open the StairwayToSQLCLR-03-06-InternalDataAccessTest.sql script in SSMS and run the USE and SET statements towards the top of the script. The first two tests prove that the Context Connection really is your current context (i.e. SPID, etc), just as you would expect running any Stored Procedure or Function. As additional evidence that the execution context of the SQLCLR code is no different (with regards to T-SQL statements) than if the code were straight T-SQL, we will set CONTEXT_INFO—Session-specific memory—to a random value:
DECLARE @GUID UNIQUEIDENTIFIER;
SET @GUID = NEWID();
SELECT @GUID AS [@GUID],
CONVERT(VARBINARY(50), @GUID) AS [@GUID -> VARBINARY];
SET CONTEXT_INFO @GUID;
-- run the following statement again, by itself, and the values won't change.
SELECT CONTEXT_INFO() AS [CONTEXT_INFO()],
CONVERT(UNIQUEIDENTIFIER, CONTEXT_INFO()) AS [CONTEXT_INFO() -> UNIQUEIDENTIFIER];
Running the above statements together should produce results similar to the following:
@GUID @GUID -> VARBINARY
A1ADA29A-7FDB-44A1-B5A9-38270DF54D8C 0x9AA2ADA1DB7FA144B5A938270DF54D8C
CONTEXT_INFO() CONTEXT_INFO() -> UNIQUEIDENTIFIER
0x9AA2ADA1DB7FA144B5A938270DF54D8C00000000... A1ADA29A-7FDB-44A1-B5A9-38270DF54D8C
Be sure to make a note of the CONTEXT_INFO() value so you can compare it to the results of the tests that follow in this example. And speaking of which, we can now start running the tests. The first one is just running the T-SQL Function directly, as a control, to see that the returned values (especially the @@SPID, CONTEXT_INFO(), and SESSION_USER fields) are what we expect (SPID can be seen in SSMS in the query tab, on the right side in parenthesis, and in the bottom info bar, towards the right and in parenthesis):
SELECT * FROM dbo.WhoAmI();
The value of the [@@SPID] field should match what you see in SSMS and the value of the [CONTEXT_INFO()] field should match what you noted before. Next we can run the SQLCLR Stored Procedure. No input parameters are specified as we want the default values; the default for @ServerName is NULL which means we will be using the Context Connection.
EXEC dbo.StairwayToSQLCLR_03_WhoAmI; -- default for @ServerName = NULL
Running the above returns the exact same values as before for all fields; we are the same User in the same Session. Of course, someone might try to use a real connection string pointing back to the current instance. The next test passes in a value for @ServerName which means we won’t be using the Context Connection.
EXEC dbo.StairwayToSQLCLR_03_WhoAmI @ServerName = '(local)';
Running the above will result in the following error:
Msg 6522, Level 16, State 1, Procedure StairwayToSQLCLR_03_WhoAmI, Line 0
System.Security.SecurityException
Trying to connect via a regular / external connection throws a security exception (this is a runtime check) due to the Assembly having a PERMISSION_SET of SAFE. Attempting to ALTER the Assembly to be EXTERNAL_ACCESS is not allowed in this database (for the same reasons as before). Hence, data access is allowed, but only in the current session and security context.
Ownership Chains
Ownership Chaining is not a specific security feature of SQLCLR; it is how SQL Server already works. And since Dynamic SQL breaks the ownership chain, it should not be surprising that SQL executed from within SQLCLR code also breaks the ownership chain, given that it is Dynamic SQL.
All of the tests in this example make use of the following table:
CREATE TABLE dbo.StairwayToSQLCLR_03
(
ID INT IDENTITY(1, 1) NOT NULL
CONSTRAINT [PK_StairwayToSQLCLR_03] PRIMARY KEY CLUSTERED,
MessageText NVARCHAR(4000) NOT NULL,
InsertSource NVARCHAR(128) NOT NULL
CONSTRAINT [DF_StairwayToSQLCLR_03_InsertSource]
DEFAULT (COALESCE(OBJECT_NAME(@@PROCID), N'Ad-hoc or Dynamic SQL')),
InsertTime DATETIME NOT NULL
CONSTRAINT [DF_StairwayToSQLCLR_03_InsertTime] DEFAULT (GETDATE()),
InsertUser SYSNAME NOT NULL
CONSTRAINT [DF_StairwayToSQLCLR_03_InsertUser] DEFAULT (SESSION_USER)
);
GRANT SELECT ON dbo.StairwayToSQLCLR_03 TO PUBLIC;There are two Stored Procedures that INSERT into this table: the following using just T-SQL, and one written in C#.
CREATE PROCEDURE [dbo].[StairwayToSQLCLR_03_InsertRowTSQL]
(
@Message NVARCHAR(4000) = NULL
)
AS
SET NOCOUNT ON;
IF (@Message IS NULL)
BEGIN
PRINT 'Nothing to INSERT.';
RETURN;
END;
INSERT INTO dbo.StairwayToSQLCLR_03 (MessageText)
VALUES (@Message);
RETURN;
GO
GRANT EXECUTE ON dbo.StairwayToSQLCLR_03_InsertRowTSQL TO PUBLIC;The C# code shown below (also located in the C:\TEMP\StairwayToSQLCLR\Level-03\SourceCode\TheCode.cs file) does not do much. It defines a query containing a single parameter "@Message" (lines 132 - 133). It then defines the connection and then the parameter (lines 135 - 144). Notice that we are passing a parameter into the query rather than concatenating the value into the string. In Level 2 we concatenated values into the query string but doing that allows for the possibility of a SQL Injection attack. Using a parameter as is being done here is the preferred method.
The following T-SQL wrapper object (created in script 05), allows us to access the .Net code:
CREATE PROCEDURE [dbo].[StairwayToSQLCLR_03_InsertRowCLR]
(
@Message NVARCHAR(4000) = NULL
)
AS EXTERNAL NAME [StairwayToSQLCLR-03-Security].[SqlClrSecurity].[InsertRowCLR];
GO
GRANT EXECUTE ON dbo.StairwayToSQLCLR_03_InsertRowCLR TO PUBLIC;Open the StairwayToSQLCLR-03-07-OwnershipChainTest.sql script in SSMS and run the USE and SET statements towards the top of the script. These tests show that the ownership chain is maintained as expected when using the T-SQL Stored Procedure to INSERT into the Table, but also that the ownership chain is broken when using the SQLCLR Stored Procedure that attempts the same INSERT.
First, we must change our current security context to another user, TestUser (created in script 02), who is less-privileged than the one we created all the objects with (and thus would naturally have permission to INSERT into the test table).
EXECUTE AS USER = 'TestUser'; SELECT SESSION_USER AS [SessionUser];
Running the two statements above will change the current execution context and return a value of "TestUser" since that is the User that you are now acting as. Next we run two statements, as controls, to confirm what permissions we have on the StairwayToSQLCLR_03 table directly.
SELECT * FROM [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03;
Running the above should return a result set, though initially it will be empty.
INSERT INTO [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03 (MessageText)
VALUES (N'test attempt 1');Running the above will return the following error due to not being GRANTed INSERT permission; only SELECT was GRANTed.
Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'StairwayToSQLCLR_03', database
'StairwayToSQLCLR', schema 'dbo'.
Now we can proceed with the first real test, which is to call a T-SQL Stored Procedure that encapsulates the INSERT statement that we cannot run directly:
EXEC [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03_InsertRowTSQL @Message = N'test attempt 2';
Running the above completes successfully. Let’s make sure that we are still acting as TestUser and that we actually inserted a row:
SELECT SESSION_USER AS [SessionUser]; SELECT * FROM [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03;
Running the two statements above should return results very similar to the following:
SessionUser
TestUser
ID MessageText InsertSource InsertTime InsertUser
1 test attempt 2 StairwayToSQLCLR_03_InsertRowTSQL 2014-05-11 12:56:23.130 TestUser
The INSERT worked when executing the Stored Procedure due to Ownership Chaining, which skips the permission check on objects referenced within an object if the inner objects have the same owner as the outer object. This mechanism allows one to maintain security on tables by granting certain Logins or Roles permissions to only Stored Procedures. However, Ownership Chains cannot be maintained through Dynamic SQL. The second test demonstrates how T-SQL submitted from within SQLCLR code is treated.
EXEC [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03_InsertRowCLR @Message = N'test attempt 3';
Running the above statement results in the following error:
Msg 6522, Level 16, State 1, Procedure StairwayToSQLCLR_03_InsertRowCLR, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate
"StairwayToSQLCLR_03_InsertRowCLR":
System.Data.SqlClient.SqlException: The INSERT permission was denied on the object
'StairwayToSQLCLR_03', database 'StairwayToSQLCLR', schema 'dbo'.
Even though we are using the Context Connection, we are still submitting Dynamic SQL. And while it was expected that TestUser would not be able to INSERT via the SQLCLR Stored Procedure, we should probably make sure that our real login can successfully run StairwayToSQLCLR_03_InsertRowCLR to ensure that the source of the error is really Ownership Chaining and not something else.
REVERT; SELECT SESSION_USER AS [SessionUser];
Running the two statements above should undo the "EXECUTE AS..." that we ran at the beginning of this example and return the name of our real Login. And now we can execute the SQLCLR Stored Procedure again:
EXEC [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03_InsertRowCLR @Message = N'test attempt 4';
The above statement should complete successfully. The last thing we need to do is check the table to make sure that the row was inserted:
SELECT * FROM [StairwayToSQLCLR].dbo.StairwayToSQLCLR_03;
Running the above statement should return results very similar to the following:
ID MessageText InsertSource InsertTime InsertUser
1 test attempt 2 StairwayToSQLCLR_03_InsertRowTSQL 2014-05-09 12:56:23.130 TestUser
2 test attempt 4 Ad-hoc or Dynamic SQL 2014-05-09 18:04:18.620 dbo
Notice that the row inserted by the SQLCLR Stored Procedure indicated that it was inserted via Dynamic SQL (due to @@PROCID returning NULL in the columns DEFAULT CONSTRAINT). Of course, Dynamic SQL is not a problem here since the User has INSERT permission. And as stated at the beginning of this example, this behavior is not so much a feature of SQLCLR but something to be aware of. While out of scope for this article, I will mention that some (or all?) of the ways to get passed broken Ownership Chains are:
- GRANT permissions on the objects to whomever needs it
- Call T-SQL objects owned by the appropriate user that contain the T-SQL statements rather than executing those statements directly
- Use ADD SIGNATURE (and look for a future article from me on this topic, but not part of this Stairway series)
- Use EXECUTE AS
Summary
We have just learned several different ways that SQL Server, by default, protects itself against potentially dangerous code, even if there is no intention of harm. We have tried to evade security restrictions in several different ways but were unsuccessful. Hence, we can plainly see that SQLCLR code, at least in SAFE mode, is very secure. In Level 4 we will loosen the restrictions, see what we can do, and how to best enable and control EXTERNAL_ACCESS and UNSAFE Assemblies.
Additional Notes
- Regarding Code Access Security: changes were made with the introduction of .Net 4.0 as noted in the Summary of Changes in Code Access Security MSDN page; Security Policies have gone away in favor of Security Transparency. So the above description might seem to only apply to SQL Server versions 2005, 2008, and 2008 R2 since they do not use .Net 4.0 or above. But SQL Server 2012 and 2014 do use .Net 4.0. However, according to the "What's New in CLR Integration" page for SQL Server 2012:
Due to the strict security requirements of SQL Server 2012, CLR database components will continue to use the Code Access Security model defined in CLR version 2.0.
- With regards to controlling how users can manipulate T-SQL objects related to SQLCLR code (i.e. Assemblies and their wrapper objects): technically, users cannot create T-SQL wrapper objects for CLR Stored Procedures in a particular Assembly if they are not the owner of the Assembly and do not have REFERENCES permission on the Assembly. However, this is not really a security feature given that, in most cases, the user creating the Assembly is the same user that is also creating the T-SQL wrapper objects for it, especially when using an automated deployment system. This restriction would seem helpful mainly when wanting to restrict who can execute certain SQLCLR Stored Procedures, in which case you would not want someone creating another T-SQL wrapper object –one that they would have execute permission on—pointing to the same CLR code. But again, given how code is typically deployed to Production, the user who does not have execute permission on the SQLCLR Stored Procedure would also likely not have CREATE PROCEDURE permission to begin with, and the user actually executing the CREATE PROCEDURE statement would likely already be the owner of the Assembly or at least be acting as "dbo".
- Regarding the restriction against updateable static variables outside of UNSAFE Assemblies: this one specific restriction is not absolute like the others. Technically, there is a way to sneak an updateable static variable passed the checks being done both during CREATE ASSEMBLY and at runtime. However, it is highly discouraged and fortunately not well-known. But is it a security concern if someone does find out about this trick and employs it? It is not really a security concern as much as it is a concern about system stability and consistent behavior of the code. The main issue for stability is that until the App Domain is recycled or the SQL Server service is restarted, unless the code cleans up that memory it will remain in use.
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
- 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
- SqlContext object: http://technet.microsoft.com/en-us/library/ms131108.aspx
T-SQL:
- ALTER ASSEMBLY: http://technet.microsoft.com/en-us/library/ms186711.aspx
- CONTEXT_INFO: http://msdn.microsoft.com/en-us/library/ms180125.aspx
- CREATE ASSEMBLY: http://technet.microsoft.com/en-us/library/ms189524.aspx
- CREATE USER: http://technet.microsoft.com/en-us/library/ms173463.aspx
- DENY Assembly Permissions: http://technet.microsoft.com/en-us/library/ms188402.aspx
- DENY Database Permissions: http://technet.microsoft.com/en-us/library/ms177518.aspx (in relation to the ALTER ANY ASSEMBLY and CREATE ASSEMBLY permissions)
- DENY Type Permissions: http://technet.microsoft.com/en-us/library/ms180063.aspx (in relation to the EXECUTE permission)
- GRANT Assembly Permissions: http://technet.microsoft.com/en-us/library/ms189479.aspx
- GRANT Database Permissions: http://technet.microsoft.com/en-us/library/ms178569.aspx (in relation to the ALTER ANY ASSEMBLY and CREATE ASSEMBLY permissions)
- GRANT TYPE Permission: http://technet.microsoft.com/en-us/library/ms174346.aspx (in relation to the EXECUTE permission)
- ORIGINAL_LOGIN: http://technet.microsoft.com/en-us/library/ms189492.aspx
- SET CONTEXT_INFO: http://msdn.microsoft.com/en-us/library/ms187768.aspx
- sys.assemblies: http://technet.microsoft.com/en-us/library/ms189790.aspx
- sys.database_principals: http://technet.microsoft.com/en-us/library/ms187328.aspx
- Ownership Chaining: http://msdn.microsoft.com/en-us/library/ms188676.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).