Google Cloud Prescribes Fix for SQL Server Migration Login Woes

Google Cloud has outlined a solution to a common issue affecting SQL Server migrations to Cloud SQL for SQL Server. When database replication is complete, but server-level logins are missing from the destination instance, applications may experience login failures.

The problem arises because Database Migration Service does not transfer instance-level objects, such as server logins and permissions, during migration. This reflects the security boundaries between a source SQL Server environment and a managed cloud database service.

To address this issue, Google recommends using the sp_help_revlogin Microsoft script to transfer SQL Server authentication logins between instances. The script generates CREATE LOGIN statements containing the original encrypted password hash and SID for each login.

Administrators can run the script on the source SQL Server instance, generate the output, and then copy it to the Cloud SQL for SQL Server destination and execute it. This will recreate each login on the target system with its existing password hash and matching SID.