Tutorial: Migración de los usuarios y los grupos de Windows de una instancia de SQL Server a Instancia administrada de Azure SQL mediante la sintaxis DDL de T-SQL

Artículo
10/31/2023

Se aplica a:Azure SQL Managed Instance

Este artículo le guiará por el proceso de migración de los usuarios y los grupos locales de Windows de SQL Server a Instancia administrada de Azure SQL mediante la sintaxis de T-SQL.

En este tutorial, aprenderá a:

Crear inicios de sesión para SQL Server
Crear una base de datos de prueba para la migración
Crear inicios de sesión, usuarios y roles
Realizar una copia de seguridad y restauración de la base de datos a Instancia administrada de SQL (MI)
Migrar manualmente los usuarios a la instancia administrada mediante la sintaxis ALTER USER
Probar la autenticación con los nuevos usuarios asignados

Nota:

Microsoft Entra ID era conocido anteriormente como Azure Active Directory (Azure AD).

Requisitos previos

Para completar este tutorial son necesarios los siguientes requisitos previos:

El dominio de Windows está federado con Microsoft Entra ID.
Acceso a Active Directory para crear usuarios y grupos.
Una instancia de SQL Server ya existente en el entorno local.
Una instancia administrada de SQL ya existente. Consulte Quickstart: Creación de una Instancia administrada de SQL.
- Para crear inicios de sesión de Microsoft Entra, se debe usar sysadmin en la instancia administrada de SQL.
Creación de un administrador de Microsoft Entra para SQL Managed Instance.
Puede conectarse a la instancia administrada de SQL dentro de la red. Para más información, consulte los artículos siguientes:

Sintaxis DDL de T-SQL

A continuación se muestra la sintaxis DDL de T-SQL que se usa para admitir la migración de usuarios y grupos de Windows desde una instancia de SQL Server a SQL Managed Instance con autenticación de Microsoft Entra.

-- For individual Windows users with logins
ALTER USER [domainName\userName] WITH LOGIN = [loginName@domainName.com];

--For individual groups with logins
ALTER USER [domainName\groupName] WITH LOGIN=[groupName]

Argumentos

domainName
Especifica el nombre de dominio del usuario.

userName
Especifica el nombre del usuario identificado en la base de datos.

= loginName@domainName.com
reasigna un usuario al inicio de sesión de Microsoft Entra

groupName
Especifica el nombre del grupo identificado en la base de datos.

Parte 1: Creación de inicios de sesión en SQL Server para usuarios y grupos de Windows

Importante

La siguiente sintaxis crea un inicio de sesión de un usuario y un grupo en SQL Server. Deberá asegurarse de que el usuario y el grupo existen dentro de Active Directory antes de ejecutar la siguiente sintaxis.

Usuarios: testUser1, testGroupUser
Grupo: migration - testGroupUser debe pertenecer al grupo de migración de AD

En el ejemplo siguiente se crea un inicio de sesión en SQL Server para una cuenta denominada testUser1 en el dominio aadsqlmi.

-- Sign into SQL Server as a sysadmin or a user that can create logins and databases

use master;  
go

-- Create Windows login
create login [aadsqlmi\testUser1] from windows;
go;

/** Create a Windows group login which contains one user [aadsqlmi\testGroupUser].
testGroupUser will need to be added to the migration group in Active Directory
**/
create login [aadsqlmi\migration] from windows;
go;


-- Check logins were created
select * from sys.server_principals;
go;

Cree una base de datos para esta prueba.

-- Create a database called [migration]
create database migration
go

Parte 2: Creación de usuarios y grupos de Windows y, a continuación, incorporación de roles y permisos

Use la siguiente sintaxis para crear el usuario de prueba.

use migration;  
go

-- Create Windows user [aadsqlmi\testUser1] with login
create user [aadsqlmi\testUser1] from login [aadsqlmi\testUser1];
go

Compruebe los permisos de usuario:

-- Check the user in the Metadata
select * from sys.database_principals;
go

-- Display the permissions – should only have CONNECT permissions
select user_name(grantee_principal_id), * from sys.database_permissions;
go

Cree un rol y asigne a este rol el usuario de prueba:

-- Create a role with some permissions and assign the user to the role
create role UserMigrationRole;
go

grant CONNECT, SELECT, View DATABASE STATE, VIEW DEFINITION to UserMigrationRole;
go

alter role UserMigrationRole add member [aadsqlmi\testUser1];
go

Use la siguiente consulta para mostrar los nombres de usuario asignados a un rol específico:

-- Display user name assigned to a specific role
SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Use la sintaxis siguiente para crear un grupo. Después, agregue el grupo al rol db_owner.

-- Create Windows group
create user [aadsqlmi\migration] from login [aadsqlmi\migration];
go

-- ADD 'db_owner' role to this group
sp_addrolemember 'db_owner', 'aadsqlmi\migration';
go

--Check the db_owner role for 'aadsqlmi\migration' group
select is_rolemember('db_owner', 'aadsqlmi\migration')
go
-- Output  ( 1 means YES)

Cree una tabla de prueba y agregue algunos datos con la siguiente sintaxis:

-- Create a table and add data
create table test ( a int, b int);
go

insert into test values (1,10)
go

-- Check the table values
select * from test;
go

