Solved: Moving Users to a SQL Contained database stops with an error
I decided to convert my ERP Database to a Contained Database so that I could have the logins move between servers with AlwaysOn on Microsoft SQL Server 2012.
https://msdn.microsoft.com/en-us/library/ff929071.aspx
SQL wouldn't let me convert the database while in use, so I had to make the change at night.
This is the script I used for migrating all the user accounts from the server to the DB:
----------------------------------------------------------------
DECLARE @username sysname ;
DECLARE user_cursor CURSOR
FOR
SELECT dp.name
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_migrate_user_to_contained
@username = @username,
@rename = N'keep_name',
@disablelogin = N'disable_login';
FETCH NEXT FROM user_cursor INTO @username
END
CLOSE user_cursor ;
DEALLOCATE user_cursor ;
----------------------------------------------------------------
But when I ran the script for moving the users from the server level up to the database level, I got an error. Great.. it's 11pm on a Saturday and now I get to figure this one out..
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.
----------------------------------------------------------------
The problem is that some of the users had not been using their accounts or changing their passwords and there was something wrong with the hash. But now what? Read on..
I started by creating a list of users by just using the SELECT part of the section above.
----------------------------------------------------------------
SELECT dp.name
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
----------------------------------------------------------------
Then I used Excel to build a SQL script that would have GO between each user so that if one UserID had a bad Hash, that it would keep going.
----------------------------------------------------------------
Important Update and warning!
Containment cause problems with out ERP (Yes I tested in a lab first) and it was ugly to go back.
I covered that in my other blog post from 11/29/15.
I found an alternative solution to copy logins from server to server using a Microsoft procedure that export the Hash and SID, it's called sp_help_revlogin (Look into this! It works great and is a safer alternative).
https://msdn.microsoft.com/en-us/library/ff929071.aspx
SQL wouldn't let me convert the database while in use, so I had to make the change at night.
This is the script I used for migrating all the user accounts from the server to the DB:
----------------------------------------------------------------
DECLARE @username sysname ;
DECLARE user_cursor CURSOR
FOR
SELECT dp.name
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_migrate_user_to_contained
@username = @username,
@rename = N'keep_name',
@disablelogin = N'disable_login';
FETCH NEXT FROM user_cursor INTO @username
END
CLOSE user_cursor ;
DEALLOCATE user_cursor ;
----------------------------------------------------------------
But when I ran the script for moving the users from the server level up to the database level, I got an error. Great.. it's 11pm on a Saturday and now I get to figure this one out..
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.
----------------------------------------------------------------
The problem is that some of the users had not been using their accounts or changing their passwords and there was something wrong with the hash. But now what? Read on..
I started by creating a list of users by just using the SELECT part of the section above.
----------------------------------------------------------------
SELECT dp.name
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
----------------------------------------------------------------
Then I used Excel to build a SQL script that would have GO between each user so that if one UserID had a bad Hash, that it would keep going.
----------------------------------------------------------------
EXECUTE sp_migrate_user_to_contained @username =N'USER1', @rename = N'keep_name', @disablelogin = N'disable_login';
GO
EXECUTE sp_migrate_user_to_contained @username =N'USER2', @rename = N'keep_name', @disablelogin = N'disable_login';
GO
EXECUTE sp_migrate_user_to_contained @username =N'USER3', @rename = N'keep_name', @disablelogin = N'disable_login';
GO
GO
EXECUTE sp_migrate_user_to_contained @username =N'USER2', @rename = N'keep_name', @disablelogin = N'disable_login';
GO
EXECUTE sp_migrate_user_to_contained @username =N'USER3', @rename = N'keep_name', @disablelogin = N'disable_login';
GO
----------------------------------------------------------------
Then after that I was able to run the SELECT again and get a list of the bad accounts. Out of 750 accounts, I had 43 bad ones left.
At this point if you change the password for those bad accounts, it cleans up the Hash and you will be able to convert them.
On a side note, while I was at it I added my SSRS (Reporting Services) database to AlwaysOn and setup a Scale-Out Deployment too. AlwaysOn is not very useful if I lose SSRS when my main server goes down. https://msdn.microsoft.com/en-us/library/ms159114.aspx
Important Update and warning!
Containment cause problems with out ERP (Yes I tested in a lab first) and it was ugly to go back.
I covered that in my other blog post from 11/29/15.
I found an alternative solution to copy logins from server to server using a Microsoft procedure that export the Hash and SID, it's called sp_help_revlogin (Look into this! It works great and is a safer alternative).
Comments