locked
sp_migrate_user_to_contained cannot be used to copy a password to an old hash algorithm. RRS feed

  • Question

  • Migrated logins from SQL 2005 to SQL 2012 using Idera tools that create SQL using the following format:

    CREATE LOGIN[ExampleLogin]WITHPASSWORD= 0x0100AD544E29127BC8FBC812383B896066C842BD5D0A825C4567 HASHED,SID= 0x52B68AA1F7EC1F4EBB2C3DEABAD0E779,DEFAULT_LANGUAGE=us_english,CHECK_POLICY=OFF,CHECK_EXPIRATION=OFF

    When trying to convert these logins to Contained Database logins using this SQL

    sp_migrate_user_to_contained 
    @username = N'ExampleLogin',
    @rename = N'keep_name',
    @disablelogin = N'disable_login' ;

    I am getting the following error:

    Msg 12822, Level 16, State 1, Procedure sp_migrate_user_to_contained, Line 1
    sp_migrate_user_to_contained cannot be used to copy a password to an old hash algorithm.

    Do I need to migrate my logins using some other method?

    Monday, October 22, 2012 12:10 AM

Answers

  • Hi Michelle,

    I am afraid that login's old password hash must be changed. CREATE LOGIN's HASHED argument is telling SQL Server the login's password argument has already been hashed. Msg 12822 is designed to be raised when a login is using an older-than-SHA2 hash. To resolve the 12822, login to SQL Server (which updates the hash algorithm), or change the password.

    Thanks,

    Cathy Miller

    • Marked as answer by Maggie Luo Monday, November 5, 2012 9:43 AM
    Tuesday, October 30, 2012 7:13 PM

