Beyond Database Migration: How to Replicate SQL Server Logins and Users to Cloud SQL

Understanding SQL Server Login Migration to Cloud SQL

Moving a SQL Server database to the cloud involves more than copying schemas and transactional data.

Organizations using Google Cloud's Database Migration Service (DMS) can replicate their application databases to a fully managed Cloud SQL for SQL Server instance. But when the migration reaches the cutover stage, applications can encounter authentication errors even though their database data is already synchronized.

One example is:

Msg 18456, Level 14, State 1, Line 1: Login failed for user 'app_user.'

The underlying issue can be that the SQL Server login did not migrate alongside the database.

Google Cloud's latest guidance explains why this happens and presents a three-step approach using the Microsoft-maintained sp_help_revlogin procedure to replicate SQL Server logins while retaining their original password hashes and Security Identifiers, or SIDs.
add heading instead of introduction

Why SQL Server Logins Are Not Migrated by DMS

Google Cloud Database Migration Service is designed to replicate database-level schemas and transactional data. However, it does not migrate instance-level objects such as the system master database, server logins and permissions.

This separation is intentional.

Security isolation and privilege boundaries

The source SQL Server environment and the managed Cloud SQL environment operate under different security models.

Directly replicating the master database could potentially transfer server-level privileges into an environment where those privileges should not automatically exist.

For example, an account with sysadmin privileges on an on-premises SQL Server should not automatically receive unrestricted sysadmin access to a managed cloud database.

Because Cloud SQL is a managed service, Google Cloud also controls areas such as physical backups, patching and security operations. Separating server-level security objects helps maintain those boundaries.

Compliance and administrative control

SQL Server logins can contain encrypted password hashes and server-level security information.

Automatically moving those credentials without administrator oversight can create governance concerns. Keeping login migration as an administrator-controlled operation allows organizations to determine which identities should exist in the destination environment.

An opportunity to modernize identity

A migration is also an opportunity to review existing credentials.

Older SQL Server environments can contain unused or outdated SQL logins. Replicating every existing login without review can carry unnecessary credentials into the new environment.

Google Cloud also highlights Customer-Managed Active Directory (CMAD) as an option for organizations looking to move away from legacy SQL authentication toward centralized authentication using Kerberos.

SQL Server Logins vs. Database Users

Understanding the difference between a login and a user is essential when troubleshooting migration authentication problems.

SQL Server separates identity into two layers.

Logins are server-level identities.

They are stored in the master database and are responsible for authenticating connections to the SQL Server instance.

Users are database-level identities.

They exist inside individual databases and determine what an authenticated identity can do within that database.

The connection between these two layers is the Security Identifier (SID).

Why the SID matters

When a database is backed up and restored, or replicated using DMS, the database-level users and their associated SIDs move with the database.

The corresponding server login, however, may not exist on the destination.

There is another possibility: a login with the same name may have already been created on the destination, but it could have a different SID.

In either situation, the relationship between the database user and server login can break.

The result is an orphaned user.

The user still exists inside the database and retains its database permissions, but there is no correctly mapped server-level login through which the application can authenticate.

The Recommended Approach: sp_help_revlogin

Rather than manually recreating every SQL Server login and attempting to reproduce its password information, Google Cloud recommends using Microsoft's sp_help_revlogin script.

The Microsoft-maintained procedure generates T-SQL CREATE LOGIN statements for SQL Server authentication logins on the source instance.

The generated statements include the original encrypted password hash and the login's Security Identifier.

This means the destination login can be recreated with the information required to maintain the relationship between the database user and server login.

Step 1: Create the Helper Procedures

First, connect to the source SQL Server instance using SQL Server Management Studio, or SSMS.

The official Microsoft script creates two stored procedures in the source master database:

• sp_hexadecimal

• sp_help_revlogin

These procedures are used to generate the required login migration statements.

Google Cloud specifically recommends obtaining the latest version of the Microsoft-maintained script and reviewing its documentation before using it.

Step 2: Generate the Migration Script

After creating the procedures, execute:

EXEC master.dbo.sp_help_revlogin;

