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