Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2000 DBA Toolkit Part 1


SQL 2000 DBA Toolkit Part 1

Author
Message
Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp
Muhammad Choirul Amri
Muhammad Choirul Amri
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 25
Excellent Job!
Thanks...
Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168

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.


kgayda
kgayda
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 172
Excellent article! I look forward to the next one in the series.


Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com
Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168
Thank you The next one should be out in the next couple of days.
Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168

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


Ben Westfall
Ben Westfall
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 37

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


Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168

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


AIM48
AIM48
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.


Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 1168
And why did you give a malicious user a login to your server exactly?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search