Google Cloud recommends setting SSMS output to Results to Text using Ctrl + T, which makes it easier to copy the generated SQL statements.

The output will contain statements similar to:

CREATE LOGIN [app_user]
WITH PASSWORD = 0x01004F3D... HASHED,
SID = 0x8D2F...,
DEFAULT_DATABASE = [CustomerDB]

The important parts are the HASHED password option and the original SID.

Together, these allow the destination login to retain the required authentication information and security identity.

Step 3: Apply the Script to Cloud SQL

Once the statements have been generated, connect to the destination Cloud SQL for SQL Server instance.

Execute the generated T-SQL statements on the destination.

The logins are then created with their corresponding password hashes and SIDs.

When the database users and server logins have matching SIDs, the relationship between them can be maintained after the database migration, helping avoid orphaned users.

What Happens If Users Become Orphaned?

There is another scenario to consider.

An administrator may manually create a login on the Cloud SQL destination before running sp_help_revlogin.

If the manually created login has a different SID from the database user, the mapping can break.

In that case, the database user can be remapped to the newly created server login.

For example:

ALTER USER [app_user] WITH LOGIN = [app_user];

This reconnects the database user with the server login through the appropriate SID mapping and can restore application connectivity.

Beyond Lift-and-Shift: Modernizing Authentication

Replicating SQL Server logins is useful when the objective is a straightforward lift-and-shift migration.

However, organizations can also use the migration as an opportunity to reconsider how users authenticate.

Google Cloud highlights Cloud SQL for SQL Server integration with Customer-Managed Active Directory (CMAD).

With Active Directory integration, organizations can move toward centralized enterprise authentication and reduce reliance on legacy SQL logins. The source specifically identifies Kerberos authentication as part of this approach.

This creates two distinct migration paths:

Lift-and-shift

• Replicate the existing SQL logins

• Preserve password hashes

• Preserve SIDs

• Maintain existing user-to-login mappings

Authentication modernization

• Review existing SQL logins

• Remove credentials that are no longer needed

• Consider Customer-Managed Active Directory

• Move toward centralized authentication

The right approach depends on the organization's migration and identity requirements.

Why This Matters for Application Cutover

Database replication can appear complete while application authentication remains unresolved.

That makes identity migration an important part of the cutover plan.

A database can contain all the expected application data and permissions, but if its corresponding server-level login is missing or incorrectly mapped, applications can still fail to connect.

The sp_help_revlogin approach addresses this specific gap by generating login creation statements that retain the original password hash and SID information.

For migration teams, this reinforces an important distinction: moving the database is not the same as moving the complete database security context.

A Practical Migration Checklist

Before completing a SQL Server to Cloud SQL migration, teams can review the following:

• Confirm that database schemas and transactional data have been replicated.

• Identify SQL Server authentication logins required by applications.

• Review existing logins and determine which ones are still required.

• Create sp_hexadecimal and sp_help_revlogin on the source instance.

• Run EXEC master.dbo.sp_help_revlogin;.

• Copy the generated CREATE LOGIN statements.

• Apply the statements to the destination Cloud SQL for SQL Server instance.

• Verify login and database-user SID mappings.

• Remap any orphaned users when necessary.

• Consider whether Customer-Managed Active Directory is appropriate for longer-term authentication modernization.

Conclusion

Migrating SQL Server databases to Cloud SQL involves more than transferring data.

Google Cloud Database Migration Service handles database-level replication, but server-level security objects such as SQL Server logins are deliberately handled separately.

For organizations performing a lift-and-shift migration, Microsoft's sp_help_revlogin provides a practical way to generate login creation statements containing the required password hash and SID information.

The process is straightforward:

Create the helper procedures → Generate the login script → Apply it to Cloud SQL.

For teams already planning a move to Google Cloud, addressing login and user mappings before cutover can help prevent authentication failures caused by orphaned database users.

At the same time, migration provides an opportunity to review legacy credentials and consider more centralized authentication approaches such as Customer-Managed Active Directory.

Database modernization, therefore, is not only about moving data. It is also about making sure identity, access and application connectivity remain properly aligned throughout the transition.