Tutorial: Migrar usuários e grupos do Windows em uma instância do SQL Server para a Instância Gerenciada de SQL do Azure usando a sintaxe T-SQL DDL

Aplica-se a: Instância Gerenciada de SQL do Azure

Este artigo orienta você ao longo do processo de migração de usuários e grupos locais do Windows no SQL Server para uma Instância Gerenciada de SQL do Azure usando a sintaxe T-SQL.

Neste tutorial, você aprenderá como:

• Criar logons para o SQL Server
• Criar um banco de dados de teste para migração
• Criar logons, usuários e funções
• Fazer backup e restaurar o banco de dados para a MI (instância gerenciada) de SQL
• Migrar os usuários manualmente para a MI usando a sintaxe ALTER USER
• Testar a autenticação com os novos usuários mapeados

Observação

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

Pré-requisitos

Para concluir este tutorial, os seguintes pré-requisitos se aplicam:

• O domínio do Windows é federado com o Microsoft Entra ID.
• Acesso ao Active Directory para criar usuários/grupos.
• Um SQL Server existente no ambiente local.
• Uma instância gerenciada existente de SQL. Confira Início Rápido: Criar uma Instância Gerenciada de SQL.
  • É preciso usar um sysadmin na Instância Gerenciada de SQL para criar logons do Microsoft Entra.
• Criar um administrador do Microsoft Entra para a Instância Gerenciada de SQL.
• Você pode se conectar à Instância Gerenciada de SQL dentro da rede. Consulte os seguintes artigos para obter informações adicionais:
  • Conectar seu aplicativo à Instância Gerenciada de SQL do Azure
  • Início Rápido: Configurar uma conexão ponto a site a uma Instância Gerenciada de SQL do Azure do local
  • Configurar um ponto de extremidade público na Instância Gerenciada de SQL do Azure

Sintaxe T-SQL DDL

Veja abaixo a sintaxe T-SQL DDL usada para dar suporte à migração de grupos e usuários do Windows de uma Instância do SQL Server para uma Instância Gerenciada de SQL com autenticação do 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 o nome de domínio do usuário.

userName
Especifica o nome do usuário identificado dentro do banco de dados.

= loginName@domainName.com
Remapeia um usuário para o logon do Microsoft Entra

groupName
Especifica o nome do grupo identificado dentro do banco de dados.

Parte 1: Criar logons no SQL Server para usuários e grupos do Windows

Importante

A sintaxe a seguir cria um logon de usuário e grupo em seu SQL Server. Você precisará certificar-se de que o usuário e o grupo existam dentro de seu AD (Active Directory) antes de executar a sintaxe abaixo.

Usuários: testUser1, testGroupUser
Grupo: migração - testGroupUser precisa pertencer ao grupo de migração no AD

O exemplo a seguir cria um logon no SQL Server para uma conta chamada testUser1 no domínio 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;

Criar um banco de dados para este teste.

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

Parte 2: Criar usuários e grupos do Windows e adicionar funções e permissões

Use a sintaxe a seguir para criar o usuário de teste.

use migration;  
go

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

Verifique as permissões do usuário:

-- 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

Crie uma função e atribua o usuário de teste a ela:

-- 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 a consulta a seguir para exibir nomes de usuário atribuídos a uma função específica:

-- 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 a sintaxe a seguir para criar um grupo. Em seguida, adicione o grupo à função 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)

Crie uma tabela de teste e adicione alguns dados usando a seguinte sintaxe:

-- 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: Fazer backup e restaurar o banco de dados do usuário individual em uma Instância Gerenciada de SQL

Crie um backup do banco de dados de migração usando o artigo Copiar bancos de dados com backup e restauração ou use a seguinte sintaxe:

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

Siga nosso Início Rápido: Restaurar um banco de dados em uma Instância Gerenciada de SQL.

Parte 4: Migrar usuários para a Instância Gerenciada de SQL

Execute o comando ALTER USER para concluir o processo de migração na Instância Gerenciada de SQL.

1. Entre em sua Instância Gerenciada de SQL usando a conta de administrador do Microsoft Entra para a Instância Gerenciada de SQL. Em seguida, crie seu logon do Microsoft Entra na Instância Gerenciada de SQL usando a sintaxe a seguir. Para obter mais informações, confira Tutorial: Segurança da Instância Gerenciada de SQL no Banco de Dados SQL do Azure usando as entidades do servidor do Microsoft Entra (logons).

   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
   

2. Verifique a migração quanto ao banco de dados, a tabela e as entidades de segurança corretas.

   -- 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
   

3. Use a sintaxe ALTER USER para mapear o usuário local para o logon do 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;
   

4. Use a sintaxe ALTER USER para mapear o grupo local para o logon do 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: Testar a autenticação do usuário ou grupo do Microsoft Entra

Teste a autenticação da Instância Gerenciada de SQL usando o usuário mapeado anteriormente para o logon do Microsoft Entra usando a sintaxe ALTER USER.

1. Faça logon na VM federada usando sua assinatura da Instância Gerenciada de SQL do Azure como aadsqlmi\testUser1

2. Usando o SSMS (SQL Server Management Studio), entre na sua Instância Gerenciada de SQL usando a autenticação Integrada do Active Directory, conectando-se ao banco de dados migration.

   1. Você também pode entrar usando as credenciais testUser1@aadsqlmi.net com a opção do SSMS Active Directory – Universal com suporte para MFA. No entanto, nesse caso, você não pode usar o mecanismo de logon único e deve digitar uma senha. Você não precisará usar uma VM federada para fazer logon na Instância Gerenciada de SQL.

3. Como parte do membro da função SELECT, você pode selecionar na tabela test

   Select * from test  --  and see one row (1,10)
   

Teste a autenticação em uma Instância Gerenciada de SQL usando um membro de um grupo do Windows migration. O usuário aadsqlmi\testGroupUser deve ter sido adicionado ao grupo migration antes da migração.

1. Faça logon na VM federada usando sua assinatura da Instância Gerenciada de SQL do Azure como aadsqlmi\testGroupUser

2. Usando o SSMS com a autenticação Integrada do Active Directory, conecte-se ao servidor de Instância Gerenciada de SQL do Azure e ao banco de dados migration

   1. Você também pode entrar usando as credenciais testGroupUser@aadsqlmi.net com a opção do SSMS Active Directory – Universal com suporte para MFA. No entanto, nesse caso, você não pode usar o mecanismo de logon único e deve digitar uma senha. Você não precisará usar uma VM federada para fazer logon na Instância Gerenciada de SQL.

3. Como parte da função de db_owner, você pode criar uma nova tabela.

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

Observação

Devido a um problema de design conhecido do Banco de Dados SQL do Azure, criar uma instrução de tabela executada como um membro de um grupo vai falhar com o seguinte erro:

Msg 2760, Level 16, State 1, Line 4 The specified schema name "testGroupUser@aadsqlmi.net" either does not exist or you do not have permission to use it.

A solução alternativa atual é criar uma tabela com um esquema existente no caso acima <dbo.new>

Próximas etapas

Tutorial: Migrar o SQL Server para a Instância Gerenciada de SQL do Azure offline usando DMS