Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Free Encryption Expand / Collapse
Author
Message
Posted Monday, July 11, 2005 1:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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

 

Post #199534
Posted Thursday, July 21, 2005 7:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, Visits: 22

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

Nate

Post #203076
Posted Thursday, July 21, 2005 7:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 4, 2013 2:16 PM
Points: 16, Visits: 39

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.

 




Post #203077
Posted Thursday, July 21, 2005 7:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #203095
Posted Thursday, July 21, 2005 7:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #203102
Posted Thursday, July 21, 2005 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 8:53 AM
Points: 10, Visits: 3

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
Post #203121
Posted Thursday, July 21, 2005 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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?
Post #203123
Posted Thursday, July 21, 2005 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2007 12:44 PM
Points: 1, Visits: 1
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
Post #203125
Posted Thursday, July 21, 2005 9:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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).

Post #203143
Posted Thursday, July 21, 2005 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 8:53 AM
Points: 10, Visits: 3

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.

Post #203148
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse