SQL Server 2017, SQLCLR and Permissions
While I was writing the post about SQLCLR and Certificates, I came across some strange behavior when I accidentally ran my code on an SQL Server 2017 instance. I quickly connected back to my SQL Server 2016 instance and continued. However, afterwards I started to look into the differences between SQLCLR in SQL Server 2017 and SQL Server 2016, and this post is the result of me
playing around investigating the changes.
UPDATE 2017-07-23. This post has a follow up post based on changes in SQL Server 2017 RC1.
Let's start with a recap and an example
In all versions of SQL Server from 2005 (where SQLCLR was introduced) up to 2016; when you deploy assemblies to the database and you want the assemblies to be able to execute outside of the database it is deployed to, you assign a permission set to the assembly. There are three permission sets available, ranging from not allowing you to shoot yourself in the foot to blow your head off:
SAFE- the code is not doing anything that you cannot do in T-SQL. You cannot shoot yourself in the foot any more than what you can do with T-SQL. This is the default.
EXTERNAL_ACCESS- you can get outside of the local database, but only via "approved" assemblies, such as
ADO.NET, etc. You can definitely shoot yourself in the foot.
UNSAFE- You can do pretty much what you want. You are free to blow your head off.
The permission set is assigned when you create the assembly:
Code Snippet 1: Creating an Assembly from Absolute Path
In order to create an assembly with anything else than
SAFE, the database or assembly need to have additional authorizations, and that was what my SQLCLR and Certificates post covered.
To see what has changed in SQL Server 2017, let's look at some sample code. In Code Snippet 1 you see some C# code that we want to deploy to a SQL Server 2017 database:
Code Snippet 1: Safe Code to be Deployed
After having built the project with the code in Code Snippet 1, you would deploy it to a specific database in your SQL Server instance like so (the dll is named
Code Snippet 2: Create Safe Assembly
The assembly we want to create in the database is definitely a safe assembly, as it is just doing some internal calculation, and in SQL Server (pre 2017), this would deploy just fine. In SQL Server 2017 however things are different. When you run the code in Code Snippet 2, you will get following error:
Figure 1: Error Deploying Safe Assembly
The error you see is more or less the same you'd receive if you - in SQL Server pre 2017 - tried to deploy an assembly as
UNSAFE. You can read more about that particular issue in my SQLCLR and Certificates post.
One difference in the error message you see in Figure 1, and what you would have received in pre SQL Server 2017 versions, is: "the 'clr strict security' option of sp_configure is set to 1". You may think; "what is this, I have never seen that in other SQL Server versions". You are absolutely correct, so let's look at that - and we'll start with Code Access Security (CAS)
Code Access Security
CAS is a security technology developed to provide the ability to protect system resources when a .NET assembly is executed. Such system resources could be: local files, files on a remote file system, registry keys, databases, printers and so on. CAS, in essence, was used to enforce security boundaries based on code origination or other identity aspects, and SQL Server's
PERMISSION_SET relied on the CAS security boundaries. That was how SQLCLR assemblies which only performed "safe" operations did not need any further authorizations.
In recent versions of the .NET framework (around version 4.5), Microsoft has changed the relation between CAS and security boundaries, and CAS is no longer supported as a boundary! From a SQLCLR perspective that is a real "bummer", as theoretically there is no control over what an assembly can and cannot do (whereas before
PERMISSION_SET controlled the abilities).
NOTE: The above statement about no control what an assembly can and cannot do is not entirely correct as we will see further down.
clr strict security
With the changes of the implications of CAS, and in order to enhance the security of CLR assemblies, Microsoft has in SQL Server 2017 introduced an
sp_configure option called
clr strict security, which by default is set to 1 (on). When the setting is on, SQL Server treats all assemblies (
UNSAFE) as if they were marked
UNSAFE. The implication of this is that you must either:
- Set the database to be
- Sign the assembly with a certificate that has a corresponding login with
UNSAFE ASSEMBLYpermission, OR
- Sign the assembly with an asymmetric key that has a corresponding login with
If you changed the
clr strict security option to 0, the code in Code Snippet 2, would work just fine. Likewise, if you didn't change the option, but instead set
TRUSTWORTHY ON on the database, it would also work.
So, where are we? In order to create a safe assembly we now need to authorize it as we would an
UNSAFE, so let's do that to the assembly that the code in Code Snippet 2 represents. We could do it by setting
ON for the database, and you who read my SQLCLR and Certificates post know that I do not think that setting
ON is like "opening the gates to hell", so I would not have an issue with doing that - even on a production database. That is however a discussion for another day, so let us do it by using a certificate.
To recap from the SQLCLR and Certificates post, to use a certificate you'd:
- Create a certificate, if you don't already have one.
- Create a
.pfxfile from the certificate. Pfx stands for Personal Information Exchange, and we'll use it to sign your assembly.
- Sign the dll with the
- In the database create a SQL Server certificate from the original certificate.
- In the database create login from the certificate.
- Grant the login
- Create the dll in the database.
I already have a certificate and a
.pfx file created from when I wrote the SQLCLR and Certificates post, so I'll re-use those and go ahead and sign the assembly with the
Code Snippet 3: Sign the dll Using signtool.exe
/p flag in Code Snippet 3 defines the password of your
.pfx file. After having signed the dll, I create the SQL Server certificate, from the original certificate:
Code Snippet 4: Create a SQL Server Certificate
When the certificate is created we can create a login from the certificate, and
GRANT the login
UNSAFE assembly permissions:
Code Snippet 5: Create a SQL Server Login and Grant Permissions
After the login has been created and the
UNSAFE ASSEMBLY permission granted to the login, the code in Code Snippet 2, should just work. Oh, don't forget to switch back from
master to the database where you want to create the assembly.
We can then finally create a T-SQL wrapper function against the
Code Snippet 6: Create T-SQL Wrapper Function
To check that it all works, run:
SELECT dbo.fn_clr_Adder(21,21) and the Answer to the Ultimate Question of Life, the Universe, and Everything should be returned to you.
So far this does not seem too bad, OK - so for a
SAFE assembly we need to sign the assembly and have a login with
UNSAFE ASSEMBLY permissions granted (or have
Since the introduction of
clr strict security there has been some confusion about how
SAFE assemblies will behave, and part of this confusion can be attributed to the documentation around
clr strict security. Specifically this statement: A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges.. When reading that statement, my understanding is that if I create an assembly as
SAFE I could potentially do
Let us test this out, and create a second dll
Sql2k17SqlClr2.dll, where the code looks like so:
Code Snippet 7: Safe and Unsafe Behavior
As you see in Code Snippet 7, we have our trusted
fn_clr_Adder method, which is as safe as you can get. But, we also have the
fn_clr_LongRunningAdder method which uses a
Task for calling into another method and this is definitely
UNSAFE. So what will happen here if we, after having built and signed the dll, create the assembly as
Code Snippet 8: Create Safe Assembly with Unsafe Method
Running the code in Code Snippet 8 should work, and it would also have worked in previous versions of SQL Server. We can now create a T-SQL wrapper function around the
fn_clr_LongRunningAdder method (the method using
Code Snippet 9: Create an Unsafe T-SQL Wrapper Function
When I execute
SELECT dbo.fn_clr_LongRunningAdder(21, 21) I would expect - having read the statement above - that this would work. However, on my SQL Server 2017 instance (CTP 2.1), I receive an error:
Figure 2: Error Executing UNSAFE Function in SAFE Assembly
HostProtection kicks in and says that I do not have necessary permissions to do the operation. In previous SQL Server versions this would happen if you hadn't assigned the correct
PERMISSION_SET to the assembly when it was created. So, let us drop the function and the assembly, and recreate the assembly with
PERMISSION_SET = UNSAFE, and recreate the wrapper function:
Code Snippet 10: Create the Assembly as UNSAFE
Now when I execute
SELECT dbo.fn_clr_LongRunningAdder(21, 21), it all works as expected. So it seems that
PERMISSION_SET still has a role to play, at least in this scenario.
So what about if I have a
SAFE assembly which references an
UNSAFE assembly, and calls into an
I created a third dll:
Sql2k17SqlClr3.dll, which has a method that calls into the
fn_clr_LongRunningAdder method in the
Code Snippet 11: Method Calling Into UNSAFE Method
After having built and signed the dll, I deployed it to the database with
PERMISSION_SET = SAFE, and created a T-SQL wrapper function around the
fn_clr_Adder method. This is what was returned when executing the function:
Figure 3: Error Calling Into UNSAFE Assembly From SAFE
Also in the case of referenced assemblies, it seems that
PERMISSION_SET plays a role. This was confirmed when I dropped and recreated the
PERMISSION_SET = UNSAFE. When I at that stage executed the wrapper function, all worked OK!
In SQL Server 2017, Microsoft now by default requires that all type of assemblies (
UNSAFE) are authorized for
UNSAFE access, by:
- The database is set to be
- The assembly is signed with a certificate that has a corresponding login with
UNSAFE ASSEMBLYpermission, OR
- The assembly is signed with an asymmetric key that has a corresponding login with
The above requirement is handled by a new configure option:
clr strict security. The documentation around this option is, at least at the moment, somewhat mis-leading as it implies that
PERMISSION_SET no longer has an impact on the behavior of an assembly.
The tests above shows however that if you want to do an
UNSAFE operation, the assembly has to be created with
PERMISSION_SET = UNSAFE. So what I wrote in the beginning of this post: "From a SQLCLR perspective that is a real "bummer", as theoretically there is no control over what an assembly can and cannot do (whereas before
PERMISSION_SET controlled the abilities).", does not seem to be correct.
Bear in mind that I am running SQL Server 2017 CTP 2.1, and things may change. I'll try and dig in some more in this, and if I find out something I will edit this post.
EDIT: Things changed in SQL Server 2017 RC1. I have written about the changes in the SQL Server 2017 SQLCLR - Whitelisting Assemblies post.
If you have comments, questions etc., please comment on this post or ping me.
Share this Post:Twitter | Google+ | LinkedIn