SQL Philosopher

Once we have setup our environment properly, written the code we are wanting to deploy, and decided how we are going to handle code security for SQL CLR, the remaining steps are very straightforward.  Unless we are referencing other libraries that are not part of .NET, but that will be discussed in detail later.

Problem with the master database

Before we deploy, there is one error that you may run into on Visual Studio 2010, if you attempt to deploy your code directly to the “master” database.  If when you deploy to master, you receive the following error in the “Build” Output:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 15657, Level 16, State 1, Procedure sp_db_vardecimal_storage_format, Line 65 Vardecimal storage format is not available in system database 'master'.

This is due to the deploy script attempting to enable the vardecimal storage format on master, but you cannot perform this action in master. So, to work around this problem, you will either have to deploy to a different database, or you can implement a new “Database Project” that references your SQL Server CLR Object.  This process is described in detail on MSDN, so I will not repeat it.

Deploy Your Code

If you have .NET setup properly, all you have to do to deploy your code is right-click on the project in Solution Explorer and click “Deploy”.  If you haven’t already built your code, it will build (compile) it before deploying the code to SQL Server:

You should be able to see the results of your deployment in the “Output” pane within Visual Studio. Toward the end of this output, hopefully you will see something like:

Build succeeded.

Time Elapsed 00:00:06.84
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Testing Your Code

At this point, your SQL CLR stored procedure should be deployed and ready to use on your SQL Server database.  Connect to SQL Server through SSMS and connect to the database to which you deployed your code.

I have created a very simple Test that you can use to test your code:

DECLARE @isMatched	BIT,
		@pattern	VARCHAR(256) = '([A-Za-z0-9\-]+)',
		@testString	VARCHAR(256) = 'some text string'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched
 
SET @pattern = '([0-9\-]+)'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched

Assuming that you get no errors, the first Execution should return a “1” because the test String matches the Regular Expression. The second Execution should return a “0” because the test String does not match the Regular Expression defined.

I will be adding some more complex objects soon, but in the meantime, I hope that this short tutorial gives you an insight into how SQL CLR can improve your environment.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

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:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

Now that we have a basic understanding of SQL CLR and have an environment configured to support the code we want to write, we can move on to writing our first SQL CLR stored procedure.  I am going to start out with a very simple piece of code, but one that solves a common problem in SQL Server: Matching Regular Expressions.

As I noted in a previous blog post, SQL Server does not have good native support for Regular Expressions, but when you are trying to sanitize data, Regular Expressions can save you a ton of processing time, development time, and effort.  So, let’s fill this need right now, by using SQL CLR.

Open the “Visual C# SQL CLR Database” Project that we created earlier and add a “Stored Procedure” class to the project, if you did not already do so.  I am going to call the file that will contain my class, “RegExTestMatch.cs”.  When you open this class for editing, you will see code that looks similar to the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RegExTestMatch()
    {
        // Put your code here
    }
};

Now, without delving too deeply into programming, I need to bring up just a few points of information, for those not familiar with high-level programming languages (HLPLs).

Libraries

To make programming languages easier to use and to encourage proper code reuse, most HLPLs incorporate some form of libraries.  These are collections of classes, methods, and properties that can be used in other projects and classes.  Programmers can also create their own libraries and include them in future projects or make them available to other developers, or even the general public.  In C#, if a library is to be used within a particular set of code, that usage must first be communicated to the compiler, by way of the “using” statements at the top of the code listing.  You will notice, the code that was auto-generated when we added our class to our project already had five “using” statements, meaning that our code is already able to utilize the objects defined inside those five libraries.

Return Types

A method is similar to a procedure within SQL Server.  A method is generally a part of a class (not always) and defines some action that can be taken from within the specific class.  In the code that was generated for us, our method is named “RegExTestMatch”.  The word that directly preceeds that name, is called the return type.  This defines the data type that any calling process should expect to return from this method. By default, this method’s return type is “void” meaning that nothing will return from this method.  We can change this to suit our needs.

