Free Encryption

Question

Post reply

Free Encryption

Mike C

SSC-Insane

Points: 23224
More actions
July 11, 2005 at 1:00 pm

#186585

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp
Get the new SQL 2000 DBA Toolkit with five encryption algorithms, hashing, phonetic matching, regular expressions and more at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

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

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

N Cook SSC Eights! Points: 981 More actions · Answer 1

Good article. Thanks. Thanks also for including source code and the script showing how to register the xp in sql, etc.

Nate

Lambchop4697 Mr or Mrs. 500 Points: 599 More actions · Answer 2

Very good code compilation! I have just started into this subject recently with lots of things still to learn and understand. I am sorry for my ignorance, but I want to ask a couple of dumb questions

Would I encrypt all of my data? and if I do, then how does data that gets returned get encrypted/decrypted through this process? I am probably missing something obvious - any further discussion on problem - solution examples would be really good for me who "thinks" I understand the basics of SQL Server.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

Thanks for the feedback. I wouldn't go so far as to encrypt all your data. If that's what you're looking to do, then you should probably look for a file-level encryption service. With column level encryption it's better to pick and choose which columns are most sensitive. For instance, an SSN column might be a prime candidate for encryption.

As for encryption/decryption, there are two xp's for that: xp_blowfishencrypt will encrypt your data; xp_blowfishdecrypt will perform the decryption for you. A standard process might look like this:

1. Load data into table

2. Encrypt sensitive column(s)

3. When user requests data that includes encrypted data, decrypt the data before returning it to the user.

4. If the user updates the data that is to be stored in an encrypted column, be sure to encrypt it again before updating it.

There are some sample SQL scripts included with the code, in the \SampleSQL directory of the ZIP file. Just load them into Query Analyzer and run them. I provided them as SQL scripts to make it easier to figure out how to use them in your own applications.

Mike C SSC-Insane Points: 23224 More actions · Answer 4

The good folks here at SQLServerCentral have uploaded the updated SQL Encryption Toolkit. To get the updated version, just click on any of the download links anywhere in the article. Installation instructions are in the README.TXT and INSTALL-NOTES.RTF files.

Thanks Steve, Andy and Brian!

Here are the update notes:

UPDATE

I've updated the files to fix the padding issue and added a couple more features:

Padding: All modern encryption algorithms operate on data in blocks of 8 or 16 bytes. Blowfish encrypts in blocks of 8 bytes. In order for Blowfish to operate on plain text that is not a multiple of 8 bytes in length, the plain text has to be padded. One FIPS approved method of padding is to right-pad with ASCII character 0 when encrypting, and strip off trailing ASCII character 0's when decrypting. I have modified xp_blowfishencrypt and xp_blowfishdecrypt to perform to this standard. Note that your encrypted data will be slightly larger if it must be right-padded with ASCII character 0's to the nearest 8 bytes.

Embedded Zero in Key: There was an issue that affected blowfish encryption keys with an ASCII character zero embedded in them ('\0' for you C/C++ programmers). This issue has been resolved with this update. Thanks to Ed Klichinsky for locating and diagnosing this issue.

XP_ADD.SQL: A typo in the XP_ADD.SQL script that gave the udf_blowfishencrypt function the wrong name (it was incorrectly named fn_blowfishencrypt) was fixed.

Unnecessary Directory: The unnecessary \DLLs directory was removed (it contained some intermediate compilations; all final compilations are in the \Install directory).

Support DLLs: The two support DLLs that Microsoft recommends be redistributed were added in a directory called \Redist. These two files are OPENDS60.DLL and MSVCR71.DLL. These files may be required on some Windows 2003 installations. Directions for using these two files are located in the file INSTALL-NOTES.RTF. Directions for modifying your ADD_XP.SQL script, if necessary, are also included in this file. Special thanks to Chris Cathers for his help in troubleshooting this!

ADDITIONS

This fix is primarily to fix these issues, but I've also added a couple of items:

