Foo Theory

Scope of this Post

In this post, I would like to disuss the CLR integration in SQL Server 2005. Specifically, I would like to talk about some of issues associated with calling Web Services from a managed code stored procedure. While creating managed code stored procedures, functions, user defined types and aggregates is very well documented in the MSDN documentation, the issues around calling Web Services from managed code stored procedures and user-defined functions have not been covered very well.

Therefore, I have created this small example. Note that this is not necessarily a real-world example, although the approach used in this example could definitely be applied to real-world application.

In this example, we assume we have a local database called SqlClrTestDB, which has a single table called Users. The structure of this table is shown below:

A stored procedure, named uspGetUserWithId, has been created in TSQL. This procedure returns the UserId, UserName and EmailAddress for a user with a given UserId. Let's assume that we have received the additional requirement that in addition to the above columns, we should also return an additional boolean column, which indicates whether or not the email address for the user is valid. Futher, let's assume that we decided to implement the validation of the email in a .NET Web Service.

We will implement this functionality in a managed code stored procedure called GetUserWithId. The remainder of this post will detail how this procedure and the associated email validation web service should be developed in managed code. Note that in this post, we are for the most part not including any detailed error handling, since this would distract us from the problem that we would like to focus on. In the future, I am planning to post an article which covers the details of exception handling within SqlCLR managed code.

Creating the Solution

First, we need to create a blank solution, called my solution SqlClrCallingWebServices.sln. Create two folders under this solution:

• Web Service. This folder will contain our email validation Web Service
• SqlClr. This folder will contain our SqlClr stored procedure code.

Creating our Email Validation Web Service

Follow the steps below the create the Email validation web service:

1. Add a new ASP.NET Web Service Application to the solution, under the Web Service solution folder. Call the Web Service Project EmailValidation, and add a Web Service called EmailValidation.asmx.
2. Add a method called ValidateEmail, which will perform our email validation. The body of this method uses a simple regular expression to validate the email address. The body of the method is shown below:

I usually set my project to run under IIS instead of under the Visual Studio Web Server. You can do this by going to: Properties | Web, and selecting the "Use IIS Web Server" radio button. You will also need to click the "Create Virtual Directory" button, which will create the IIS virtual directory for you.  Keep the suggested Url, we will need this Url later on, when we want to generate the code for the web service proxy class.

Creating the SqlClr Stored Procedure

Creating the Sql Server Database Project

Now, we are ready to add our SqlClr stored procedure, using the following steps:

• Add a Database Sql Server Project to the solution, under the SqlClr folder. Call the project UserProcedures, as is shown below:

• After we create the project, Visual Studio will prompt us to add a reference to a database, we will add a reference to SqlClrTestDB database, as is shown below:

• Since we are going to call a Web Service from this project, we will need to add a reference to System.Web.Services to the stored procedure. Right click the References node in Solution Explorer, and select Add Reference. In the dialog, make sure that the Sql Server tab is selected, and select System.Web.Services from the list:

Creating the Web Service Proxy class

In order to be able to invoke the Web Service, we need to create a proxy class. In this case, we will use WSDL to generate this class. I like to store all of my proxy classes in a seperate project folder called "ConsumedWebServices". Follow these steps:

• Create a new project folder called "ConsumedWebServices".
• Open a Visual Studio command prompt in the project directory, and enter the following command:

• Add this file to the project by selecting Add | Existing Item, and selecting EmailValidationProxy.cs.
• Sql server will NOT ALLOW any code containing threading semantics to execute within it's process. By default, WSDL will generate both synchronous AND sychronous invocation methods. Therefore, you should "trim out" any asychnrounous method containing BeginInvoke, EndInvoke, Delegates etc. You should only maintain the constructor and the ValidateEmail synchronous method in the proxy class.

Add a Stored Procedure to the Project

Next, we add a stored procedure to our project. We wil call the procedure GetUserWithId:

This stored procedure will retrieve the UserId, UserName, the EmailAddress and the Email Validation Result from our Web Service for the User with the specified UserId. At a high level, this stored procedure will perform the following tasks:

1. Retrieve the UserName and EmailAddress for the specified user, by invoking the stored procedure uspGetUserWithId.
2. Validate the retrieved eMail address by invoking our EMailValidation web service.
3. Create a custom record, store our values in this record, and return the record to the caller.

The skeleton for our stored procedure is shown below:

First, we need to create a helper method to retrieve the userName and EmailAddress, based upon the UserId. This code is pretty much standard SqlClr server code:

1. First, we open up a trusted connection, by creating a SqlConnection instance a "Context Connection=true" connection string. Since our procedure is already running within the context of the SQL Server process, we are already in the database, and we don't need an explicit connection. Specifying "Context Connection=true" indicates to SQL server that we want to use the implicit database connection already in place.
2. Next, we setup our parameters, and call our "uspGetUserId" stored procedure, and return both the userName and emailAddress as reference parameters.

The full code for this method is shown below:

Next, we can add our code to call into the Web Service, which is very easy using the proxy class:

Note again that we are completely ignoring error handling here. Look for a future post on the details of handling errors in SqlClr procedures.

Finally, we need to return our custom record with the following fields:

1. UserId
2. UserName
3. EmailAddress
4. The validation status of the email address

When you return such a custom record from a SqlClr stored procedure, you can define the structure of this record by means of the SqlMetaData class. You create an array of SqlMetaData instances, where each instance represents a column in the record, and then you pass this array into the constructor of a new SqlDataRecord instance. Once you created the SqlDataRecord instance, you can set the values of the record by it's various SetXXX methods. We have isolated the creation of this record in the createDataRecord helper method:

The main body of our stored procedure now looks as follows:

