Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL 2000 DBA Toolkit Part 1 Expand / Collapse
Author
Message
Posted Thursday, April 6, 2006 11:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp
Post #271678
Posted Thursday, April 13, 2006 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 11, 2014 5:30 AM
Points: 9, Visits: 25
Excellent Job!
Thanks...
Post #272938
Posted Thursday, April 13, 2006 7:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #273065
Posted Thursday, April 13, 2006 2:23 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168
Excellent article! I look forward to the next one in the series.


Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com
Post #273240
Posted Tuesday, April 18, 2006 3:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Thank you   The next one should be out in the next couple of days.
Post #273797
Posted Wednesday, April 19, 2006 7:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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 Plaintext As String = "This is some Plain Text"

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

Post #274056
Posted Friday, April 21, 2006 12:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:14 PM
Points: 21, Visits: 35

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

 

Post #274595
Posted Friday, April 21, 2006 2:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

***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!

Post #274640
Posted Monday, June 5, 2006 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:16 PM
Points: 6, Visits: 13

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.

Post #284965
Posted Monday, June 5, 2006 2:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
And why did you give a malicious user a login to your server exactly?
Post #285030
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse