Using CLR to access .NET functions in SQL Server

Using CLR to access .NET functions in SQL Server (example to execute aspx pages from asp)
Posted by: Marker | Uncategorized | 06.12.2007

Summary: An example of how we can create a function in .NET and then use them in SQL Server.

This example runs an aspx page (useful for people wanting to call aspx pages from asp)

How do we implement this functionality?

In order to use CLR we have to:

1) Write a .NET class with a public function
2) Compile the class to a DLL
3) Register the DLL with SQL Server
4) Create a SQL Server function to access the .NET function

Creating an example function and DLL

To provide a simple example that you can use, we need to first create a new project. In Visual Studio, start a new Class Library project and call it RunASPX. Then, create a new Class called RunASPX and add the following code:

Imports System.Net

Public Class RunASPX

Public Shared Function ExecutePage(ByVal sPageURL As String, ByVal sPostData As String) As String

Dim myWebClient As WebClient = Nothing
Dim myQueryStringCollection As System.Collections.Specialized.NameValueCollection
Dim mystream As System.IO.Stream
Dim sr As System.IO.StreamReader = nothing

Try

‘ Create a new WebClient instance.
myWebClient = New WebClient()

‘ Create a new NameValueCollection instance to hold the QueryString parameters and values.
myQueryStringCollection = New System.Collections.Specialized.NameValueCollection()

If sPostData.Length > 1 Then

Dim s() As String = sPostData.Split(“|”)

‘Assign the user-defined search phrase.

For i As Integer = 0 To s.Length – 1 Step 2

myQueryStringCollection.Add(s(i), s(i + 1))

Next

End If

‘ Attach QueryString to the WebClient.
myWebClient.QueryString = myQueryStringCollection

‘open url for reading – this causes the aspx page on load event to fire
mystream = myWebClient.OpenRead(sPageURL)

sr = New System.IO.StreamReader(mystream)

Return “” ‘sr.ReadToEnd

Catch ex As WebException

Return ex.Message

Finally

If Not myWebClient Is Nothing Then myWebClient.Dispose()
myWebClient = Nothing
myQueryStringCollection = Nothing
mystream = Nothing
if not sr is nothing then sr.dispose
sr = Nothing

End Try

End Function

End Class

You’ll notice that we have a simple function (which is Public Shared so that SQL Server can access it) which accepts a URL and PostData as parameters and returns nothing if successful or the webexception.message if an error occurs.

Now, we need to create a DLL out of this project so that we can register it with SQL Server. The easiest way to do this, is to simply click the Build->Build Solution menu item in VS which will build the application and create the DLL. As your project will be in debug mode the DLL will usually be found at a location such as:

C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\RunASPX\RunASPX\bin\Debug\RunASPX.dll

Once you’ve located this DLL we can copy it over to our SQL Server machine or simply make a note of this path if that happens to be the same machine as our development machine.

Turn on CLR functionality

By default, CLR is disabled in SQL Server so to turn it on we need to run this command against our database:

exec sp_configure ‘clr enabled’,1

reconfigure

Registering the DLL

In order to use the function we wrote, we first have to register the DLL with SQL Server. To do this we have to create an assembly, assign it a name and point the assembly at the DLL. Using the path to the DLL we created, run the following command against the database:

CREATE ASSEMBLY asmRunASPX FROM ‘C:\your\path\here\RunASPX.dll’

To remove the assembly type:

DROP ASSEMBLY asmRunASPX

Accessing our function from SQL Server

In order to access our .NET function, we need to create a SQL Server function which makes use of the “EXTERNAL NAME” option which informs SQL Server that we will be using a CLR function. The function we will be creating will look like this:

CREATE FUNCTION dbo.clrRunASPX

(

@sPageURL nvarchar(200),

@sPostData nvarchar(200)

)

RETURNS nvarchar(1000)

AS EXTERNAL NAME asmRunASPX.[RunASPX.RunASPX].ExecutePage

There’s two things to note about the above function. The first is that we use an nvarchar which will be the equivilant of the .NET string (it will produce an error if you try to use a varchar and the second is the format of the “EXTERNAL NAME” path. From the reading I’ve done, the format should be:

MyAssemblyName.MyClassName.MyMethodName

However, when I tried this I received an error from SQL Server stating that it couldn’t find the type, so in order to get it to work I had to use the format:

MyAssemblyName.[MyAssemblyName.MyClassName].MyMethodName

Calling the function

Now that we’ve registered our function, we should be able to call it by using the following statement:

SELECT dbo.clrRunASPX(‘http://www.somesite/somepage.aspx’,’id|123|yz|456′)

or from code:

MySQL = “select dbo.clrRunASPX(“”‘http://www.somesite/somepage.aspx’””,””‘uname|test|pwd|
a1b2c3d4′””)”

Hopefully, when you run this code you’ll get a simple “” returned in your results.

This is obviously just an example to show you how to implement CLR, but to create something useful you could look at creating functions that deal with Regular Expressions, interact with the File System or Registry, send emails or simply access any resources that are located outside of SQL Server.

One other issue that I ran into, was that extended stored procedures, although they dont error, dont necessarily return the correct results or even run when called from an external site where the calling user, or impersonated user, is not sa. This is a permissions issue and at this stage I just changed my user name in the sql connection string to sa to resolve it, but thats not the production answer. The impersonate user needs access to the procedures as well as access to the file system if it is to execute filesystem commands like the sp_FileExists function for example.

Thanks to Mark Smith for the base information. I have changed it to provide a function that people can use to run aspx pages from asp.

How to create an EXTERNAL_ACCESS or UNSAFE assembly

To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:

The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

The two conditions listed above are also checked at assembly load time (which includes execution). At least one of the conditions must be met in order to load the assembly.

We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

The following Transact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant EXTERNAL_ACCESS permission to the login. You must run the following Transact-SQL statements before running the CREATE ASSEMBLY statement.

USE master

GO

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = ‘C:MyDBAppSQLCLRTest.dll’

CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey

GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin

GO

To create an EXTERNAL ACCESS assembly, the creator needs to have EXTERNAL ACCESS permission. This is specified when creating the assembly:

CREATE ASSEMBLY SQLCLRTest

FROM ‘C:MyDBAppSQLCLRTest.dll’

WITH PERMISSION_SET = EXTERNAL_ACCESS

The following Transact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant UNSAFE permission to the login. You must run the following Transact-SQL statements before running the CREATE ASSEMBLY statement.

USE master

GO

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = ‘C:MyDBAppSQLCLRTest.dll’

CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey

GRANT UNSAFE ASSEMBLY TO SQLCLRTestLogin

GO

To specify that an assembly loads with UNSAFE permission, you specify the UNSAFE permission set when loading the assembly into the server:

CREATE ASSEMBLY SQLCLRTest

FROM ‘C:MyDBAppSQLCLRTest.dll’

WITH PERMISSION_SET = UNSAFE

For more details about the permissions for each of the settings, see CLR Integration Security.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s