Implementing CLR User Defined Function (UDF) in SQL 2008 R2

by Tom Hundley 21. April 2011 07:05

Implementing a CLR User Defined Function (UDF) in SQL 2008 R2 is very easy.  Note that this code is not limited to 2008 R2 or UDFs, and implementing CLR stored procedures is just as simple.

 

The steps are as follows:

 

1.  Create you class with all desired methods / functionality. Note that your classes and methods should be static!

 

2.  Add appropriate attributes to the methods you want to utilize as CLR objects.  For a UDF, you add the [SqlFunction] attribute.  For a CLR stored procedure, you would use [SqlProcedure].  Note that these attributes are in the Microsoft.SqlServer.Server namespace.

 

3.  It’s considered best practice to install register your assembly with SQL Server using Partial Trust.  To that end, you must Allow Partially Trusted Callers in your assembly’s AssemblyInfo class.  Do this my simply adding [assembly: AllowPartiallyTrustedCallers].

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using Microsoft.SqlServer.Server;
   6:  
   7: namespace Demo.ClrUdf
   8: {
   9:     public static class MyClrUdfClass
  10:     {
  11:         [SqlFunction]
  12:         public static string HelloWorld()
  13:         {
  14:             return "giddyup";
  15:         }
  16:     }
  17: }
   1: using System.Reflection;
   2: using System.Runtime.CompilerServices;
   3: using System.Runtime.InteropServices;
   4:  
   5: // General Information about an assembly is controlled through the following 
   6: // set of attributes. Change these attribute values to modify the information
   7: // associated with an assembly.
   8: [assembly: AssemblyTitle("Demo.ClrUdf")]
   9: [assembly: AssemblyDescription("")]
  10: [assembly: AssemblyConfiguration("")]
  11: [assembly: AssemblyCompany("Microsoft")]
  12: [assembly: AssemblyProduct("Demo.ClrUdf")]
  13: [assembly: AssemblyCopyright("Copyright © Microsoft 2011")]
  14: [assembly: AssemblyTrademark("")]
  15: [assembly: AssemblyCulture("")]
  16:  
  17: // Used becuase we're not giving the assemby full trust in SQL
  18: [assembly: AllowPartiallyTrustedCallers]
  19:  
  20:  
  21: // Setting ComVisible to false makes the types in this assembly not visible 
  22: // to COM components.  If you need to access a type in this assembly from 
  23: // COM, set the ComVisible attribute to true on that type.
  24: [assembly: ComVisible(false)]
  25:  
  26: // The following GUID is for the ID of the typelib if this project is exposed to COM
  27: [assembly: Guid("63b93754-c707-4610-b1f4-c6e5949c0a99")]
  28:  
  29: // Version information for an assembly consists of the following four values:
  30: //
  31: //      Major Version
  32: //      Minor Version 
  33: //      Build Number
  34: //      Revision
  35: //
  36: // You can specify all the values or you can default the Build and Revision Numbers 
  37: // by using the '*' as shown below:
  38: // [assembly: AssemblyVersion("1.0.*")]
  39: [assembly: AssemblyVersion("1.0.0.0")]
  40: [assembly: AssemblyFileVersion("1.0.0.0")]

 

AllowPartialTrustCallers

 

4.  Sign your assembly.  The easiest way to do this is to use a new string key name file by right clicking on your project, click on the Signing Tab, and click Sign the assembly.  This will create a PFX certificate in your project folder that is used to create the signature.

 

Sign Assembly with SKN

 

5.  You’re done with the assembly.  Now on to SQL Server.  First, you need to make sure that the CLR is enabled on the server.

   1: -- First, run this to enable the CLR.  This only has to be done once.
   2: sp_configure 'show advanced options', 1;
   3: GO
   4: RECONFIGURE;
   5: GO
   6: sp_configure 'clr enabled', 1;
   7: GO
   8: RECONFIGURE;
   9: GO
  10:  
  11:  
  12:  
  13:  

 