DROP_XP.SQL: I've added a DROP_XP.SQL script to drop the extended stored procedures and UDF’s installed by ADD_XP.SQL. This is useful if you want to uninstall (maybe for a clean reinstall?)

Advanced Encryption Standard (AES)/Rijndael: I've added AES encryption via the xp_aesencrypt and xp_aesdecrypt functions. Here's an overview:

Padding: These functions have another form of FIPS-approved padding built in (namely they are right-padded with ASCII character 0, with the very last character containing the count of padding characters). This allows you to encrypt strings/data that ends with ASCII 0 characters. Note that this FIPS padding method expands 15-byte plain text to 16-bytes of encrypted text, and 16-byte plain text is padded to 32 bytes of encrypted text. I.e., if your plain text is a multiple of 16 bytes, 16 bytes of padding will be added.
Encryption Blocks: AES/Rijndael encrypts 16-byte blocks of data, as opposed to Blowfish which encrypts 8 byte blocks.
Keys: AES uses 128, 192 or 256-bit keys (16, 24 or 32 bytes). Examples of key usage are given in the sample SQL scripts.
UDFs: udf_aesencrypt and udf_aesdecrypt are included to wrap the xp’s in user-defined functions.
Scripts: Additional test scripts showing how to use AES encryption are included.
Encoding: The AES encryption functions provided do *not* use Base64 encoding, so no base64 conversions are necessary. They have been tested on CHAR, VARCHAR, BINARY and VARBINARY data.

I'd also be interested to know which padding method you find most useful, and whether you prefer your encrypted text be Base64 encoded or if you prefer standard 8-bit binary encoding.

Chris Cathers SSC Veteran Points: 218 More actions · Answer 5

I've got a problem with the DLLs, or something...

I've copied the DLL's, from the INSTALL directory, to my MSSQL\BINN directory. I ran the ADD_XP.SQL script, and it compleeted successfully. I've even ran the Blowfish Function correction script.

When I attempt to run the ROT13.SQL sample script I get the following error message three time (Once fore each XP call)

ODBC: Msg 0, Level 16, State 1

Cannot load the DLL xp_rot13.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

Any idea what's going on? I didn't see anything in the Readme about restarting SQL, or the box, which is a SQL 2000 (Ver 8.00.194) installed on a Windows 2003 server.

Thanks in advance

Chris Cathers

Mike C SSC-Insane Points: 23224 More actions · Answer 6

You shouldn't have to re-start SQL after running the script. The xp's are installed in the "master" database (the only place xp's can be installed, by the way). Are you running the tests from within the "master" database?

Stephen Atkins Valued Member Points: 55 More actions · Answer 7

I'm also having the same problem with Win2003 server and mssql 2000. It does work on my personal machine which is running msde 2000 and WinXP sp2. Could it be that the server is missing a dll that is on my development machines (it has VB6 VCC6 interdev and a bunch of other ide's).

Stephen

Mike C SSC-Insane Points: 23224 More actions · Answer 8

According to the best information I could find it is most likely that SQL Server can't locate a referenced DLL, i.e., MFC40.DLL as you mentioned. I just checked it against a Win 2003 box and got the same result. It appears that there's a DLL out there that's not on the System Path on Win 2003. I'll keep looking for it and let you know what I find. Here's an MS article on it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;151596

Sorry about that (didn't have a Win 2003 test box at the time I did this).

Chris Cathers SSC Veteran Points: 218 More actions · Answer 9

Thanks for your prompt response on this. I looked at the article you posted, It was a little beyond me (Developer stuff and all...

I was in the master DB when executing the scripts, but I guess that's not relevant afterall...

Hope to see your solution posted soon,

Chris Cathers.

Ray M SSC-Insane Points: 21093 More actions · Answer 10

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 Ten Centuries Points: 1217 More actions · Answer 11

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 SSC-Insane Points: 23224 More actions · Answer 12

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 SSC Veteran Points: 218 More actions · Answer 13

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 SSCommitted Points: 1734 More actions · Answer 14

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