Object-Oriented Programming

For the sake of brevity, I will state that Object-Oriented Programming (OOP) is a style of programming that focuses on objects, which are usually classes (but certainly not always).  A class is defined by a programmer and then can be reused many times either within the same project or in other projects that reference the library containing this object.  For the purpose of this demonstration, the key point to understand is that we can create discrete instances of a specific class and that instance is what we use to perform whatever tasks we need.  This entire subject would require a lot more discussion to flesh out completely.

To Solve Our Problem

Now that we have a basic understanding of a few points within our managed language, we can address the problem at hand.  We are trying to create a CLR stored procedure that will accept a Regular Expression Pattern and a String to test for matching against said pattern.  We will then want to respond to the calling process with either a true or a false.

We will need to first be able to use Regular Expressions within our code, so we need to include a reference to the Regular Expressions library found within .NET, so let’s add that “using” statement:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RegExTestMatch()
    {
        // Put your code here
    }
};

Next, we need to decide what we are going to pass to the method and what we will return to the calling process.  There are many different ways that we could handle this, but to keep things simple, I am going to pass in two strings, a pattern and a test string, and then return an int, which I will either set to “1” to indicate that the test string did indeed match the pattern, or a “0” to indicate that it did not match:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
    {
        // Put your code here
    }
};

After we have our method setup, we need to instantiate an instance of the Regex class (which is the class within the “System.Text.RegularExpressions” library we will be using). During our instantiation, we can go ahead and pass in the pattern we want to match, but we will have to first cast the pattern to the correct datatype, “string”. That code looks like this:

Regex patternTester = new Regex((string)regExPattern);

The last part is to use our newly instantiated variable, which we named “patternTester”, to perform the test against our Test String. Once again, we must cast the variable to the correct datatype. Putting all of this together results in our final code, which looks like:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
    {
        Regex patternTester = new Regex((string)regExPattern);
        if (patternTester.IsMatch((string)testString))
            return 1;
        else
            return 0;
    }
};

Now, we will need to have a brief look at security before we move on to deploying and using this code.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

To create and execute SQL CLR procedures (as well as other objects), there are several prerequisites that must first be met.  We are going to need:

• Visual Studio (I’ll be using Visual Studio 2010 Ultimate)
• A SQL Server Instance (I’ll be using SQL Server 2008 R2)
• The .NET framework (For SQL Server 2008 R2, I’ll be using .NET 3.5)

These three components give us an application in which we can write and compile code, a database engine to host the code we will write, and the CLR we need to execute the code.

Configuration

The configuration steps for each component are simple, but I want to review them quickly here.  You may need to take particular consideration to setting up Visual Studio with your environment’s source control, but I will not be covering that in this series.  Each shop implements their source control a little differently and it would be best to stay consistent with the rest of your development endeavors.

SQL Server

For basic SQL CLR procedures, the only configuration needed on SQL Server is to enable CLR. I have included the script from MSDN here, for quick reference:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

.NET Framework

To enable the .NET Framework on the SQL Server, you will need to install the .NET 3.5 Framework (available from Microsoft).  If you are on Windows 2008 or Windows 2008 R2, you will have to Enable the Application Server Role first.  To enable the Application Server Role, you simply open “Server Manager”, right-click on “Roles” and select “Add Roles”.  Then select “Application Server” from the “Server Roles” list in the “Add Roles Wizard” dialog box, and click through the rest of the wizard to complete installation:

Visual Studio

Lastly, we need to configure Visual Studio.  After we have Visual Studio installed, we can open it up and Create a New Project.  In the “New Project” Dialog box, select “Database” and then “SQL Server” from the Left pane, called “Installed Templates”.  In the center pane, select “Visual C# SQL CLR Database Project”.  I suggest C# just because it is the language I prefer and also the language I will be writing all future examples in, but you can also choose Visual Basic, if that suits you better.  At the top of the dialog, change the .NET Framework to 3.5 (this can be changed later, if needed), give the project a name, and click “OK” to create your project:

