So, you’ve planned your database modernization journey. You’ve set up Google Cloud’s Database Migration Service (DMS), configured replication, and successfully synchronized your application databases from your on-premises or cloud systems to a fully managed Cloud SQL for SQL Server instance.
The replication is complete, the data is up to date, and you’re ready for cutover. But when your application attempts to connect to the newly migrated database, you’re hit with a frustrating roadblock:
Msg 18456, Level 14, State 1, Line 1: Login failed for user ‘app_user.
The culprit is simple: your SQL Server logins didn’t migrate with your database. In this post, we’ll look at why this gap exists, why it actually protects your organization’s security posture, and how easy it is to bridge using standard, time-tested SQL Server tools.
Why DMS doesn’t migrate logins: Security and compliance
Database Migration Service (DMS) is highly efficient at replicating database-level schemas and transactional data. However, it purposefully doesn’t migrate instance-level objects, such as the system master database or server logins and permissions.
While this might feel like a missing feature, it is actually a deliberate design choice built around three core pillars:
-
Security Isolation and Privilege Boundaries: The source environment and the destination Cloud SQL environment operate under different security paradigms. Replicating the master system database directly could lead to unauthorized privilege escalation. For example, an on-premises login with sysadmin privileges shouldn’t have unrestricted sysadmin access to a fully managed Google Cloud database. When the cloud provider manages physical backups, patching, and security, it needs to limit underlying operating system access to ensure correct operation.
-
Compliance and Audit Governance: Automated migration of encrypted password hashes and server-level security credentials without explicit administrator oversight frequently violates enterprise compliance frameworks such as PCI-DSS or SOC 2. By keeping security object migration as a deliberate, administrator-driven step, organizations can guarantee that only approved identities are provisioned in the cloud landing zone.
-
The Need for Identity Modernization: Migrating to the cloud is the perfect opportunity to update and prune stale credentials. Frequently, on-premises instances carry legacy SQL logins that are no longer used. Replicating them blindly to a cloud-managed service is a security anti-pattern. Furthermore, moving to Cloud SQL is often the catalyst for shifting away from legacy SQL authentication toward modern, cloud-native identity solutions like Customer-Managed Active Directory (CMAD).
Understanding logins vs. users: The SID connection
To migrate logins successfully, let’s briefly revisit how SQL Server manages security. SQL Server separates identity into two distinct layers:
-
Logins (server-level): Stored in the master database. These authenticate a client connection to the SQL Server instance.
-
Users (database-level): Stored inside individual user databases. These authorize what actions a connection can perform within that specific database.
The bridge between a server login and a database user is a unique Security Identifier (SID).
When you backup and restore a database (or use DMS to replicate it), the database-level users (and their corresponding SIDs) are migrated inside the database files. However, if the corresponding server-level login does not exist in the destination master database—or exists but has a different SID—the mapping breaks. This results in “orphaned users” who have database access permissions but no way to authenticate at the server level.
