Bridging the SQL Server Login Gap in Cloud SQL Migrations
Google Cloud explains how to resolve "Login failed for user" errors when migrating SQL Server databases to Cloud SQL using Database Migration Service (DMS). The article clarifies that DMS intentionally doesn't migrate instance-level logins due to security and compliance, preventing privilege escalation and aiding identity modernization. It details a method using Microsoft's `sp_help_revlogin` script to accurately transfer server-level logins, including SIDs and password hashes, thus avoiding orphaned users. This ensures seamless application connectivity post-migration and suggests adopting Customer-Managed Active Directory for enhanced cloud authentication.
- →Understanding why DMS doesn't migrate SQL Server logins
- →Clarifying SQL Server logins, users, and orphaned users
- →Migrating SQL Server logins using sp_help_revlogin
- →Resolving orphaned SQL Server users in Cloud SQL
- →Modernizing authentication with Customer-Managed Active Directory
Notes (5) ›
- Understanding why DMS doesn't migrate SQL Server logins
Google Cloud's Database Migration Service (DMS) intentionally omits instance-level objects like SQL Server logins during database replication. This design choice upholds security isolation, privilege boundaries, and compliance frameworks by preventing unauthorized access and enabling identity modernization.
- Clarifying SQL Server logins, users, and orphaned users
SQL Server distinguishes between server-level logins for authentication and database-level users for authorization, linked by a Security Identifier (SID). When a database is migrated, users are transferred, but mismatched or missing server logins lead to "orphaned users" unable to authenticate.
- Migrating SQL Server logins using sp_help_revlogin
The recommended solution involves using Microsoft's `sp_help_revlogin` script to generate T-SQL statements that recreate server logins on the destination Cloud SQL instance. This process preserves original encrypted password hashes and SIDs for seamless user mapping.
- Resolving orphaned SQL Server users in Cloud SQL
If SIDs are mismatched or logins were manually created before running `sp_help_revlogin`, users can become orphaned. The `ALTER USER [user_name] WITH LOGIN = [login_name];` command can be used to remap the database user to its corresponding server login.
- Modernizing authentication with Customer-Managed Active Directory
Beyond direct login migration, the move to Cloud SQL is an opportunity to modernize authentication. Cloud SQL for SQL Server supports integration with Customer-Managed Active Directory (CMAD) for centralized, enterprise-grade Kerberos authentication.
https://cloud.google.com/blog/products/databases/how-to-replicate-sql-server-logins-and-passwords-to-cloud-sql/
Related releases
- Cloud SQL for PostgreSQL Admin API adds regional endpoints for GA Google Cloud release notes ·
- Cloud SQL for SQL Server Admin API Regional Endpoints Go GA Google Cloud release notes ·
- Cloud SQL for MySQL Regional Endpoints General Availability Google Cloud release notes ·
- Google Cloud Introduces Data Agent Kit for Agentic Data Analysis in IDEs Google Cloud Blog ·
- Cloud SQL for PostgreSQL Gains C4 Series and In-Place Instance Upgrades Google Cloud release notes ·
- Cloud SQL for MySQL Gains C4 Machine Series and In-Place Instance Upgrades Google Cloud release notes ·