All replies

  • Hi AnonymousUserPerson,

    Below article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

    How to transfer logins and passwords between instances of SQL Server: http://support.microsoft.com/kb/918992.

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Thanks.


    Maggie Luo

    TechNet Community Support

    • Marked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    • Unmarked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    Monday, October 22, 2012 2:57 AM
  • Hi Maggie,

    It will work as you provided but this should also work right-

    sp_migrate_user_to_contained
    @username = N'ExampleLogin',
    @rename = N'keep_name',
    @disablelogin = N'disable_login' ;

    Also as it saying that it is an message..

    "Msg 12822, Level 16, State 1, Procedure sp_migrate_user_to_contained, Line 1
    sp_migrate_user_to_contained cannot be used to copy a password to an old hash algorithm".
    is that anything Problem with the password the way we used it or the way SQL uses &  and unable to convert it on 2012(might also depends on the windows password policies).

    Hi AnonymousUserPerson,

    I have not done before but thinking like this-
    after restore you have changed the compatibility from 2005to 2012

    I hope you have done these below things-

    Contained database feature is not enabled by default hence you need to do like below-.

    First we need to enable “contained database authentication” on the SQL Server instance if not already enabled:

    USE [master]
    GO

    sp_configure 'contained database authentication', 1
    GO
    RECONFIGURE
    GO

    Then, we can change the containment option for the database to "PARTIAL":

    ALTER DATABASE [yourdb]
    SET CONTAINMENT=PARTIAL
    GO

    (or )
    if it is newly creating then you have to do-
    USE [master]
    GO
    CREATE DATABASE [yourdb] CONTAINMENT = PARTIAL


        
    And final step is-

    USE [yourdb]
    GO
    sp_migrate_user_to_contained
    @username = N'yourdblogin',
    @rename = N'keep_name',
    @disablelogin = N'disable_login'
    GO

    Did you executed this script on where you set the Contained your database right... just to ensure..

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.


    • Edited by Rama Udaya Monday, October 22, 2012 2:50 PM
    • Marked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    • Unmarked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    • Proposed as answer by Ramesh Babu Vavilla Wednesday, October 31, 2012 1:26 PM
    Monday, October 22, 2012 2:46 PM
  • Yes, I enabled contained databases and am running the conversion script in my user database. I will try migrating the users in a different fashion and see if I am then able to convert them. I don't have the passwords to these users so my other thought was restoring the master from 2005 to 2012, but that might not be a good idea as I don't know if anything has changed between the two but I imagine it has.
    Monday, October 22, 2012 4:07 PM
  • I don't have the passwords to these users so my other thought was restoring the master from 2005 to 2012,

    It doesn't requires,please follow what Maggie referred KB .it does both login and password .!


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Marked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    • Unmarked as answer by MichellePrat Monday, October 22, 2012 7:46 PM
    Monday, October 22, 2012 5:06 PM
  • I used the method in the referenced KB article and am still getting the same error.
    Monday, October 22, 2012 7:45 PM
  • Migrated logins from SQL 2005 to SQL 2012 using method outlined here: http://support.microsoft.com/kb/918992

    When trying to convert these logins to Contained Database logins using this SQL

    sp_migrate_user_to_contained 
    @username = N'ExampleLogin',
    @rename = N'keep_name',
    @disablelogin = N'disable_login' ;

    I am getting the following error:

    Msg 12822, Level 16, State 1, Procedure sp_migrate_user_to_contained, Line 1
    sp_migrate_user_to_contained cannot be used to copy a password to an old hash algorithm.

    Note: These steps were also performed:
    USE [master]
    GO


    sp_configure 'contained database authentication', 1
    GO
    RECONFIGURE
    GO

    Then, we change the containment option for the database to "PARTIAL":

    ALTER DATABASE [NormalDB]
    SET CONTAINMENT=PARTIAL
    GO

    Also wondering if there is a way to avoid checking the password policy on new contained users similar to 

    CHECK_POLICY = OFF

    with CREATE LOGIN

    • Edited by MichellePrat Monday, October 22, 2012 11:00 PM
    • Moved by Shulei Chen Wednesday, October 24, 2012 2:10 AM (From:SQL Server Migration)
    • Merged by Maggie Luo Tuesday, October 30, 2012 9:17 AM
    Monday, October 22, 2012 8:11 PM
  • is that the same error message you are getting?

    Also the KB script it is different than when you compare to the contained database....incase of security.


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.


    • Edited by Rama Udaya Tuesday, October 23, 2012 1:35 AM
    Monday, October 22, 2012 11:59 PM
  • Also wondering if there is a way to avoid checking the password policy on new contained users similar to 

    CHECK_POLICY = OFF

    with CREATE LOGIN


    Hi Michelle,

    The answer to this is no, but you have had the workaround for it. Please check this thread which also posted by you: http://social.technet.microsoft.com/Forums/en-US/sqlsecurity/thread/424367d3-e77b-4a2c-9df7-c9a1782c5a67. And this thread is similar with yours: http://connect.microsoft.com/SQLServer/feedback/details/717069/contained-user-syntax-does-not-support-bypassing-password-policy.

    For the workaround, I have tested it and worked fine. Could you please create a new login on SQL Server 2012, and then do the procedure to check if it could work?


    Best Regards,
    Ray Chen


    • Edited by Shulei Chen Wednesday, October 24, 2012 2:18 AM
    Wednesday, October 24, 2012 2:18 AM
  • Hi Michall,

    Any progress?

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.


    Maggie Luo

    TechNet Community Support

    Wednesday, October 24, 2012 7:20 AM
  • Hi Michelle,

    I am afraid that login's old password hash must be changed. CREATE LOGIN's HASHED argument is telling SQL Server the login's password argument has already been hashed. Msg 12822 is designed to be raised when a login is using an older-than-SHA2 hash. To resolve the 12822, login to SQL Server (which updates the hash algorithm), or change the password.

    Thanks,

    Cathy Miller

    • Marked as answer by Maggie Luo Monday, November 5, 2012 9:43 AM
    Tuesday, October 30, 2012 7:13 PM
  • Hi Michall,


    If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.  

    Thanks


    Maggie Luo

    TechNet Community Support

    Monday, November 5, 2012 9:43 AM