SQL Server CLR Functions

 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. 
  
  

1. Create the CLR Dll for the SQL function to use, and copy it to SQL Binn
2. Register the CLR Dll in SQL server
3. 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’)