Google Cloud outlines SQL Server login migration fix

Google Cloud has outlined a way to move SQL Server logins and passwords to Cloud SQL for SQL Server, addressing a gap that can leave migrated databases inaccessible to applications.

The problem arises when database replication finishes but the server-level login an application uses does not exist on the destination instance. In that case, SQL Server returns a login failure even though the database has been copied and synchronised.

Database Migration Service does not move instance-level objects such as the master database, server logins, or server permissions. Google said this reflects the security boundaries between a source SQL Server environment and a managed cloud database service.

It added that automatically transferring encrypted password hashes and server-level credentials can create compliance and audit concerns. Cloud migration can also prompt organisations to remove old accounts or move away from traditional SQL authentication.

Login mapping

The guidance focuses on a long-standing SQL Server distinction between logins and users. Logins are stored at server level in the master database and control authentication to the SQL Server instance. Users are stored within individual databases and determine what actions a connection can perform there.

The link between the two is a security identifier, or SID. When a database is backed up and restored, or replicated through Database Migration Service, the database user and its SID move with the database files, but the corresponding server login does not.

If the login is missing on the destination, or has been recreated with a different SID, the user becomes orphaned. The account then retains permissions inside the database but no longer has a valid authentication path at server level.

Microsoft script

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

The process starts on the source SQL Server instance, where administrators create two helper procedures in the master database: sp_hexadecimal and sp_help_revlogin. They then run EXEC master.dbo.sp_help_revlogin; to generate the output.

That output consists of T-SQL statements that recreate each login on the target system with its existing password hash and matching SID. Once copied to the Cloud SQL for SQL Server destination and executed, the logins are created on the cloud instance with the same underlying identifiers used on the source system.

Preserving the original SID means the database-level user and server-level login line up automatically after migration, avoiding the orphaned-user problem that can otherwise appear after cutover.

Fixing errors

Google also described a remedy for cases where a login was created manually on the target before the migration script was applied. In those cases, the manually created login may have a different SID from the user inside the migrated database.

The fix is to remap the database user to the intended server login with an ALTER USER statement. The command ALTER USER [app_user] WITH LOGIN = [app_user]; reconnects the database user to the server login, as long as the correct login now exists on the target instance.

The guidance presents the script-based method as the simplest option for organisations carrying out a direct migration with minimal changes to existing authentication arrangements. It also describes the process as a way to preserve continuity for applications that rely on SQL logins and fixed credentials.

Security context

Google also explained why the service does not automatically replicate logins. Source systems and managed cloud destinations operate under different security models, and copying server-level security objects directly could introduce privilege issues.

It gave the example of an on-premises login with sysadmin rights, noting that such access should not be assumed on a managed cloud database. In a hosted service, the provider controls patching, backups, and parts of the underlying environment, limiting the administrative access available to customers.

Google added that migration offers a chance to review identity management and retire unused or outdated SQL logins. For organisations looking to change their authentication model, Cloud SQL for SQL Server also supports integration with Customer-Managed Active Directory as an alternative to legacy SQL-based authentication.

The central message is that data replication alone does not complete a SQL Server migration if authentication remains on the source system. Administrators must recreate or remap server logins with matching SIDs if they want applications to connect to a newly migrated Cloud SQL for SQL Server instance without interruption.