SQL 2000 DBA Toolkit Part 1

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

  • Excellent Job!

    Thanks...

  • 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.

  • Excellent article! I look forward to the next one in the series.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • Thank you   The next one should be out in the next couple of days.

  • 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

  • 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.

  • And why did you give a malicious user a login to your server exactly?

  • I didn't - but isn't that why I am encrypting the data - so that if somebodu does get a login all hell see is gibirsh.

    If the user does not get a login in the first place - then what's the problem , why even encrypt?

  • Ok - sorry again but I am having trouble running the functions from a non master database - I keep on getting

    Could not find stored procedure 'dbo.xp_encrypt_aes'. The statement has been terminated.

    Do you have any advice thanks.

  • Extended stored procedures live in the master database.  The script to install the functions should be installing them to the master database as well.  Did the install script you downloaded not install the functions to the master database?  If you are accessing the functions from another database, the format is master.dbo.function_name.

    I guess I'm not understanding your other question.  Column level encryption prevents someone from being able to see the raw data in the tables.  It's part of an overall security system that includes physical security, communication security, etc.  If someone were to get an admin login, for instance, they will be able to do pretty much anything they want to do on your server.

  • ok - thanks a bunch for the explanations

  • Also regarding logging in - I am in a funny situation where I have users (i.e. other developers) logging in to the db with full rights (it would be very hard to lock down access to this one table  etc...) - and I don't want them to be able to see the data in a specific field.

    I guess for my situation the best approach would be for the app layer (which they do not have access to) to send the data preencrypted to the database. is that correct?

     

Viewing 15 posts - 1 through 15 (of 72 total)

You must be logged in to reply to this topic. Login to reply