Again, no error handling is included here, so if we do not find the specified user, we simply return. After we create our SqlDataRecord instance, we use the SqlContext's SqlPipe instance to send the data row instance to the caller.

Configuring the Project and Sql Server

Before we can debug our stored procedure, we need to perform some extra configuration work. Sql server defines three different access categories of code access security (CAS) for managed code:

1. SAFE
2. EXTERNAL_ACCESS
3. UNSAFE

An assembly that is marked with the SAFE access permission can only perform internal computation and local data access. The SAFE access permission is the most restrictive. SAFE assemblies cannot access external resources such as files, the network, environment variables, the registry OR any Web Services. Therefore, the SAFE permission will not be sufficient for our assembly.

An assembly that is marked with the EXTERNAL_ACCESS permission have the same permission as SAFE assemblies, but with the additional ability to access external resources, such as Web Services, environment variables etc. In our case, we will want to use the EXTERNAL_ACCESS permission set.

An assembly with the UNSAFE permission set has unrestricted access to resources, both inside and outside of SQL server. This permission set is considered very dangerous, and should be avoided as a general policy.

To set the EXTERNAL_ACCESS permission level, we select the properties of our project, and go to the Database tab. From there, we can set the permission level, as shown below:

For more information on permission sets for managed assemblies in Sql server, check out this msdn post.

We also need to ensure that our assembly has a strong name, by going to the Project properties | Signing, checking the checkbox "Sign the Assembly", and selecting "New.." from the "Choose a stroing name key file" dropdown. In the dialog that comes up, enter a key file name. Enter the name "UserProcedures.snk" as the key file name. Optionally, you can protect the key with a password. In order to be allowed to have the EXTERNAL_ACCESS permission set, our assembly needs be strong named.

By default, CLR integration in Sql server is turned off. You need to run the system stored procedure sp_configure to turn on CLR integration.  In addition, if your managed code assembly is marked with the EXTERNAL_ACCESS permission, your database has to meet one of these two requirements:

1. The database owner has to have EXTERNAL ACCESS ASSEMBLY permission and the database must have the TRUSTWORTHY property set.
2. The assembly must be signed with a certficate or assymetric key with a login that has EXTERNAL ACCESS ASSEMBLY permission

The TRUSTWORTHY property of a database requires admin privileges  to set and is a prerequisite for installing non-SAFE assemblies in that database.

An example script that enables CLR integration in the server and that turns the TRUSTWORTHY property on is shown below:

Debugging the Stored Procedure

When a Sql Server Database project is created, Visual Studio will add a folder called "Test Scripts" to the project. This folder contains a Test.sql script, which can be used to debug the project. It contains a number of commented-out templates to invoke the created SqlClr code. In our case, we can replace all of these templates with the following invocation of our stored procedure:

exec GetUserWithId 2

Now, when we set a breakpoint in our stored procedure, and hit F5, we will be able to debug our stored procedure. However when we debug our  stored procedure, you will notice that we get the following exception right when we create the constructor of our Web Service Proxy:

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetUserWithId':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] sources)
at System.Xml.Serialization
...

What is wrong here?

Well it turns out that the Web Service proxy classes generated by WSDL will attempt to use dynamic assembly generation at the procedure runtime to build and load the XmlSerializer classes that it uses to communicate with the Web Service. This is not allowed in SqlClr, regardless of the permissions of the assembly. As a matter of fact, SQL Server will not allow any implicit load of any assembly, albeit using Load(), LoadFrom(), LoadFile etc.

The solution is to is to build your XmlSerializer classes in advance, using a command line utiltity, sgen.exe. We will add a post-build step to our project, will will build the serializer assembly after the assembly is build:

The full command is:

sgen /force $(TargetFileName) /compiler:/keyfile:..\..\UserProcedures.snk

Since our main assembly is strongly named, this assembly needs to be strongly named also, hence the /compiler:/keyfile option.

This will produce an assembly called UserProcedures.XmlSerializers.dll in the bin\debug directory of the project. This assembly will contain our serialization implementation.

Note that this assembly also needs to be registered in SQL Server (together with the UserProcedures.dll main assembly). This also messes a bit with the automatic deployment procedures implemented by Visual Studio, since it only registers the main assembly. While there are a number of ways around this problem, here is what I usually do:

1. I turn off automatic deployment for the project by bringing up the properties of the project, going to the Deploy tab, and unchecking the "Deploy Code" checkbox.
2. Although one would think that the previous step would be sufficient, I have found that in the configuration manager, the assembly will still be set to automatic deployment. Therefore, you need to go to the solution, right click and select Properties to bring up the configuration manager.  In the Configuration Manager, select "Configuration Properties", and uncheck "Deploy" for the UserProcedures assembly.
3. I create a manual deployment SQL script, which I can run myself is Sql Management studio. The script is shown below:

Note that although the name of the serializers assembly is UserProcedures.XmlSerializers.dll, we must reference the assembly as UserProceduresXML, since <assembly>XML is the naming standard for the name of a serializer assembly in SQL Server.

In the code, this script is called DeploymentScript.sql. Run this script in an instance of Sql Management Studio or something similar (sqlcmd etc..). Note that when you run the script for the first time, you'll get errors on the "drop" statements, since the assemblies and procedure do not yet exist.

Note also that since we turned off automatic deployment, Visual Studio will no longer know which sql script is the test script. Therefore, right click the Test.sql script in the Solution Explorer, and select "Set as Default Debug Script". This way, when we debug the stored procedure, this script will be used as the test script to run the procedure.

 Now, when we run the stored procedure for our 3 records in the Users table, we get the following result:

From this results we can conclude that we get the correct resultfor the "EmailValid" column of the result set. The first two records have indeed a valid email address, while the last email address is invalid.