6.  Next, you must install / register your assembly with SQL Server.  This is simple.

   1: -- Run this, changing the names and paths appropriately (path is relative to the server)
   2: -- Note the Safe permission set- this is best practice
   3: -- Thus, you must set "[assembly: AllowPartiallyTrustedCallers]" in your AssemblyInfo.cs (and all referenced assemblies
   4: -- Note that this will automatically register dependent / referenced assemblies
   5:  
   6: USE [MyDatabase]
   7:  
   8: CREATE ASSEMBLY DemoUserDefinedFunctions
   9: FROM 'D:\Demo\Demo.ClrUdf.dll'
  10: WITH PERMISSION_SET = SAFE
  11:  
  12:  
  13: --DROP ASSEMBLY DemoUserDefinedFunctions
  14:  
  15: --SELECT * FROM sys.assemblies

 

7.  Finally, Create the CLR UDF or CLR Stored Procedure.  Here’s an example of creating a UDF with the assembly we just registered.  I used a simple Demo class for this blog, but I left one my of implementation of a real UDF in this sample so you can see how to pass parameters, etc.  Note, the only thing that might trick you up a big is the return type of the methods.  For strings, it has to support Unicode, so use nvarchar().

 

   1: -- Enable the CLR if it isn't already done
   2: -- Register the assembly
   3: -- Change the names and parameters of the UDF appropriatley
   4: -- Input and output must match the method
   5: -- Note the SQL to CLR mappings are obviously important- string only maps to unicode so we'll have to convert in our packages
   6:  
   7: USE [MyDatabase]
   8:  
   9: SET ANSI_NULLS ON
  10: GO
  11: SET QUOTED_IDENTIFIER ON
  12: GO
  13:  
  14: --CREATE FUNCTION EncryptUsernamePassword(@clearText nvarchar(255)) RETURNS nvarchar(255) 
  15: --AS EXTERNAL NAME XXXUserDefinedFunctions.[XXX.Database.Clr.UserDefinedFunctions.XXXCryptography].EncryptUsernamePassword
  16: --GO
  17:  
  18: CREATE FUNCTION HelloWorld() RETURNS nvarchar(255) 
  19: AS EXTERNAL NAME DemoUserDefinedFunctions.[Demo.ClrUdf.MyClrUdfClass].HelloWorld
  20: GO
  21:  
  22: -- DROP FUNCTION EncryptUsernamePassword
  23:  

 

8.  You’re done.  Just call your UDf or sproc from your T-SQL.  EzPz.

 

Happy Coding,

 

Tom Hundley

Tags:

C# | SQL Server

Emulating Java’s PBEWithMD5AndDES Encryption with .NET

by Tom Hundley 21. April 2011 05:03

I was recently put on a new project and tasked with emulating Java’s PBEWithMD5AndDES encryption using Microsoft .NET.  It seemed like it might be a fairly daunting task at first, but luckily Bob Janova posted a great article doing this at The Code Project.  His code implements an algorithm by Michel Gallant posted here.  I’ve tweaked his code slightly, but it’s essentially the same thing. 

 

I’m posting this just so there is another reference / resource available on the subject.  I’m not a mathematician and don’t have an in depth knowledge of cryptography, so I’m not going to attempt to explain what is going on.  You can figure most of it out from the code, and the bottom line is that works like a champ.  It’s been tested and confirmed. 

 

 

DemoEncryption.cs:

   1: // *************************************************************************
   2: // <copyright file="DemoEncryption.cs" company="Elegant Software Solutions, LLC">
   3: //     Copyright (C) 2011 Elegant Software Solutions, LLC.  All rights reserved worldwide.
   4: // </copyright>
   5: // *************************************************************************
   6:  
   7: namespace Demo.Cryptography
   8: {
   9:     using System;
  10:     using System.Collections.Generic;
  11:     using System.Linq;
  12:     using System.Security.Cryptography;
  13:     using System.Text;
  14:  
  15:     /// <summary>
  16:     /// This class is used to perform username and password encryption in the Demo java system.
  17:     /// </summary>
  18:     public class DemoEncryption
  19:     {
  20:         /// <summary>
  21:         /// Use this method to encrypt usernames and passwords in the Demo user table.  The password and salt are hardcoded into this method.
  22:         /// </summary>
  23:         /// <param name="clearText">This is the clear text you wish to encrypt.</param>
  24:         /// <returns>Returns the encrypted version of the clear text.</returns>
  25:         public string EncryptUsernamePassword(string clearText)
  26:         {
  27:             // TODO: Parameterize the Password, Salt, and Iterations.  They should be encrypted with the machine key and stored in the registry
  28:             
  29:             if (string.IsNullOrEmpty(clearText))
  30:             {
  31:                 return clearText;
  32:             }
  33:            
  34:             byte[] salt = new byte[]
  35:             {
  36:                 (byte)0xc7,
  37:                 (byte)0x73,
  38:                 (byte)0x21,
  39:                 (byte)0x8c,
  40:                 (byte)0x7e,
  41:                 (byte)0xc8,
  42:                 (byte)0xee,
  43:                 (byte)0x99
  44:             };
  45:             
  46:             // NOTE: The keystring, salt, and iterations must be the same as what is used in the Demo java system.
  47:             PKCSKeyGenerator crypto = new PKCSKeyGenerator("MyPassword", salt, 20, 1);
  48:             
  49:             ICryptoTransform cryptoTransform = crypto.Encryptor;
  50:             byte[] cipherBytes = cryptoTransform.TransformFinalBlock(Encoding.UTF8.GetBytes(clearText), 0, clearText.Length);
  51:             return System.Convert.ToBase64String(cipherBytes);
  52:         }
  53:  
  54:         /// <summary>
  55:         /// Use this method to decrypt usernames and passwords in the Demo user table.  The password and salt are hardcoded into this method.
  56:         /// </summary>
  57:         /// <param name="clearText">This is the cipher text you wish to decrypt.</param>
  58:         /// <returns>Returns the decrypted version of the cipher text.</returns>
  59:         public string DecryptUsernamePassword(string cipherText)
  60:         {
  61:             if (string.IsNullOrEmpty(cipherText))
  62:             {
  63:                 return cipherText;
  64:             }
  65:  
  66:             byte[] salt = new byte[]
  67:             {
  68:                 (byte)0xc7,
  69:                 (byte)0x73,
  70:                 (byte)0x21,
  71:                 (byte)0x8c,
  72:                 (byte)0x7e,
  73:                 (byte)0xc8,
  74:                 (byte)0xee,
  75:                 (byte)0x99
  76:             };
  77:  
  78:             // NOTE: The keystring, salt, and iterations must be the same as what is used in the Demo java system.
  79:             PKCSKeyGenerator crypto = new PKCSKeyGenerator("MyPassword", salt, 20, 1);
  80:  
  81:             ICryptoTransform cryptoTransform = crypto.Decryptor;
  82:             byte[] cipherBytes = System.Convert.FromBase64String(cipherText);
  83:             byte[] clearBytes = cryptoTransform.TransformFinalBlock(cipherBytes, 0, cipherBytes.Length);
  84:             return Encoding.UTF8.GetString(clearBytes);
  85:         }
  86:     }
  87: }

 

 

PKCSKeyGenerator.cs:

 

   1: // *************************************************************************
   2: // <copyright file="PKCSKeyGenerator.cs" company="Elegant Software Solutions, LLC">
   3: //     Portions Copyright (C) 2011 Elegant Software Solutions, LLC.  All rights reserved worldwide.
   4: // </copyright>
   5:  
   6: // PKCSKeyGenerator.cs
   7: // Derive key material using PKCS #1 v1.5 algorithm with MD5 hash
   8: //
   9: // Portions Copyright (C) 2005.  Michel I. Gallant
  10: // Portions copyright 2006 Richard Smith
  11: // Adapted from http://www.jensign.com/JavaScience/dotnet/DeriveKeyM/index.html
  12: //
  13: // *************************************************************************
  14: //
  15: //  DeriveKeyM.cs
  16: //
  17: //  Derive a key from a pswd and Salt using MD5 and PKCS #5 v1.5 approach
  18: //   see also:   http://www.openssl.org/docs/crypto/EVP_BytesToKey.html
  19: //   see also:   http://java.sun.com/j2se/1.5.0/docs/guide/security/jce/JCERefGuide.html#PBE
  20: //
  21: // **************************************************************************
  22:  
  23: namespace Demo.Cryptography
  24: {
  25:     using System;
  26:     using System.IO;
  27:     using System.Security.Cryptography;
  28:     using System.Text;
  29:  
  30:     /// <summary>
  31:     /// This class is used to emulate the Java based PBEWithMD5AndDES functionality of the Demo system.
  32:     /// </summary>
  33:     public class PKCSKeyGenerator
  34:     {
  35:         /// <summary>
  36:         /// Key used in the encryption algorythm.
  37:         /// </summary>
  38:         private byte[] key = new byte[8];
  39:  
  40:         /// <summary>
  41:         /// IV used in the encryption algorythm.
  42:         /// </summary>
  43:         private byte[] iv = new byte[8];
  44:         
  45:         /// <summary>
  46:         /// DES Provider used in the encryption algorythm.
  47:         /// </summary>
  48:         private DESCryptoServiceProvider des = new DESCryptoServiceProvider();
  49:         
  50:         /// <summary>
  51:         /// Initializes a new instance of the PKCSKeyGenerator class.
  52:         /// </summary>
  53:         public PKCSKeyGenerator()
  54:         { 
  55:         }
  56:         
  57:         /// <summary>
  58:         /// Initializes a new instance of the PKCSKeyGenerator class.
  59:         /// </summary>
  60:         /// <param name="keystring">This is the same as the "password" of the PBEWithMD5AndDES method.</param>
  61:         /// <param name="salt">This is the salt used to provide extra security to the algorythim.</param>
  62:         /// <param name="iterationsMd5">Fill out iterationsMd5 later.</param>
  63:         /// <param name="segments">Fill out segments later.</param>
  64:         public PKCSKeyGenerator(string keystring, byte[] salt, int iterationsMd5, int segments)
  65:         {
  66:             this.Generate(keystring, salt, iterationsMd5, segments);
  67:         }
  68:  
  69:         /// <summary>
  70:         /// Gets the asymetric Key used in the encryption algorythm.  Note that this is read only and is an empty byte array.
  71:         /// </summary>
  72:         public byte[] Key
  73:         {
  74:             get
  75:             {
  76:                 return this.key;
  77:             }
  78:         }
  79:  
  80:         /// <summary>
  81:         /// Gets the initialization vector used in in the encryption algorythm.  Note that this is read only and is an empty byte array.
  82:         /// </summary>
  83:         public byte[] IV
  84:         {
  85:             get
  86:             {
  87:                 return this.iv;
  88:             }
  89:         }
  90:  
  91:         /// <summary>
  92:         /// Gets an ICryptoTransform interface for encryption
  93:         /// </summary>
  94:         public ICryptoTransform Encryptor
  95:         {
  96:             get
  97:             {
  98:                 return this.des.CreateEncryptor(this.key, this.iv);
  99:             }
 100:         }
 101:  
 102:         /// <summary>
 103:         /// Gets an ICryptoTransform interface for decryption
 104:         /// </summary>
 105:         public ICryptoTransform Decryptor 
 106:         { 
 107:             get 
 108:             { 
 109:                 return des.CreateDecryptor(key, iv); 
 110:             } 
 111:         }
 112:  
 113:         /// <summary>
 114:         /// Returns the ICryptoTransform interface used to perform the encryption.
 115:         /// </summary>
 116:         /// <param name="keystring">This is the same as the "password" of the PBEWithMD5AndDES method.</param>
 117:         /// <param name="salt">This is the salt used to provide extra security to the algorythim.</param>
 118:         /// <param name="iterationsMd5">Fill out iterationsMd5 later.</param>
 119:         /// <param name="segments">Fill out segments later.</param>
 120:         /// <returns>ICryptoTransform interface used to perform the encryption.</returns>
 121:         public ICryptoTransform Generate(string keystring, byte[] salt, int iterationsMd5, int segments)
 122:         {
 123:             // MD5 bytes
 124:             int hashLength = 16;
 125:             
 126:             // to store contatenated Mi hashed results
 127:             byte[] keyMaterial = new byte[hashLength * segments];
 128:  
 129:             // --- get secret password bytes ----
 130:             byte[] passwordBytes;
 131:             passwordBytes = Encoding.UTF8.GetBytes(keystring);
 132:  
 133:             // --- contatenate salt and pswd bytes into fixed data array ---
 134:             byte[] data00 = new byte[passwordBytes.Length + salt.Length];
 135:             
 136:             // copy the pswd bytes
 137:             Array.Copy(passwordBytes, data00, passwordBytes.Length);
 138:             
 139:             // concatenate the salt bytes
 140:             Array.Copy(salt, 0, data00, passwordBytes.Length, salt.Length);
 141:  
 142:             // ---- do multi-hashing and contatenate results  D1, D2 ...  into keymaterial bytes ----
 143:             MD5 md5 = new MD5CryptoServiceProvider();
 144:             byte[] result = null;
 145:  
 146:             // fixed length initial hashtarget
 147:             byte[] hashtarget = new byte[hashLength + data00.Length];   
 148:  
 149:             for (int j = 0; j < segments; j++)
 150:             {
 151:                 // ----  Now hash consecutively for iterationsMd5 times ------
 152:                 if (j == 0)
 153:                 {
 154:                     // initialize
 155:                     result = data00;
 156:                 }
 157:                 else
 158:                 {
 159:                     Array.Copy(result, hashtarget, result.Length);
 160:                     Array.Copy(data00, 0, hashtarget, result.Length, data00.Length);
 161:                     result = hashtarget;
 162:                 }
 163:  
 164:                 for (int i = 0; i < iterationsMd5; i++)
 165:                 {
 166:                     result = md5.ComputeHash(result);
 167:                 }
 168:  
 169:                 // contatenate to keymaterial
 170:                 Array.Copy(result, 0, keyMaterial, j * hashLength, result.Length);  
 171:             }
 172:             
 173:             Array.Copy(keyMaterial, 0, this.key, 0, 8);
 174:             Array.Copy(keyMaterial, 8, this.iv, 0, 8);
 175:             
 176:             return this.Encryptor;
 177:         }
 178:     }
 179: }

 

I hope this helps someone!  I’ve attached a project with the two files as well.  It’s in VS 2008 but will upgrade to 2010 with no problem.  Enjoy.

 

Tom Hundley

Tags:

C#

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

by Tom Hundley 20. May 2010 11:58

Let’s start by saying the code in this example is a really silly, but it’s a great reference for the matter at hand.  Let’s assume you need to do a join on multiple columns and you’ve double checked and triple checked that the types are all the same, but you’re stuck with the following error:  The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.  The problem is related to anonymous types.  The type initializer cannot infer the same anonymous type because the names are not the same.  The solution is simple: specify the name in one of the expressions such that they are the same and the problem is solved.

 

Problem Code:

image

 

 

Solution:

image

 

Now that the types AND the *names* are the same, the anonymous type initializer can create the same anonymous type and properly facilitate the join.

 

Happy coding,

 

Tom Hundley

Elegant Software Solutions, LLC

 

Reference:

Thanks to Matt Warren from Microsoft:

http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/bf98ec7a-cb80-4901-8eb2-3aa6636a4fde

Tags: , , ,

C# | Linq