This is a brief post showing how to migrate the sa password to another SQL Server instance without knowing it.
Backstory
I recently had to tear down an instance and rebuild it in order to downgrade the edition.
My usual process for this is:
- backup all the databases
- export SQL Server configuration with Export-DbaSpConfigure
- export SQL Server linked servers with Export-DbaLinkedServer
- export logins with Export-DbaLogin
- and, depending on the situation, other dbatools commands to export other server level objects.
The issue
Export-DbaLogin does a great job of exporting all logins, but it doesn’t offer a way to migrate the sa password to another instance.
In this case, I didn’t have the sa password and was required to ensure that the rebuilt instance is a 1:1 copy of the original one (edition excluded).
This also meant that the existing password used for sa had to be transferred to the rebuilt instance.
The solution
The main part of the solution comes from my post about cracking SQL Server login hashes offline.
Specifically, this query:
| 1 2 3 4 | SELECT @@SERVERNAME + N'_' + [name] + N':' + CONVERT(NVARCHAR(256), password_hash, 1) FROM sys.sql_logins WHERE [name] NOT LIKE N'##%'; |
Migrating the sa password as a hash
To make things easier for similar future situations, I’ve adapted the above query into the following:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE @SQL NVARCHAR(500), @LineFeed NVARCHAR(5) = CHAR(13) + CHAR(10); SELECT @SQL = N'ALTER LOGIN ' + QUOTENAME([name]) + N' WITH CHECK_POLICY= OFF,' + @LineFeed + N' PASSWORD = ' + CONVERT(NVARCHAR(256), [password_hash], 1) + N' HASHED;' + @LineFeed + N'GO' + @LineFeed + CASE WHEN [is_policy_checked] = 1 THEN N'ALTER LOGIN ' + QUOTENAME([name]) + N' WITH CHECK_POLICY= ON;' + @LineFeed + N'GO' ELSE N'' END FROM sys.[sql_logins] WHERE [name] = N'sa'; PRINT @SQL; |
This T-SQL generates the ALTER LOGIN command that sets sa’s password to the current hash.
The gotcha here is that, in order to set a SQL Server’s login password using the hash, you need to set CHECK_POLICY to OFF.
This makes sense since there’s no way for SQL Server to know if the password matches the policy without actually having a clear text password to asses.
To account for this, the query also checks what the original status of CHECK_POLICY is, and if it’s ON, it includes the command to set it back to ON afterwards.
And this is how the output looks like:
The resulting output can be copy pasted and executed on an instance where you’d want to copy or transfer sa’s password.
Note that I’ve redacted the password hash in the example above so that it fits nicely into the screenshot.
Conclusion
Making up for other tools’ lack of an option to transfer sa’s password to another install of SQL Server with some hacky T-SQL.