Parte 3: Copia de seguridad y restauración de la base de datos de usuario individual en Instancia administrada de SQL

Cree una copia de seguridad de la base de datos de migración mediante las indicaciones del artículo Copy Databases with Backup and Restore (Copia de bases de datos con copia de seguridad y restauración), o use la siguiente sintaxis:

use master;
go
backup database migration to disk = 'C:\Migration\migration.bak';
go

Consulte Inicio rápido: Restauración de una base de datos en una instancia administrada de SQL.

Parte 4: Migración de usuarios a Instancia administrada de SQL

Ejecute el comando ALTER USER para completar el proceso de migración en Instancia administrada de SQL.

Inicie sesión en la instancia de SQL Managed Instance mediante la cuenta de administrador de Microsoft Entra para SQL Managed Instance. Después, cree el inicio de sesión de Microsoft Entra en la instancia administrada de SQL con la siguiente sintaxis. Para más información, vea Tutorial: Seguridad de SQL Managed Instance en Azure SQL Database mediante entidades de seguridad de servidor (inicios de sesión) de Microsoft Entra.
```
use master
go

-- Create login for AAD user [testUser1@aadsqlmi.net]
create login [testUser1@aadsqlmi.net] from external provider
go

-- Create login for the Azure AD group [migration]. This group contains one user [testGroupUser@aadsqlmi.net]
create login [migration] from external provider
go

--Check the two new logins
select * from sys.server_principals
go
```

Compruebe la migración de la base de datos, la tabla y las entidades de seguridad correctas.

-- Switch to the database migration that is already restored for MI
use migration;
go

--Check if the restored table test exist and contain a row
select * from test;
go

-- Check that the SQL on-premises Windows user/group exists  
select * from sys.database_principals;
go
-- the old user aadsqlmi\testUser1 should be there
-- the old group aadsqlmi\migration should be there

Use la sintaxis ALTER USER para asignar el usuario local al inicio de sesión de Microsoft Entra.

/** Execute the ALTER USER command to alter the Windows user [aadsqlmi\testUser1]
to map to the Azure AD user testUser1@aadsqlmi.net
**/
alter user [aadsqlmi\testUser1] with login = [testUser1@aadsqlmi.net];
go

-- Check the principal
select * from sys.database_principals;
go
-- New user testUser1@aadsqlmi.net should be there instead
--Check new user permissions  - should only have CONNECT permissions
select user_name(grantee_principal_id), * from sys.database_permissions;
go

-- Check a specific role
-- Display Db user name assigned to a specific role
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Use la sintaxis ALTER USER para asignar el grupo local al inicio de sesión de Microsoft Entra.

/** Execute ALTER USER command to alter the Windows group [aadsqlmi\migration]
to the Azure AD group login [migration]
**/
alter user [aadsqlmi\migration] with login = [migration];
-- old group migration is changed to Azure AD migration group
go

-- Check the principal
select * from sys.database_principals;
go

--Check the group permission - should only have CONNECT permissions
select user_name(grantee_principal_id), * from sys.database_permissions;
go

--Check the db_owner role for 'aadsqlmi\migration' user
select is_rolemember('db_owner', 'migration')
go
-- Output 1 means 'YES'

Parte 5: Prueba de la autenticación de usuarios o grupos de Microsoft Entra

Pruebe la autenticación en SQL Managed Instance mediante el usuario asignado previamente al inicio de sesión de Microsoft Entra con la sintaxis ALTER USER.

Inicie sesión en la máquina virtual federada mediante su suscripción de Instancia administrada de Azure SQL como aadsqlmi\testUser1
Con SQL Server Management Studio (SSMS), inicie sesión en su instancia administrada de SQL con la autenticación integrada de Active Directory y conéctese a la base de datos migration.
1. También puede iniciar sesión mediante las credenciales de testUser1@aadsqlmi.net con la opción de SSMS Active Directory – Universal compatible con MFA. Sin embargo, en este caso, no puede usar el mecanismo de inicio de sesión único y debe escribir una contraseña. No tendrá que usar una máquina virtual federada para iniciar sesión en la instancia administrada de SQL.
Como parte del miembro del rol SELECT, puede seleccionar en la tabla test
```
Select * from test  --  and see one row (1,10)
```

Pruebe la autenticación en una instancia administrada de SQL con un miembro de un grupo migration de Windows. El usuario aadsqlmi\testGroupUser debe haberse agregado al grupo migration antes de la migración.

Inicie sesión en la máquina virtual federada mediante su suscripción de Instancia administrada de Azure SQL como aadsqlmi\testGroupUser
El uso de SSMS con la autenticación integrada de Active Directory permite conectarse al servidor de Instancia administrada de Azure SQL y a la base de datos migration
1. También puede iniciar sesión mediante las credenciales de testGroupUser@aadsqlmi.net con la opción de SSMS Active Directory – Universal compatible con MFA. Sin embargo, en este caso, no puede usar el mecanismo de inicio de sesión único y debe escribir una contraseña. No tendrá que usar una máquina virtual federada para iniciar sesión en su instancia administrada de SQL.

Como parte del rol db_owner, puede crear una nueva tabla.

-- Create table named 'new' with a default schema
Create table dbo.new ( a int, b int)