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

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

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

Neste tutorial, irá aprender a:

• Criar logons para o SQL Server
• Criar um banco de dados de teste para migração
• Criar logins, usuários e funções
• Faça backup e restaure seu banco de dados para a Instância Gerenciada SQL (MI)
• Migrar manualmente usuários para MI usando a sintaxe ALTER USER
• Testando a autenticação com os novos usuários mapeados

Nota

Microsoft Entra ID é o novo nome para o Azure Ative Directory (Azure AD). Estamos atualizando a documentação neste momento.

Pré-requisitos

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

• O domínio do Windows é federado com o Microsoft Entra ID.
• Acesso ao Ative Directory para criar usuários/grupos.
• Um SQL Server existente em seu ambiente local.
• Uma instância gerenciada SQL existente. Consulte Guia de início rápido: criar uma instância gerenciada pelo SQL.
  • A sysadmin na Instância Gerenciada SQL deve ser usada para criar logons do Microsoft Entra.
• Crie um administrador do Microsoft Entra para Instância Gerenciada SQL.
• Você pode se conectar à sua Instância Gerenciada SQL em sua rede. Consulte os seguintes artigos para obter informações adicionais:
  • Ligar a aplicação ao Azure SQL Managed Instance
  • Guia de início rápido: configurar uma conexão ponto a site para uma Instância Gerenciada SQL do Azure a partir do local
  • Configurar ponto de extremidade público na Instância Gerenciada SQL do Azure

Sintaxe DDL T-SQL

Abaixo está a sintaxe DDL T-SQL usada para dar suporte à migração de usuários e grupos do Windows de uma instância do SQL Server para a Instância Gerenciada do 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 login 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 no SQL Server. Você precisará verificar se o usuário e o grupo existem dentro do Ative Directory (AD) antes de executar a sintaxe abaixo.

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

O exemplo abaixo 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;

Crie 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, em seguida, 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 seu usuário de teste a essa função:

-- 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 seguinte consulta 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: Backup e restauração do banco de dados de usuários individuais para a Instância Gerenciada 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 Guia de início rápido: restaurar um banco de dados para uma instância gerenciada do SQL.

Parte 4: Migrar usuários para a instância gerenciada SQL

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

1. Entre em sua Instância Gerenciada SQL usando a conta de administrador do Microsoft Entra para Instância Gerenciada SQL. Em seguida, crie seu logon do Microsoft Entra na Instância Gerenciada do SQL usando a sintaxe a seguir. Para obter mais informações, consulte Tutorial: Segurança da instância gerenciada do SQL no Banco de dados SQL do Azure usando entidades de servidor (logons) do 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
   

2. Verifique a migração para obter o banco de dados, a tabela e os principais corretos.

   -- 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: Testando a autenticação de usuário ou grupo do Microsoft Entra

Teste a autenticação na Instância Gerenciada SQL usando o usuário previamente mapeado 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 SQL do Azure como aadsqlmi\testUser1

2. Usando o SQL Server Management Studio (SSMS), entre em sua instância gerenciada do SQL usando a autenticação integrada do Ative Directory, conectando-se ao banco de dados migration.

   1. Você também pode entrar usando as testUser1@aadsqlmi.net credenciais com a opção SSMS Ative Directory – Universal com suporte a MFA. No entanto, neste caso, não pode utilizar o mecanismo de Início de Sessão Único e tem de escrever uma palavra-passe. Você não precisará usar uma VM federada para fazer logon em sua Instância Gerenciada SQL.

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

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

Teste a autenticação em uma Instância Gerenciada SQL usando um membro de um grupo migrationdo Windows. 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 SQL do Azure como aadsqlmi\testGroupUser

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

   1. Você também pode entrar usando as testGroupUser@aadsqlmi.net credenciais com a opção SSMS Ative Directory – Universal com suporte a MFA. No entanto, neste caso, não pode utilizar o mecanismo de Início de Sessão Único e tem de escrever uma palavra-passe. Você não precisará usar uma VM federada para fazer logon em sua Instância Gerenciada SQL.

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

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

Nota

Devido a um problema de design conhecido para o Banco de Dados SQL do Azure, uma instrução create a table executada como membro de um grupo 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 atual é criar uma tabela com um esquema existente no caso acima <dbo.new>

Próximos passos

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