March 13, 2019 at 9:21 am
Not sure whether to put it in this subject or 2008. I am updating from SQL Server 2008 R2 to SQL Server 2017. I need to migrate the logins. I found the script to migrate logins, sp_help_revlogin here: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server . On the bottom of the page under remarks, it states that the passwords are hashed differently in 2017. So would I need to change the script in anyway to account for the change in the hash since it is different in 2017?
March 13, 2019 at 11:38 am
pmb88 - Wednesday, March 13, 2019 9:21 AMNot sure whether to put it in this subject or 2008. I am updating from SQL Server 2008 R2 to SQL Server 2017. I need to migrate the logins. I found the script to migrate logins, sp_help_revlogin here: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server . On the bottom of the page under remarks, it states that the passwords are hashed differently in 2017. So would I need to change the script in anyway to account for the change in the hash since it is different in 2017?
You may want to look into DBATools. They are a set of powershell scripts.
https://docs.dbatools.io/#Copy-DbaLogin
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 13, 2019 at 7:44 pm
pmb88 - Wednesday, March 13, 2019 9:21 AMOn the bottom of the page under remarks, it states that the passwords are hashed differently in 2017. So would I need to change the script in anyway to account for the change in the hash since it is different in 2017?
No, this only applies to logins created in that version with a supplied password. The actual password hash contains several components denoting which algorithm is used and the later versions can still handle the SHA1 hashed passwords. The key thing to note is that any login created on SQL 2012 or later versions cannot be scripted and recreated on a SQL 2008 R2 or earlier version using sp_help_revlogin because the hashing algorithm is not supported so the hashed password is not recognised correctly by the earlier version.
Basically, if you're moving up versions, you're fine, down versions you may have problems depending on the version.
The sp_help_revlogin procedure is quite an older method for doing this now. Tools like the PowerShell module dbatools provide a much better solution for handling things like this. I would definitely suggest looking at that for any migration work you're performing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply