August 23, 2005 at 11:37 am
One more question. I noticed that the stored procedures define the variables as varchar. Is there any reason this wouldn't work with NVARCHAR? (double-byte characters)? I read something about blowfish in that is processes data 8 (bits or bytes?) at a time. With NVACHAR, 1 character = 2 bytes. Can anyone foresee any issues?
Thank you in advance.
August 23, 2005 at 12:57 pm
I made some minor modifications to see if the SP's and UDF's would work with nvarchar data. Specifically, I modified the SP's to use nvarchar instead of varchar.
I then modified the BlowFish UDF test to make sure I 'N' prefix all text values when inserting rows into the virtual tables. The weird thing is....it ALMOST worked. In the first section (encrypting names), the names got encrypted OK (I think), but on the decrypt, all of the names are missing the very last character. As for the SSN encrypt/decrypt test, it worked fine.
NAME ENCRYPT DECRYPT SUCCESS
________ __________________ ________ ________
ADRIANNE ???????????? ADRIANN N
ANNE ?????? ANN N
On the AES test, most of the names worked (a few didn't), and ALL of the SSN's failed.
Ideas anyone?
August 24, 2005 at 9:10 am
Actually this code was primarily designed for demonstation purposes. It wasn't developed to handle NVARCHAR's and NCHAR's specifically, which would have required utilizing the C++ wide character data types.
One issue with Blowfish is that the decryption algorithm will strip trailing ASCII character zeroes ('\0'). Your NCHAR or NVARCHAR value, using the basic Latin alphabet, will have a '\0' byte at the end. When this '\0' byte is stripped, SQL will strip the other half of your final unicode character as well (can't have an odd number of bytes in a Unicode string).
AES deals with the padding differently from Blowfish (in this implementation anyway), since it preserves trailing '\0' characters. Keep in mind that your keys must be a specific length (for AES, 128, 192 or 256 bits) in order to encrypt/decrypt properly. If you're using Unicode for a key (not recommended...), you're effectively doubling the number of bits used in your key. Make sure the total number of bits is valid for the algorithm you select.
Try eliminating the VARBINARY columns and CAST function calls in the sample app. Turn the VARBINARY into an NCHAR or NVARCHAR and try running the sample again. Just on cursory testing here, I was able to get AES to work with NCHAR and NVARCHAR just by changing the VARBINARY column in the test to NVARCHAR and converting the UDF paramters to NVARCHAR. This was just basic testing though, and not extensive by any means.
Thanks
August 24, 2005 at 9:12 am
Hi glively,
Were you able to resolve this issue? Usually this error message involves the two support files being copied to your MSSQL\BINN directory.
Thanks
August 26, 2005 at 8:07 am
Really nice work.
The only thing I noticed is when using the xp_generatekey '256' function it does not provide a long enough key for the xp_aesencrypt as I get an error stating that the key must be 128, 192 or 256 bits in length. It returns a key with a length of 44 bytes or 352 bits , not 32 bytes or 256 bits.
This is minor as I can just use left, right or substring to only select the 32 characters, just thought that you would want to know that the 256 call isn't returning 32 characters.
Thanks,
Robert
August 29, 2005 at 8:00 am
Thanks for the feedback!
Actually "xp_generatekey 256" returns a 256-bit key, but it is Base 64 encoded. This implementation of the AES crypto function doesn't use Base 64 encoding (as opposed to the BlowFish implementation which demonstrates the use of Base 64 encoding).
To use "xp_generatekey" with "xp_aesencrypt", run "xp_generatekey 256" and then run "xp_base64decode" on the result. If you grab a Substring out of the Base 64 encoded result and use that as a key, you'll limit the effectiveness of your keys, since only 64 possible bit combinations will be used in each byte (that's 1/4 of the possible combinations if you Base64Decode it first!)
Thanks!
September 2, 2005 at 10:14 am
Got it. Works perfectly.
Thanks for the help.
Robert
October 12, 2005 at 10:45 am
Mike,
Thanks for the great work. It was a breeze to install and it works. Wow.....
I am going to see if I can use it for my work instead of using JCE to do the data encryption and decryption before I insert/update/select from the database. Also, I did not have enough testing time to do the benchmark. Hope this run faster than using JCE to do encryption/decryption. I am about to write a trigger to do the cryptography, then I can do the measurement.
Thanks a million for the source code and to the point examples.....
October 12, 2005 at 12:19 pm
Hi all.
I have a question to ask - if the data is encrypted, when I do a select statement to retrieve a group of rows based on where clause. What is the best way to decrypt data since I need the data back in clear text to do some other work?
Is it best to have trigger for insert/update to encrypt the desired column?
Please advice.....
thanks
October 13, 2005 at 12:58 pm
Dear Mike C,
From reading your article, I know this is tested for SQL 2000, not SQL 7, I went ahead and tried it out for SQL 7, it did not work. I was wondering - what others dll I need beside the ones from you.
thanks
Phoebe
October 27, 2005 at 2:10 pm
Hi Phoebe,
I don't have access to a SQL 7 installation to see what would be required to make it work on SQL 7. The XP's themselves are standard XP's based on the Open Data Services interface. This same OpenDS interface is available in SQL 7, and as long as you're using the correct version of the OpenDS library and Visual C++ Runtime library DLL's theoretically it *should* work. Sometimes there can be a lot of room between "should work" and "does work" though.
There may be another issue with how the XP's are installed on your SQL 7 server? What error message does it return when you try to run the XP's on SQL 7? Maybe it can't find the appropriate DLL's? Again, I'm not 100% sure, but I'd say there's a very good chance these SP's should work with SQL 7.
As far as the trigger goes, I can see using a trigger to encrypt the data on the fly; though I didn't test it for performance using that method. You probably won't be able to decrypt the data effectively using a trigger, I would imagine; but I'm definitely no expert on triggers myself.
Thanks!
October 31, 2005 at 10:19 am
Mike,
I read and enjoyed your cryptography related articles. I installed the SQL Encryption Toolkit and am able to run all the examples wiothout any difficulty. Thanks for your work on this effort!
I am trying to use the xp_aesdecrypt routine to decrypt data stored in SQL Server 2000 which was encrypted with the System.Security.Cryptography.Rijndaelmanaged class via a C# application. When using the C# application to encrypt (or decrypt) the data, both a key and an IV (inialization vector) are used. If I use the xp_aesdecrypt with just the key, the data is not properly decrypted. Can you explain or point me in the right direction as to how I can take the IV into account with the xp_aesdecrypt extended stored procedure?
Regards!
November 20, 2005 at 7:38 pm
Hi Greg,
Thanks for the feedback. There are two issues with trying to decrypt data which has been encrypted using another package (like the .NET System.Security.Cryptography namespace classes):
You've already encountered the first issue, which is the IV (Initialization Vector). The xp_aesencrypt and xp_aesdecrypt procedures provided use the default IV that were provided by the original authors of the algorithm. The source code can be modified to change the IV as required. For those who might be wondering, the IV is used in CBC (Cypher Block Chaining) mode to supply an initial set of bytes to be XOR'd with the first block of data. Each subsequent block of data is then XOR'd with the previous block during encryption, to further obfuscate the encrypted data.
The next issue is padding. There are various standards for padding. The ANSI X9.23 standard specifies padding consists of a sequence of zeroes followed by the length of the padding. ISO-10126 consists of a sequence of random data followed by the length. There is also simple zero-padding to a fixed length multiple, and no padding. If your other algorithm uses a different form of padding from the version used by xp_aesencrypt and xp_aesdecrypt, the data used by the xp's and the external routine will not be compatible.
So, make sure of these things when using external algorithms:
1) You're using the same mode of operation (CBC, PCBC, ECB, CFB, OFB, etc.). xp_aesencrypt uses CBC mode.
2) You're using the same IV (all modes except ECB). The IV for the xp's can be modified in the source code.
3) You use the same padding mode.
Hope that helps.
December 3, 2005 at 8:56 am
Hello; before installing the tool a question: does the tool allow to encrypt the store procedure, as the unsafe "with encryption" sql server statement does?
Thanks in advance
Bruno
December 5, 2005 at 8:26 am
Hi,
I am trying to use this tool in one of my sp extracting xml text data and my problem is that I have an image to get but varchar variable is not enough for this image.
How can I fix this?
It's a way to use this tool as function?
Tks
JFB
Update myTable
Set
myTable.customer = xmlTable.cust,
myTable.address1 = xmlTable.addr1
myTable.xmlImage = xmlTable.image2
FROM OPENXML(@hDoc, '/FieldTitle1')
WITH (
Cust varchar(32) 'Request/Reference/Customer'
,ReqOpt varchar(32) 'Request/RequestOption'
,addr1 varchar(32) 'Address/addressLine1'
,addr2 varchar(32) 'Address/addressLine2'
,image2 image 'imageLabel/gifimage' ) xmlTable
Where myTable.RequetOption = xmlTable.ReqOpt
Viewing 15 posts - 31 through 45 (of 153 total)
You must be logged in to reply to this topic. Login to reply