No problem and thanks for the feedback.  There are about 3 dozen XP's and UDF's in the Toolkit that I'll be covering in upcoming articles.  They are all discussed in the Windows Compiled Help (.CHM) file included in the download as well.

I'm posting this in response to an email request for some sample ASP code to encrypt/decrypt using the DBA Toolkit.  Unfortunately it's been quite a while since I used good old ASP (sans .NET), so I'll post a short ASP.NET/VB.NET snippet that shouldn't be too difficult to translate backwards.  The key items to consider here are that:

1) the UDF's accept and return VARBINARY values, which in .NET are BYTE() arrays.  Hence all the System.Text.Encoding... calls. 

2) The example assumes a few things - that your server is (local), the login is Windows Integrated security, and the default Local Key and Master Key created by the INSTALL.SQL script are still around.

Hope this helps!

Dim Bits As Integer = 256

Dim Key As String = "Local Key 1"

Dim sqlcon As System.Data.SqlClient.SqlConnection

Dim sqlcmd As System.Data.SqlClient.SqlCommand

Dim sqlcmd2 As System.Data.SqlClient.SqlCommand

Try

    Console.Writeline (Plaintext)

    sqlcon = New System.Data.SqlClient.SqlConnection("server=(local);initial catalog=master;integrated security=sspi;")

    sqlcon.Open()

    sqlcmd = New System.Data.SqlClient.SqlCommand("SELECT dbo.fn_encrypt_aes(CAST(@plaintext AS VARBINARY), @key, NULL, @bits)", sqlcon)

    sqlcmd.Parameters.Add("@plaintext", SqlDbType.VarBinary, 1000).Value = System.Text.Encoding.ASCII.GetBytes(Plaintext)

    sqlcmd.Parameters.Add("@key", SqlDbType.VarChar, 64).Value = Key

    sqlcmd.Parameters.Add("@bits", SqlDbType.Int).Value = Bits

    Dim Encryptedtext As Byte() = sqlcmd.ExecuteScalar()

    Console.Writeline (System.Text.Encoding.ASCII.GetString(Encryptedtext))

    sqlcmd2 = New System.Data.SqlClient.SqlCommand("SELECT dbo.fn_decrypt_aes(@enctext, @key, NULL, @bits)", sqlcon)

    sqlcmd2.Parameters.Add("@enctext", SqlDbType.VarBinary, 1000).Value = Encryptedtext

    sqlcmd2.Parameters.Add("@key", SqlDbType.VarChar, 64).Value = Key

    sqlcmd2.Parameters.Add("@bits", SqlDbType.Int).Value = Bits

                         = "g" convert the relevantou e the article or the Toolkit.chm file for directions)If there were some way wDim Decryptedtext As String = System.Text.Encoding.ASCII.GetString(sqlcmd2.ExecuteScalar())

    Console.Writeline (Decryptedtext)

Catch ex As Exception

    MessageBox.Show(String.Format("Error Occurred: {0}", ex.Message))

Finally

    If Not (sqlcmd2 Is Nothing) Then

        sqlcmd2.Dispose()

    End If

    If Not (sqlcmd Is Nothing) Then

        sqlcmd.Dispose()

    End If

    If Not (sqlcon Is Nothing) Then

        sqlcon.Dispose()

    End If

End Try

I have been validating the encryption/decryption stuff particularly the 3des and have run into a decryption issue that might be a bug/feature but I couldn't find anything in the docs about it.  When you create a new master key all decryption using any previous local key that uses the older master key fails.  Is there an implied limit of only having one master key in the table?

In looking into this I noticed that the fn_decrypt_des stored proc is missing a tie in the WHERE clause to the master key itself.  I changed the WHERE clause from this:

  WHERE l.[name] = @localkeyname

To this:

  WHERE l.[name] = @localkeyname and m.[Name] = l.[Master_Key_Name]

thinking it would solve the issue but even passing the proper master key to the extended stored procs doesn't allow for decryption.

-Ben

***The INSTALL.SQL script in the ZIP download has been updated with this fix.***

Hi Ben,

You're right, there is a typo in the decryption routines.  This doesn't cause an issue for a single Master Key.  It can cause an issue with multiple Master Keys, however.  The TripleDES routine should read:

ALTER FUNCTION dbo.fn_decrypt_3des (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128))
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_3des @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey
 RETURN @plaintext
END

Without the AND clause it returns the first master key it finds, which may or may not be the right one when there's more than one.  I was able to resolve the issue locally using this.  Here are all the functions that need to be updated similarly:

ALTER  FUNCTION dbo.fn_decrypt_aes (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128),
 @keybits INT)
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_aes @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey, @keybits
 RETURN @plaintext
END
GO

ALTER FUNCTION dbo.fn_decrypt_3des (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128))
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_3des @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey
 RETURN @plaintext
END
GO

ALTER  FUNCTION dbo.fn_decrypt_blowfish (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128),
 @keybits INT)
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_blowfish @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey, @keybits
 RETURN @plaintext
END
GO

ALTER  FUNCTION dbo.fn_decrypt_des (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128))
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_des @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey
 RETURN @plaintext
END
GO

ALTER  FUNCTION dbo.fn_decrypt_twofish (@enctext VARBINARY(7984),
 @localkeyname VARCHAR(128),
 @password VARCHAR(128),
 @keybits INT)
RETURNS VARBINARY(8000)
AS
BEGIN
 DECLARE @masterkey VARBINARY(256)
 DECLARE @localkey VARBINARY(256)
 SELECT @masterkey = m.[Key], @localkey = l.[Key]
 FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m
 WHERE l.[name] = @localkeyname
             AND l.[Master_Key_Name] = m.[Name]
 DECLARE @plaintext VARBINARY(8000)
 EXEC dbo.xp_decrypt_twofish @enctext, @plaintext OUTPUT, @password, @masterkey, @localkey, @keybits
 RETURN @plaintext
END
GO

This resolved the issue here for me.  I'll post an updated INSTALL script shortly.  If this doesn't resolve your issue, you might want to contact me by email to discuss some configuration specific info.

Thanks!

Sorry - I am a bit confused.

How does it help me to have the data encrypted - when a maluser who has a login (obviously the person i am trying to hide the cleratext from in the first place) can just specify the same keys that I specify in my sprocs - that he can view the source too.