Beyond DMS: Accelerating Migra... Note

Beyond DMS: Accelerating Migrations SQL Server Logins and Users to Cloud SQL

Database Migration Service efficiently replicates data but excludes server-level objects like logins for security and compliance reasons. This deliberate omission prevents unauthorized privilege escalation by ensuring that on-premises privileges do not automatically transfer to the cloud. Migrating encrypted password hashes without oversight also violates compliance frameworks like PCI-DSS and SOC 2. The process also allows for identity modernization by removing stale credentials and shifting to cloud-native authentication. SQL Server separates logins, which authenticate connections at the server level, from users, which authorize actions within databases. A Security Identifier (SID) links server logins to database users. When databases are migrated without their corresponding logins, or with mismatched SIDs, users can become orphaned. The sp_help_revlogin script from Microsoft generates T-SQL statements to recreate logins with their original password hashes and SIDs. This script is run on the source instance to generate the migration query. This generated query is then executed on the destination Cloud SQL instance to create the necessary logins. If users become orphaned, they can be remapped to the correct server login using the ALTER USER command. For enhanced security, consider migrating to Customer-Managed Active Directory for centralized, Kerberos-based authentication.
CdXz5zHNQW_jlKnVZN3XM.png