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


Free Encryption


Free Encryption

Author
Message
Ray M
Ray M
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: 1491 Visits: 1076

I do not believe this would be a proper solution to encrypt every column in your table, I would only suggest doing this to "sensitive" Data, Ie SSN, Address, CreditCard#, etc.

It depends on your security requirements, but you should use some product that encrypts your backups, and you can even get hardware level encryption.


thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 1

A "whole system" kindof question though:

Where should the data get decrypted? For SSN's I almost think it should not be decrypted, but the user input should be encrypting and the crypted strings matched (like a hash table, but you don't have to worry about collisions); you could retrieve based on the SSN, but it would never be exposed...

Where would you store the keys?
What do people think about using a public/private key system where you would encrypt the SSN's with your public key, and do the same match above, keeping the private key on a USB fob or someplace entirely separate so even if the database is stolen, the contents cannot be decrypted...

OTOH, if you decrypt as part of a sproc/view/whatever, doesn't it get sent over the network as plaintext? Is that worrisome or am I just being too paranoid (I'm usually a dev dude, so I know very little about what goes on in the networking/biz spaces).





Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

Actually this is just a demonstration of encryption, not necessarily key management. Key management is a whole 'other topic. Windows provides key management tools via the Crypto library; it would be possible to utilize the Crypto library to perform encryption and key management via XP's as well. This functionality is integrated into .NET 1.1, and presumably will be integrated into the SQL CLR as well.

For SSN's, you might need to decrypt it, you might not. If all you're using the system for is to match or compare an SSN against the data in your table you probably don't need to decrypt it. In that case you should look into a one-way Hash function, such as MD5, etc. However, if you need to generate reports based on SSN's, decryption will be a system requirement.

You are correct, if you decrypt on the server, you will send plaintext across the wire. Keep in mind though, that encryption/decryption in one part of the system isn't going to keep the whole system secure. To protect against the sending plaintext on the wire, you can use SSH or another secure transmission protocol to communicate with your SQL Server.

Basically what encrypting data on the server provides is a degree of protection if the hard drive is physically stolen/removed from the server, or if the system is hacked and the hacker gains access to the server tables directly. As you say, there are several other areas that also need to be addressed in a 'total system' approach.

Thanks.


Chris Cathers
Chris Cathers
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 39

Well, thats some progress, I think.... ;~]

I executed the script (I had some formatting to do because it copied into Query Analizer as on long string :< ). I also had to modify the path a little. My installation is the default path, but on the D: drive. The script executed clean, except from a few "If you had an error message..." tags from DBCC. Now, however, when I run the script I get the following error message.

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL D:\Program Files\Microsoft SQL Server\MSSQL\Binn\xp_rot13.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
I verified the path, that is where the XP_Rot13.dll exists. Is there going to be a problem because my installation is on D? I hope not, all my servers have their SQL installation on a different partition from the operating system.
Please advise,
Chris Cathers.

imarchenko-236376
imarchenko-236376
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 1

Mike,

I have successdully registered all DLLs and try to run following sample code:

DECLARE @plain_text VARCHAR(500) -- Our plain text
DECLARE @enc_text VARCHAR(500) -- Our encrypted text
DECLARE @dec_text VARCHAR(500) -- Our decrypted text
DECLARE @key VARCHAR(500) -- Our encryption key
--SET @plain_text = 'Now is the time for all good men to come to the aid of their countrymen.'
set @plain_text='This is my test of encrypted message.'
EXEC master..xp_generatekey 448, @key OUTPUT
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
PRINT 'Test 3: Longer String/Longer Key'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
PRINT '--Result: PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
PRINT '--Result: PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'

The problem is that I always get the same Encrypted Text even though Key changes. Your commented code is working fine (both key and encrypted version change)

Am I doing something terribly wrong?

Thanks,

Igor


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
I'll take a look at it and post something for you tonight. Thanks!
imarchenko-236376
imarchenko-236376
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 1

Thanks, Mike. I have also noticed that it could be somehow related to the lenght of the message. If I make my testing message the same lenght as your sample, it works fine (both key and encrypted message change). Hopefully this could help you troubleshoot the problem.

Igor


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

UPDATE POSTED

I've found the issue, and I'll see if I can get the guys here to post an update to the code shortly. Basically Blowfish deals with text in multiples of 8 bytes. This is fairly normal, all modern algorithms encrypt in blocks of 8 bytes (Blowfish/DES, etc.) or blocks of 16 bytes (Rijndael/AES, etc.)

The problem is that I forgot to add the code into the XP itself to pad out the input string to 8 bytes, so Blowfish isn't encrypting it properly. As a temporary work-around, you can create a "pad" function to pad your string with spaces to make it a multiple of 8:

CREATE FUNCTION dbo.udf_PadZeroChar8(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
SET @string = @string + SUBSTRING(CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0) + CHAR(0), 1, 8 - LEN(@string) % 8)
RETURN @string
END

CREATE FUNCTION dbo.udf_StripZeroChar(@string VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @i INT
SET @i = LEN(@string)
WHILE @i > 0 AND SUBSTRING(@string, @i, 1) = CHAR(0)
SET @i = @i - 1
IF @i > 0
SET @string = SUBSTRING(@string, 1, @i)
RETURN @string
END

udf_PadZeroChar8 will right-pad your plaintext string with CHAR(0) out to the next highest multiple of 8 characters. udf_StripZeroChar will remove right-padded CHAR(0) characters from your string after a decryption. To use your example, you can use these like this:

DECLARE @plain_text VARCHAR(500) -- Our plain text
DECLARE @enc_text VARCHAR(500) -- Our encrypted text
DECLARE @dec_text VARCHAR(500) -- Our decrypted text
DECLARE @key VARCHAR(500) -- Our encryption key
--SET @plain_text = 'Now is the time for all good men to come to the aid of their countrymen.'
SET @plain_text='This is my test of encrypted message.'
EXEC master..xp_generatekey 448, @key OUTPUT
SET @plain_text = dbo.udf_PadZeroChar8(@plain_text)
EXEC master..xp_blowfishencrypt @plain_text, @key, @enc_text OUTPUT
EXEC master..xp_blowfishdecrypt @enc_text, @key, @dec_text OUTPUT
SET @dec_text = dbo.udf_StripZeroChar(@dec_text)
SET @plain_text = dbo.udf_StripZeroChar(@plain_text)
PRINT 'Test 3: Longer String/Longer Key'
PRINT '--Plain Text: ''' + @plain_text + ''''
PRINT '--Key: ''' + @key + ''''
PRINT '--Encrypted Text: ''' + @enc_text + ''''
PRINT '--Decrypted Text: ''' + @dec_text + ''''
IF @plain_text = @dec_text
PRINT '--Result: PLAIN TEXT IS EQUAL TO DECRYPTED TEXT'
ELSE
PRINT '--Result: PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
PRINT '-----------------------------------------'

Like I said, this is a temporary work-around until I can get the update posted. I apologize for the inconvenience.


imarchenko-236376
imarchenko-236376
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 1

Thanks, Mike. Appreciate your prompt reply.

Igor


Chris Cathers
Chris Cathers
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 39

Well Mike,

I found out that I have the OpenDS60.dll in the SQL Binn Directory, but MSVCRT71.dll does not exist on ANY of my servers. Know where I can get a copy?

Chris

---===<<< Edit >>>===---

I found the following article from Microsoft. MSVCRT71.dll is no longer installed with the system files and that VC++ .net packages need to re-distribute this dll in an application directory. But instead of me paraphrasing the whole article. Why don't I just give you a Link to it instead...

The Microsoft Article about MSVCRT71.dll


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