Before we publish our newly created SQL CLR Stored Procedure, we need to take a moment to discuss security. SQL Server is a highly-protected environment. As Database Administrators, we strive to work based off of least-privilege principles to keep the environment’s data secure and trustworthy. Introducing a completely new set of code, running inside of our environment, should make us all take particular caution. Unfortunately, there is quite a lot of debate across the internet about this, in which one side is saying, “Don’t worry about nitpicking over signed vs. unsigned code. Protecting the front door is your real concern.” While, yes, the front door (logins, roles, so forth) is often left vulnerable, shouldn’t we protect the back door, especially when it is a NEW back door that we are opening willfully?
So, what is the risk?
The risk is that while signed code is guaranteed to match the code that was originally compiled, unsigned code could be manipulated to inject malicious commands that can be executed with very high privileges, without the DBA being aware at all. This is another subject that could be spoken about for pages and pages worth of text, but suffice it to say, that I believe it is worth the 60 seconds (literally) that it takes to sign your code.
If it is so simple, why is there a debate?
Because, there are complications that can arise. For one, if you are going to include another library with your code that was not signed, then you cannot sign your code. Signed code requires that all dependencies are also signed. This may be insurmountable. Also, there may be a precedence set within your environment that is hard to overcome. If downstream developers are not on board, and you need to use their libraries in your code, then you will either have to recompile their code as signed code, or convince them to move toward a signed assembly mindset.
So, how do I sign my code?
Signing code involves asymmetrically encrypting a hash (like a CRC) of the compiled code within the resultant assembly. This enables the calling process to verify that the code has remained unchanged since the developer compiled it. Since we are dealing with encryption, you will either need an existing signing certificate, issued by a CA (like Verisign) or do what is known as “Self-Signing”. I am going to walk through Self-Signing our code, since it is free.
From within Visual Studio, in the Solution Explorer pane, right-click on the project and select “Properties”. Then select the “Signing” tab to the left. You will see a screen similar to this:
I click “Sign the assembly” and then I need to choose the “Strong Name Key File” I will use to sign the code. If I did not have an existing certificate, I could select “New..” from the drop-down:
All I have to do in the “Create Strong Name Key” dialog box is define a name for my new key file and setup a good strong password. Once I click “OK” in this dialog box and then save the project, my code will automatically be signed at compile time (as long as I do not have any unsigned references in my code).
What if I cannot sign my code?
If you cannot sign your code, you are going to have to tell SQL Server that you want it to trust “unsafe” assemblies. You can do this through the ALTER ASSEMBLY command.
Now that we have made our decision about signing our code and taken the necessary steps, we are ready to deploy and test our new SQL CLR Stored Procedure.
This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:
- The Origination and Uses of SQL CLR
- Infrastructure Requirements and Configuration
- Writing a Simple CLR Stored Procedure
- Security and Signing
- Deployment and Usage
- Table-Valued Functions and Other SQL CLR (Coming Soon)
- Incorporating Existing Libraries (Coming Soon)
1 Comments.