how to encrypt password column in sql server 2005

所有回覆

• 2012年5月23日 上午 09:03

   

   
  

  0

  Please see this link

  Protect Sensitive Data Using Encryption in SQL Server 2005

  vt

  ---

  Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

  • 已提議為解答 Naomi NMicrosoft Community Contributor, Moderator 2012年5月23日 下午 01:12
  • 已標示為解答 Iric WenModerator 2012年5月30日 下午 02:09
  •  
  • 回覆
  • 引述

   
• 2012年5月23日 上午 09:04

   

   
  

  0

  Check this article. SQL Server 2005 has support for column encryption.

  http://msdn.microsoft.com/en-us/library/ms179331(v=sql.90).aspx

  • 已提議為解答 Naomi NMicrosoft Community Contributor, Moderator 2012年5月23日 下午 01:12
  • 已標示為解答 Iric WenModerator 2012年5月30日 下午 02:09
  •  
  • 回覆
  • 引述

   
• 2012年5月23日 上午 09:05

  解答者

   

   
  

  0

  Most secure way is not storing passwords in database at all  but only  hash value to compare 

  Below is a C# version that you can use before 
  sending the value to SQL Server, which is a better approach, IMHO.
  
     var md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
     var encoding = new System.Text.ASCIIEncoding();
     byte[] passwordHash = md5.ComputeHash(encoding.GetBytes(password));
  
  ---Below is a snippet that compare the hash value from the database
  --- to the user-supplied one for password validation:
  
    //compare password hash
    if(compareHashes(databasePasswordHash, userSuppliedPasswordHash))
    {
     //password is valid
    }
    else
    {
     //password is invalid
    }
  
  
    //Helper function
    private bool compareHashes(byte[] value1, byte[] value2)
    {
     if (value1.Length != value2.Length)
     {
      return false;
     }
     for (int i = 0; i < value1.Length; ++i)
     {
      if (value1[i] != value2[i])
      {
       return false;
      }
     }
     return true;
    }
  

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • 已提議為解答 Naomi NMicrosoft Community Contributor, Moderator 2012年5月23日 下午 01:12
  • 已標示為解答 Iric WenModerator 2012年5月30日 下午 02:09
  •  
  • 回覆
  • 引述

   
• 2012年5月23日 上午 09:06

   

   
  

  0

  The following example updates a record in the SalesCreditCard table and encrypts the value of the credit card number stored in column CardNumber_EncryptedbyPassphrase, using the primary key as an authenticator.

  USE AdventureWorks2012;
  GO
  -- Create a column in which to store the encrypted data.
  ALTER TABLE Sales.CreditCard 
      ADD CardNumber_EncryptedbyPassphrase varbinary(256); 
  GO
  -- First get the passphrase from the user.
  DECLARE @PassphraseEnteredByUser nvarchar(128);
  SET @PassphraseEnteredByUser 
      = 'A little learning is a dangerous thing!';
  
  -- Update the record for the user's credit card.
  -- In this case, the record is number 3681.
  UPDATE Sales.CreditCard
  SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
      , CardNumber, 1, CONVERT( varbinary, CreditCardID))
  WHERE CreditCardID = '3681';
  GO
  

  ---

  Many Thanks & Best Regards, Hua Min

  • 回覆
  • 引述

   
• 2012年5月23日 上午 10:55

   

   
  

  0

  Hi,

  Please look at this link 

  http://msdn.microsoft.com/en-us/library/ms179331(v=sql.90).aspx

  this is a sample from MSDN

  USE AdventureWorks;
  GO
  
  --If there is no master key, create one now. 
  IF NOT EXISTS 
      (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
      CREATE MASTER KEY ENCRYPTION BY 
      PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
  GO
  
  CREATE CERTIFICATE HumanResources037
     WITH SUBJECT = 'Employee Social Security Numbers';
  GO
  
  CREATE SYMMETRIC KEY SSN_Key_01
      WITH ALGORITHM = AES_256
      ENCRYPTION BY CERTIFICATE HumanResources037;
  GO
  
  USE [AdventureWorks];
  GO
  
  -- Create a column in which to store the encrypted data.
  ALTER TABLE HumanResources.Employee
      ADD EncryptedNationalIDNumber varbinary(128); 
  GO
  
  -- Open the symmetric key with which to encrypt the data.
  OPEN SYMMETRIC KEY SSN_Key_01
     DECRYPTION BY CERTIFICATE HumanResources037;
  
  -- Encrypt the value in column NationalIDNumber with symmetric 
  -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
  UPDATE HumanResources.Employee
  SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
  GO
  
  -- Verify the encryption.
  -- First, open the symmetric key with which to decrypt the data.
  OPEN SYMMETRIC KEY SSN_Key_01
     DECRYPTION BY CERTIFICATE HumanResources037;
  GO
  
  -- Now list the original ID, the encrypted ID, and the 
  -- decrypted ciphertext. If the decryption worked, the original
  -- and the decrypted ID will match.
  SELECT NationalIDNumber, EncryptedNationalIDNumber 
      AS 'Encrypted ID Number',
      CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
      AS 'Decrypted ID Number'
      FROM HumanResources.Employee;
  GO

  
  

  Regards
  Satheesh
  -----------------------------------------------
  Mark this as answer if the post helped

  
  

  • 已編輯 Satheesh Variath 2012年5月23日 上午 11:17
  •  
  • 回覆
  • 引述

   
• 2012年5月23日 上午 11:03

   

   
  

  0

  Uri is right, don't store passwords. See this thread for a plain T-SQL example.

  btw, change the your threads type from discussion to question. thx.

  • 回覆
  • 引述