locked
Encrypted Passwords Moving from SQLServer 2000 to 2008 RRS feed

  • Question

  • Hi,

    I moved an SQL Server 2000 database to another machine using backup/restore. On the new machine it was attached to SQL Server 2008.

    The database contains an encrypted password which was encrypted on SQL Server 2000 using PWDEncrypt function. Everything seems to work fine on SQL Server 2008, apart from the fact that PWDCompare is not working correctly any more.

    My guess is that the algorithm used in encryption has changed or that the key is different, but from what I can tell, SQL Server 2000 doesn't have a MASTER KEY I can backup and restore.

    Does anyone know what I could do?

    Many thanks,

    Markus

    Thursday, May 29, 2014 3:21 PM

Answers

  • Hi,

    According to your description, when you used the PWDCOMPARE function in SQL Server 2008, did you get any errors? You can post more details for analysis.

    The PWDCOMPARE function compares the unencrypted password and the hash value of an existing password, it returns 1 if they match and 0 if they don't. The PWDENCRYPT function returns a varbinary value which is the SQL Server password hash of the input value. These two functions might not be supported in a future release of SQL Server. Usually, from SQL Server 2005 or higher version, we use symmetric keys and the ENCRYPTBYKEY function to encrypt data.  However, the functions of PWDENCRYPT and PWDCOMPARE just perform hashing and hash comparisons.

    Since SQL Server 2000 has been out of support, if you want to retrieve the encrypted data in SQL Server 2008 from SQL Server 2000, I recommend you to decrypt the data and backup your decrypted data in SQL Server 2000, and then restore the database to SQL Server 2008. And you can implement encryption using the supported encryption features in SQL Server 2008 as follows.             

    http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/                

    Thanks 

    Lydia Zhang

    Friday, May 30, 2014 1:14 PM
  • My guess is that the algorithm used in encryption has changed ...

    Hello Markus,

    I tested it on several SQL Server and yes, every differen Version returns a different result for PWDEncrypt

    select PWDEncrypt('test')
    
    -- Version 2000
    -- 0x01005D58732B14A21E86F6BE60989F1B38BD8ACC8D049C18B221C7BC03C671E38BEFD12CB8E417A3092DF48CF9D1
    
    -- Version 2005
    -- 0x0100D1A8582C0588A8EE86C62FBED2B41C857EB0EC642E59F896
    
    -- Version 2008 
    -- 0x0100983D45150BDBF80B68C8AFF622B19EFE0840B6CF04D18774
    
    -- Version 2012
    -- 0x0200A1FBF6A45524D5528BCA2344CC8E43D1E194F0B3C8C54E7CE97CCF6779725CD71BC5D53B89C95CDA73FED462874414C2ABE240E312FA9D987E2390AA8FF277FFAA3B9E40


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Saturday, May 31, 2014 12:42 PM
    • Marked as answer by Sofiya Li Friday, June 6, 2014 8:22 AM
    Friday, May 30, 2014 1:21 PM

All replies

  • Hi,

    According to your description, when you used the PWDCOMPARE function in SQL Server 2008, did you get any errors? You can post more details for analysis.

    The PWDCOMPARE function compares the unencrypted password and the hash value of an existing password, it returns 1 if they match and 0 if they don't. The PWDENCRYPT function returns a varbinary value which is the SQL Server password hash of the input value. These two functions might not be supported in a future release of SQL Server. Usually, from SQL Server 2005 or higher version, we use symmetric keys and the ENCRYPTBYKEY function to encrypt data.  However, the functions of PWDENCRYPT and PWDCOMPARE just perform hashing and hash comparisons.

    Since SQL Server 2000 has been out of support, if you want to retrieve the encrypted data in SQL Server 2008 from SQL Server 2000, I recommend you to decrypt the data and backup your decrypted data in SQL Server 2000, and then restore the database to SQL Server 2008. And you can implement encryption using the supported encryption features in SQL Server 2008 as follows.             

    http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/                

    Thanks 

    Lydia Zhang

    Friday, May 30, 2014 1:14 PM
  • My guess is that the algorithm used in encryption has changed ...

    Hello Markus,

    I tested it on several SQL Server and yes, every differen Version returns a different result for PWDEncrypt

    select PWDEncrypt('test')
    
    -- Version 2000
    -- 0x01005D58732B14A21E86F6BE60989F1B38BD8ACC8D049C18B221C7BC03C671E38BEFD12CB8E417A3092DF48CF9D1
    
    -- Version 2005
    -- 0x0100D1A8582C0588A8EE86C62FBED2B41C857EB0EC642E59F896
    
    -- Version 2008 
    -- 0x0100983D45150BDBF80B68C8AFF622B19EFE0840B6CF04D18774
    
    -- Version 2012
    -- 0x0200A1FBF6A45524D5528BCA2344CC8E43D1E194F0B3C8C54E7CE97CCF6779725CD71BC5D53B89C95CDA73FED462874414C2ABE240E312FA9D987E2390AA8FF277FFAA3B9E40


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Saturday, May 31, 2014 12:42 PM
    • Marked as answer by Sofiya Li Friday, June 6, 2014 8:22 AM
    Friday, May 30, 2014 1:21 PM