SQLCLR in SQL Server 2017

Security of your databases has become the most important part of your job as a DBA. No one wants to be the next company to leak a list of customers, leak photos of children and their parents using your company’s product, or have a key marketing database released. Security is also a very complicated, multi-layered thing. There’s not a switch you can turn on to suddenly have good security. Additionally, you can be doing all the right things, and a few bad lines of application code can subvert all of the measures you have taken to protect your data.

With this is mind, Microsoft has made some big changes to CLR in SQL Server 2017. SQL CLR has always been an interesting area of the engine—it allows for the use of .NET code in stored procedures and user defined types. For certain tasks , it’s an extremely powerful tool—things like RegEx and geo functions can be much faster in native CLR than trying to do the equivalent operation in T-SQL. It’s always been a little bit of a security risk, since under certain configurations, CLR had access to resources outside of the context of the database engine. This was protected by boundaries defined in the CLR host policy. We had SAFE, EXTERNAL_ACCESS, and UNSAFE levels that we could set. SAFE simply limited access of the assembly to internal computation and local data access. For the purposes of this post, we will skip UNSAFE and EXTERNAL_ACCESS, but it is sufficed to say, these levels allow much deeper access to the rest of the server.

Code Access Security in .NET (which is used to managed these levels) has been marked obsolete. What does this mean? The boundaries that are marked SAFE, may not be guaranteed to provide security. So “SAFE” CLR may be able to access external resources, call unmanaged code, and acquire sysadmin privileges. This is really bad.

So What Now?

Microsoft is recommending enabling “CLR Strict Security” in SQL Server 2017, which means users cannot create any CLR assemblies unless they have elevated permissions. This could be a breaking change anywhere you want to use dynamic CLR. The user needs to have CREATE ASSEMBLY and one of the following options has to be true:

• Database has the TRUSTWORTHY property on AND the database owner (DBO) has the UNSAFE ASSEMBLY permission on the server
• Assembly is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server

In short, the engine treats all CLR assemblies as unsafe, even if they are created with SAFE or EXTERNAL_ACCESS permission sets.

You will need to sign your assemblies. Also, if you try to restore a database with CLR assemblies into SQL Server 2017, you may run into issues if your assemblies are unsigned.

But Now I Have to Do Work?

What does this mean for you, the SQL DBA or Developer? This is a breaking change (and breaking changes are a BIG DEAL at Microsoft—they don’t like to break things unless there are really good reasons). This means if you are using CLR, and you want to move to SQL Server 2017 (or really, let’s face it, these security risks are in lower versions of the code), you need to work out a signing system for your CLR assemblies and possibly some key management infrastructure. You need to evaluate your older CLR code to ensure that none of it is running under UNSAFE or EXTERNAL_ACCESS (unless you want to turn off “CLR Strict Security”, which you really don’t want to do). Also, if you want to run SQL Server 2017 on Linux, you will be limited to the SAFE permission set.

We Only Have Third Party Apps…

Good luck with that. This is a big change, and we all know lots of smaller independent software vendors (ISVs) have just gotten around to supporting SQL Server 2014. In most of my work with ISVs, I haven’t seen a ton of CLR in most of the vendor apps. That being said, I’m  sure it exists in many ISV apps. The angle I would take as a DBA towards my ISV(s) is to use the “audit hammer” with the vendor. If you are not familiar with this approach to managing your vendors, you can mention that your internal audit process is now failing unsigned assemblies that can’t run under CLR Strict Security. This is probably accurate anyway—CLR has been an audit flag for years. I would also recommend doing a comprehensive testing process to ensure your CLR functions as you would expect.

Don’t Assume that Your Sessions Need to Be 500 Level

You know what people say about what happens when you assume something, right? Don’t be an ass and make assumptions. One trend I’ve seen in recent years from some conference is that the conferences tend to prefer super advanced, internals, deep-dive sessions. While these sessions are very cool, and we all like to totally geek out about internals and the inter-workings of the query optimizer, and secret trace flags that make SQL Server return all query results as emojis. (Dear Conor, can you work on that one )

Anyway, while many of us in the SQL Server community have massive depth of knowledge about the product, and computer systems in general, it is important to realize that everyone attending SQL events is not an MCM, MVP, and MSCE. It’s great if you want to do a session on backup and restore, or how to install SQL Server (make sure to explain why you are doing each step). Topics like intro to T-SQL and Writing Your First Stored Procedure can be fantastic.

I’m not saying the whole event needs to have 100 level sessions, but making an effort to reach out to people who are just getting started could go a long way to broadening the reach of a professional association.