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 Thursday, July 21, 2005 9:16 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:29 PM
Points: 1,480, Visits: 1,031

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.

Post #203149
Posted Thursday, July 21, 2005 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
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).

 




Post #203168
Posted Thursday, July 21, 2005 10:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134

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.

Post #203190
Posted Thursday, July 21, 2005 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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.
Post #203223
Posted Thursday, July 21, 2005 1:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 31, 2006 3:34 PM
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

Post #203286
Posted Thursday, July 21, 2005 3:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
I'll take a look at it and post something for you tonight.  Thanks!
Post #203303
Posted Thursday, July 21, 2005 3:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 31, 2006 3:34 PM
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

Post #203307
Posted Thursday, July 21, 2005 4:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134

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.

Post #203318
Posted Thursday, July 21, 2005 4:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 31, 2006 3:34 PM
Points: 98, Visits: 1

Thanks, Mike. Appreciate your prompt reply.

 

Igor

Post #203319
Posted Friday, July 22, 2005 6:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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

Post #203470
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse