Is it ok to load .net dlls into SQL Server as UNSAFE?

Question

When creating a SQL Server CLR stored procedure, I noticed that I couldn't reference anything in the .net framework as I would normally. After some reading around, I realised that assemblies needed to be loaded into the database first.

Therefore, I loaded in the ones I need but due to P/Invoke had to use the UNSAFE permission set. I can now reference them in my stored procedure code and everything works fine.

However, I'm a little concerned about having to set them to UNSAFE when I don't really know what they are doing. So my question is this:

Is it ok to load the .net framework in as UNSAFE without exactly what it's doing? And how would doing so compromise security/robustness/scalability of sql server (as microsoft warn it could)?

Many thanks.

Answer 1

This question is specific to loading .Net Framework assemblies that are not in the set of Supported .NET Framework Libraries, so I will focus on the context being Microsoft supplied DLLs rather than any random DLL.

The difference between the assemblies in the "Supported" list and those not in the list comes down to the fact that the supported ones "have been tested to ensure that they meet reliability and security standards for interaction with SQL Server" (as noted in the "Supported Libraries" page linked above). The main issue is more so the "reliability" than the "security". The assemblies in the supported list have been verified to behave consistently as expected and without any bugs or odd side-effects. The functionality has been tested to work with various languages and collations, etc.

Some .Net Framework assemblies that are not in the supported list can be loaded with a PERMISSION_SET set to SAFE. This, however, does not guarantee desired behavior. And some can be loaded as UNSAFE without necessarily indicating that there will be a problem.

As an example of not guaranteeing behavior: I have loaded System.Drawing in order to do some simple image manipulation. I tested manipulations when the image was supplied directly via byte[] / VARBINARY(MAX) as well as when it was supplied by a filepath and read from disk. Everything worked as expected. I sent that to someone in Germany whose Windows and SQL Server were both set to "German" as the language. He was able to get the expected results when supplying the image directly. But when he supplied a filepath it didn't work.

And regarding undesired behavior, SQL Server will display the reasons why the assembly can't load as either SAFE or EXTERNAL_ACCESS when you try to do it. For example:

CREATE ASSEMBLY [System.Drawing]
AUTHORIZATION [dbo]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll'
WITH PERMISSION_SET = SAFE;

Results in:

Warning: The Microsoft .NET Framework assembly 'system.drawing, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Drawing' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x600013c][offset 0x00000053][found address of Byte] Expected numeric type on the stack.

[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x600013c][offset 0x00000043][found Native Int][expected address of Byte] Unexpected type on the stack.

[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x600013c][offset 0x00000027][found Native Int][expected address of Byte] Unexpected type on the stack.

[ : System.Drawing.Icon::ToBitmap][mdToken=0x6000349][offset 0x00000084][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.

[ : System.Drawing.Icon::ToBitmap][mdToken=0x6000349][offset 0x000000E4] Unmanaged pointers are not a verifiable type.

[ : System.Drawing.Icon::GetShort][mdToken=0x6000356][offset 0x00000002] Unmanaged pointers are not a verifiable type.
...

If you are not going to use any of those methods or types, then you likely will not have any issues. There is just no way to separate out the "safe" stuff from the "unsafe" items.

Another example of guilt-by-association, but even farther removed, is:

CREATE ASSEMBLY [System.Web]
AUTHORIZATION [dbo]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Web.dll'
WITH PERMISSION_SET = SAFE;

Results in:

Warning: The Microsoft .NET Framework assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'microsoft.build.framework, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.xaml, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'TypeDescriptorRefreshed' on type 'System.Windows.Markup.ValueSerializer' in safe assembly 'System.Xaml' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

As you can see, System.Web is actually, by itself, fine for SAFE, but it has dependent assemblies and those are being auto-loaded. The first dependent assembly, microsoft.build.framework also has no issues (at least not that can be verified, though it is possible that something that is disallowed in SAFE is there but can only be caught at run-time). But the second dependent assembly does have an issue that can be verified upon loading the assembly: it is "storing to a static field". This is a problem for reliability more than security because classes are instantiated one time (well, per App Domain, meaning: per-database, per owner) and shared across the SPIDs to use (which is why only static methods are accessible in SQLCLR). Hence, static class-level variables are technically sharing information between sessions (i.e. SPIDs) and that can very easily cause unexpected behavior. But at the same time, if you only want to use HtmlString.ToHtmlString(), then you probably aren't making use of System.Xaml. So why doesn't it just load System.Web as SAFE and System.Xaml as UNSAFE? Probably because code in SAFE assemblies is not allowed to call code in UNSAFE assemblies (at least not in SQLCLR).

CONLUSION
So is it OK to load UNSAFE .Net Framework assemblies? That really should come down to testing. Lots of testing (and not just a single thread on your dev box, but real testing). If everything behaves as expected then you should be fine. But, if something does not behave as expected, then it is not a bug that can be reported to Microsoft because it has already been declared as unsupported.

---

EDIT:
And here is a more official answer to this question, which lists a few situations where problems could occurr: Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment

Answer 2

When you use the SQL database engine on a server which is hosting many many public websites you don't know anything about as a server administrator (or DBA or whoever responsible), you should restrict their access and damn it's important! Also if you have a DBA in a restricted area, where data matters the most in some big companies, again it's the most important thing.

In my point of view, you should give your application as it needs to see, nothing more. If you don't need to see the registry for example, why you wanna give unrestricted access to the assembly? You have no idea how dangerous it could be if somebody injects the code of your application and hijack into the database (also with an unrestricted access!).

Hope it helps

Answer 3

It could change the registry, restart services, reboot the server etc. Nothing too important ;-) A simple chart with the differences

See this question too (no answers though) SQL Server 2008: How crash-safe is a CLR Stored Procedure that loads unmanaged libraries

Of course, what are you doing that requires UNSAFE access?