SQL Server CLR Functions
You can create a database object inside an instance of SQL Server
that is programmed in an assembly created in the Microsoft .NET
Framework common language runtime (CLR). Database objects that can
leverage the rich programming model provided by the common language
runtime include aggregate functions, functions, stored procedures,
triggers, and types.
Creating a CLR function in SQL Server involves the following steps:
-
Define the function as a static method of a class in a
language supported by the .NET Framework. For more information about how
to program functions in the common language runtime, see CLR User-Defined Functions. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
-
Register the assembly in SQL Server by using the CREATE
ASSEMBLY statement. For more information about assemblies in SQL
Server, see Assemblies (Database Engine).
-
Create the function that references the registered assembly by using the CREATE FUNCTION statement.
CLR functions can be used to access external resources such as files,
network resources, Web Services, other databases (including remote
instances of SQL Server). This can be achieved by using various classes
in the .NET Framework, such as System.IO, System.WebServices, System.Sql,
and so on. The assembly that contains such functions should at least be
configured with the EXTERNAL_ACCESS permission set for this purpose.
For more information, see CREATE ASSEMBLY (Transact-SQL).
The SQL Client Managed Provider can be used to access remote instances
of SQL Server. However, loopback connections to the originating server
are not supported in CLR functions.
- Create the CLR Dll for the SQL function to use, and copy it to SQL Binn
- Register the CLR Dll in SQL server
- Create a normal SQL function that uses the CLR Dll
1: using System;
2: using System.Collections.Generic;
3: using System.Text;
4: using Microsoft.SqlServer.Server;
5: using System.Data;
6: using System.Data.Sql;
7: using System.Data.SqlTypes;
8: using Encrypt;
9:
10: public class StoredProcedures
11: {
12:
13: [Microsoft.SqlServer.Server.SqlFunction()]
14: public static string Enc(SqlString password, SqlString encStringOut)
15: {
16: Encryption enc = new Encryption();
17: return enc.Encrypt(password.ToString(), encStringOut.ToString());;
18: }
19:
20: [Microsoft.SqlServer.Server.SqlFunction()]
21: public static string Dec(SqlString password, SqlString encStringOut)
22: {
23: Encryption enc = new Encryption();
24: return enc.Decrypt(password.ToString(), encStringOut.ToString()); ;
25: }
26: }
So next we need to do the SQL server work. So firstly I copied the
SQLServerEncryption.Dll to the C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn directory of the SQL server machine.
I also copied the Dll generated (SQLServerEncryption.Dll) to the C:\
drive on the SQL server machine, as it makes the Dll registration code
that needs to be run for SQL a bit easier.
So we’ve copied to \binn and C:\ so far, so now we need to register the Dll with SQL server. So lets look at that
Firstly we need to allow CLR types in the SQL server installation. Which is either done using the following SQL
EXEC dbo.sp_configure ‘clr enabled’,1 RECONFIGURE WITH
Or if you have issues doing it that way use the SQL Server Surface Area
Configuration, use the “Surface Area Configuration For Features” link,
and then CLR integration from the presented treeview. Once this is done
we can register the CLR Dll with SQL, as follows
create assembly SQLServerEncryption from ‘c:SQLServerEncryption.dll’ WITH PERMISSION_SET = SAFE
Now that weve done that, all thats left to do is create a normal SQL
server function that uses the CLR Dll. Which is simply done as follows
ALTER FUNCTION [dbo].[ENCRYPT](@password [nvarchar](255), @encStringOut [nvarchar](255))
RETURNS [nvarchar](255) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerEncryption].[StoredProcedures].[Enc]
And that it you can now use the CLR Function as you like. For example
dbo.ENCRYPT(‘xxxx’,’sb_SQL’)
