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
Good article. Thanks. Thanks also for including source code and the script showing how to register the xp in sql, etc.
Mr or Mrs. 500
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.
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.
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:
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!
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:
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.
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)
Cannot load the DLL xp_rot13.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
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?
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).
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:
Sorry about that (didn't have a Win 2003 test box at the time I did this).
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,
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.
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).
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.
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.
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 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'
PRINT '--Result: PLAIN TEXT IS NOT EQUAL TO DECRYPTED TEXT'
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?
Viewing 15 posts - 1 through 15 (of 153 total)