Now that we have an open project, we need to setup our connection to the database we will be using.  In the Solution Explorer pane of Visual Studio, right-click on the project you created, and select “Properties”. Find the “Database” tab, click on it, and then click the “Browse…” button next to the “Connection String” field.  A small dialog will come up, allowing you to chose an existing “Database Reference” or add a new one, if needed.  Select the appropriate database reference, and close out of this dialog:

Lastly, we need to add a class that will contain the object(s) that we will be creating.  For example, we can create a “Stored Procedure” class by right-clicking on the Project in the Solution Explorer, and then select “Add”, and then “New Item…”.  This brings up the “Add New Item” dialog box, where we can select “Stored Procedure”.  Select that item, give it a name, and click the “Add” button:

Now we have an environment that is ready to start creating SQL CLR objects.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

Executive Summary

SQL CLR is a feature which allows database developers to create powerful code that can be executed from within SQL statements, utilizing familiar programming languages.

More Detail

SQL CLR is a feature, added in SQL Server 2005, that allows a database developer to host .NET managed code inside of a SQL Server database instance.  The hosted code can then be used within SQL DML statements to execute procedures/methods that were written in a .NET language.  To understand what is going on here, a brief understanding of .NET, CLR, CIL, and CLI is useful.

Starting in the early 2000s, Microsoft released a set of languages under a common framework, called .NET (Visual Basic .NET, C# .NET, and J# .NET).  The intent was to be able to bring a common set of features together from the different leading high-level programming languages at the time (Visual Basic, C/C++, and Java).  Microsoft published their own versions of each of these languages (Microsoft already had versions of all of these languages in the market), some being changed more greatly than others.

They were all published under one framework so that features available to one language would be available to all, although with different implementations.  Each of these languages would then compile down to a common code type, known as Common Intermediate Language (CIL).  This is not to be confused with the Common Language Infrastructure (CLI) which is a standard, developed by Microsoft, in conjunction with the .NET endeavor.  The CLI is the standard, .NET/CIL/CLR is just the way that Microsoft implemented this standard.

This Intermediate Language would then be converted by the Common Language Runtime (CLR) into machine code, which is specific to a particular processor family. Having this relationship between the CIL and the CLR is what makes .NET languages Interoperable and Portable.  The languages can be said to be Interoperable in that any language that compiles down to CIL can be linked with other similarly compiled code.  When I say Portable, I mean that, theoretically, a CLR-type interpreter (virtual machine) could be created for basically any platform you wish, and that new interpreter would be able to convert that CIL into machine code that would run on that platform.

This is where SQL CLR comes back into the picture.  The SQL Common Language Runtime hosts a distinct engine within SQL Server that runs the CIL that the database developer has created.  This enables programmers to work in the managed languages, with which they are already familiar.  They can take advantage of many of the features that are only available to managed and compiled code.  It is important to remember, however, that there is overhead in running through the CLR and poorly written or implemented methods can actually cause huge performance problems.  Like anything in database development, you have to learn how to use the tools effectively.  There are no magic bullets.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

Microsoft SQL Server has a ton of features and tools built into the product that we use every day. But sometimes we need to be able to step outside of the base feature-set and jump into traditional programming to accomplish tasks that are either harder or impossible without doing so. In SQL Server 2005, Microsoft introduced SQL CLR to enable this sort of extensiblity. I have used SQL CLR to accomplish several different types of tasks and I have found that there are a lot of different nuances to consider when using this technology. I am going to attempt to distill these points into a short series of blog posts that will outline the basics of SQL CLR. The points I am hoping to cover are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)

I will update this post with links to the other blog posts as I write them.  I hope that this short series will prove useful to those trying to wade through this technology.