Using Symmetric key problem with encryption, decryption works fine

Using Symmetric key problem with encryption, decryption works fine

• supi

  Hey Laurentiu. I already know what the answer is supposed to be. And also, I actually dont know if I have to hash the objectid. In my code, I know that the key1 is the symmetric key I have created. I dont exactly know if I can use the @inTargetPassword as one of the arguments for EncryptByKey function. How would you do this encryption and if possible can u tell me wut each argument in the function does I read the BOL, but am a bit confused. My encryption thing worked when I did that for the whole column but now it doesn't...

  Thanks Laurentiu

  
  

• qnal

  Please correct me if I misunderstood your scenario. You are trying to compare if the user-supplied password matches the encrypted value, correct

  Encryption functions are non-deterministic. Every time you call them you will get different results, even if you used exactly the same parameters. I explain this behavior in detail in the following article: http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

  In this case, what you can do is to use a one-way hashed function, you can use hashbytes builtin in SQL Server 2005. To protect yourself against a rainbow-table attack I strongly recommend to use a salt (you can read more about rainbow tables in http://en.wikipedia.org/wiki/Rainbow_table ).

  If you still need to be able to recover the password plain text (for example, for key/password escrow), then you will need 2 columns: one for the encrypted password, and one for the hashed password. I wrote a short demo (based on fragments of your sample code) that I hope will be useful. In this demo I created a third column to store the salt, that way you can use a different salt for each row and still have easy access to it.

  Let us know if this information was useful and/or if you have further questions or feedback.

  Thanks,

  -Raul Garcia

  SDE/T

  SQL Server Engine

  /**********************************************************

  *

  * This posting is provided "AS IS" with no warranties,

  * and confers no rights.

  ***********************************************************

  *

  * (c) 2005 Microsoft Corporation. All rights reserved.

  *

  ***********************************************************/

  -- User table please modify as needed

  CREATE TABLE dbo.table1(

  UserId bigint IDENTITY PRIMARY KEY,

  Name nvarchar(100) NOT NULL,

  -- encrypted (recoverable) password

  password varbinary(256),

  -- hash( password + salt)

  hash_Password varbinary(20),

  -- salt

  hash_salt varbinary(50) )

  go

  -- I assume [key1] already exists and that is protected

  -- by certificate [sqlSecurity].

  -- In this case I am inserting some data for the demo

  OPEN SYMMETRIC KEY key1 DECRYPTION BY CERTIFICATE sqlSecurity;

  go

  declare @password varchar(128)

  declare @key_guid uniqueidentifier

  declare @salt varbinary(50)

  SET @key_guid = key_guid( 'key1' )

  -- For each user you can use a different salt

  -- User 1

  SET @password = 'MyPassword01'

  SET @salt = convert( varbinary(100), newid())

  INSERT INTO dbo.table1 values(

  N'Test User 01',

  encryptbykey(@key_guid, @password),

  hashbytes(

  'sha1',

  convert( varbinary(128), @password)+ @salt ),

  @salt )

  -- User 2

  SET @password = 'MyPassword02'

  SET @salt = convert( varbinary(100), newid())

  INSERT INTO dbo.table1 values(

  N'Test User 02',

  encryptbykey(@key_guid, @password),

  hashbytes(

  'sha1',

  convert( varbinary(128), @password)+ @salt ),

  @salt )

  -- User 3

  SET @password = 'MyPassword03'

  SET @salt = convert( varbinary(100), newid())

  INSERT INTO dbo.table1 values(

  N'Test User 03',

  encryptbykey(@key_guid, @password),

  hashbytes(

  'sha1',

  convert( varbinary(128), @password) + @salt ),

  @salt )

  go

  CLOSE SYMMETRIC KEY key1

  go

  -- Now we have the encrypted (recoverable) password,

  -- a hash( password + salt )

  -- and the salt stored in the table

  SELECT * FROM dbo.table1

  go

  -- Returns 1 if the password matches the stored password,

  -- 0 otherwise

  CREATE FUNCTION [dbo].[usp_DoesPasswordMatch]( @theUserID bigint, @thePassword varbinary(128) )

  RETURNS int

  AS

  BEGIN

  DECLARE @RetVal int

  DECLARE @hash_salt varbinary(50)

  DECLARE @hash_password varbinary(20)

  DECLARE @hash_userinput varbinary(20)

  SET @RetVal = 0 -- Assume it doesn't match

  SELECT @hash_salt=[hash_salt],

  @hash_password = [hash_password]

  from dbo.table1 where UserId=@theUserID

  if( @hash_password is not null

  AND @hash_salt is not null )

  BEGIN

  SET @hash_userinput

  = hashbytes( 'sha1', @thePassword + @hash_salt )

  if( @hash_userinput is not null

  AND @hash_userinput = @hash_password )

  BEGIN

  SET @RetVal = 1 -- The password matched

  END

  END

  return @RetVal

  END

  go

  -- Now let's test it.

  -- The first column should be 1 in all rows

  OPEN SYMMETRIC KEY key1

  DECRYPTION BY CERTIFICATE sqlSecurity;

  SELECT [dbo].[usp_DoesPasswordMatch](

  [UserId], DecryptByKey([password]) )

  as Password_matched,

  * from dbo.table1

  close symmetric key key1

  go

  -- Now we can test that if teh password doesn't match

  -- (or the user Id is invalid)

  -- the function gives back the right result

  -- should be 0 in all cases

  SELECT [dbo].[usp_DoesPasswordMatch]( 1, convert( varbinary(128), 'Dummy password') )

  SELECT [dbo].[usp_DoesPasswordMatch]( 2, 0x )

  SELECT [dbo].[usp_DoesPasswordMatch]( 3, 0xBAADF00D )

  SELECT [dbo].[usp_DoesPasswordMatch]( 7777, convert( varbinary(128), 'Dummy password') )

  go

  
  
  

• K Praveen Kumar Reddy

  I understand the documents in the links. They are very helpful. Thank you for that. Now. Let me tell you what i had done.

  There was a table with encrypted password before and we used to use a extended proc to decrypt and encrypt passwords. I used that proc to decrypt the passwords and put the usernames and passwords in a column. Once I had everything in plain text, I created a key with DES encryption and used the under to update the column to the encrypted pasword.

  OPEN SYMMETRIC KEY key1

  DECRYPTION BY CERTIFICATE sqlSecurity;

  GO

  update table1

  SET column1=CONVERT(varchar,DecryptByKey([column1]))

  close symmetric key key1

  Once I'm done with this I've to create a sp where the userID and column1(password) will be supplied in pure text and I've to encrypt the pw and insert into the table.

  Also, I need a sp to decrypt the password so I can compare it with the plain text supplied initially.I've used this so far and the decrypt one works fine. The encrypt doesn't work.Once I get the output variable I convert it into a varchar variable. The below works fine...

  ALTER procedure [dbo].[usp_DecryptPW]

  @theUserID bigint,

  @thePassword varbinary(128) output

  as

  OPEN SYMMETRIC KEY key1

  DECRYPTION BY CERTIFICATE sqlSecurity;

  SELECT @thePassword=DecryptByKey([password])

  from dbo.table1where UserId=@theUserID

  close symmetric key key1

  ---This one doesn't work. I need to encrypt it somehow... Maybe it works. I dont know. Please tell me this. I've a varchar(10) with value 'test1' When I encrypt this the first time I get a value '0x0010F4AED584E243BF79004104381D9801000000958BB8810D04063A08FFDE28A4B75693F8FC5D48D75EB9EB'. When I encrypt 'test1' in a DIFFERENT script, I get a different value '0x0010F4AED584E243BF79004104381D9801000000BCEEB58CABD30998952732B6A0649A1141CA8896204DDBBC'. Is this supposed to happen But when I decrypt those using the above I get the value 'test1'. I'm confused. Am I doing things right or am I missing something Thank you for your help, Laurentiu

  
  

• Allen LeRoy

  How do you know you are getting wrong data out of the encryption

  Also, why are you hashing the object id This is unnecessary.

  Thanks
  Laurentiu

  
  
  

• JIrwin

  The only way you can verify if the encryption worked is to attempt to decrypt. Encrypting the same piece of data twice will produce two different results. So, how did you check that the encryption didn't work

  If you encrypt with EncryptByKey(Key_GUID('Key1'), @Data), then you can decrypt by simply calling DecryptByKey() on the resulting blob and eventually casting back to the original type of @Data.

  For information on the extra parameters, see the comments to the following post:

  http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx

  For other examples that don't use the extra parameters, see this demo:

  http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx

  Your encryption statement is syntactically correct, although it does some things that might be unnecessary, but I need to see both your decryption code as well, to be able to tell you what you're doing wrong.

  If you are lookin for an example, check the above links, and if you can't find an answer there, tell me what you are trying to do.

  Thanks
  Laurentiu

  
  
  
• Using Symmetric key problem with encryption, decryption works fine