Esercitazione: Eseguire la migrazione di utenti e gruppi di Windows in un'istanza di SQL Server a Istanza gestita di SQL di Azure usando la sintassi DDL T-SQL

Articolo
10/20/2023

Si applica a:Azure SQL Managed Instance

Questo articolo illustra il processo di migrazione di utenti e gruppi locali di Windows in SQL Server a un'istanza gestita di SQL di Azure con la sintassi T-SQL.

In questa esercitazione apprenderai a:

Creare account di accesso per SQL Server
Creare un database di test per la migrazione
Creare account di accesso, utenti e ruoli
Eseguire il backup e il ripristino del database in un'istanza gestita di SQL
Eseguire manualmente la migrazione degli utenti a un'istanza gestita con la sintassi ALTER USER
Testare l'autenticazione con i nuovi utenti mappati

Nota

Microsoft Entra ID era precedentemente noto come Azure Active Directory (Azure AD).

Prerequisiti

Per completare questa esercitazione, è necessario soddisfare i prerequisiti seguenti:

Il dominio Windows è federato con Microsoft Entra ID.
Accesso ad Active Directory per la creazione di utenti/gruppi.
Un'istanza di SQL Server esistente nell'ambiente locale.
Un'istanza gestita di SQL esistente. Vedere Avvio rapido: Creare un Istanza gestita di SQL.
- Un sysadmin oggetto nella Istanza gestita di SQL deve essere utilizzato per creare account di accesso di Microsoft Entra.
Creare un amministratore di Microsoft Entra per Istanza gestita di SQL.
È possibile connettersi all'istanza gestita di SQL all'interno della rete. Per altre informazioni, vedere gli articoli seguenti:

Sintassi DDL T-SQL

Di seguito è riportata la sintassi DDL T-SQL usata per supportare la migrazione di utenti e gruppi di Windows da un'istanza di SQL Server a Istanza gestita di SQL con l'autenticazione 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]

Argomenti

domainName
Specifica il nome di dominio dell'utente.

userName
Specifica il nome dell'utente identificato all'interno del database.

= loginName@domainName.com
Esegue nuovamente il mapping di un utente all'account di accesso di Microsoft Entra

groupName
Specifica il nome del gruppo identificato all'interno del database.

Parte 1: Creare account di accesso in SQL Server per utenti e gruppi di Windows

Importante

La sintassi seguente crea un account di accesso per un utente e un gruppo in SQL Server. Prima di eseguire questa sintassi, è necessario assicurarsi che l'utente e il gruppo esistano all'interno di Active Directory (AD).

Utenti: testUser1, testGroupUser
Group: migrazione - testGroupUser deve appartenere al gruppo di migrazione in AD

L'esempio seguente crea un account di accesso in SQL Server per un account denominato testUser1 nel 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;

Crea un database per questo test.

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

Parte 2: Creare utenti e gruppi di Windows, quindi aggiungere ruoli e autorizzazioni

Usare la sintassi seguente per creare l'utente di test.

use migration;  
go

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

Controllare le autorizzazioni dell'utente:

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

Creare un ruolo e assegnarlo all'utente di test:

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

Usare la query seguente per visualizzare i nomi utente assegnati a un ruolo specifico:

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

Usare la sintassi seguente per creare un gruppo. Quindi aggiungere il gruppo al ruolo 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)

Creare una tabella di test e aggiungere alcuni dati usando la sintassi seguente:

-- 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: Eseguire il backup e ripristinare il database utente singolo in Istanza gestita di SQL

Creare un backup del database di migrazione usando l'articolo Copiare i database con backup e ripristino oppure usare la sintassi seguente:

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

Seguire la guida introduttiva: Ripristinare un database in un Istanza gestita di SQL.

Parte 4: Eseguire la migrazione degli utenti a Istanza gestita di SQL

Eseguire il comando ALTER USER per completare il processo di migrazione nell'istanza gestita di SQL.

Accedere al Istanza gestita di SQL usando l'account amministratore di Microsoft Entra per Istanza gestita di SQL. Creare quindi l'account di accesso di Microsoft Entra nel Istanza gestita di SQL usando la sintassi seguente. Per altre informazioni, vedere Tutorial: Istanza gestita di SQL security in database SQL di Azure using Microsoft Entra server principals (logins).
```
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
```

Controllare la migrazione per verificare se il database, la tabella e le entità sono corretti.

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

Usare la sintassi ALTER U edizione Standard R per eseguire il mapping dell'utente locale all'account di accesso di 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;

Usare la sintassi ALTER U edizione Standard R per eseguire il mapping del gruppo locale all'account di accesso di 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: Test dell'autenticazione utente o gruppo di Microsoft Entra

Testare l'autenticazione per Istanza gestita di SQL usando l'utente mappato in precedenza all'account di accesso di Microsoft Entra usando la sintassi ALTER U edizione Standard R.

Accedere alla macchina virtuale federata con la sottoscrizione dell'istanza gestita di SQL di Azure come aadsqlmi\testUser1
Da SQL Server Management Studio (SSMS) accedere all'istanza gestita di SQL usando l'autenticazione integrata di Active Directory, connettendosi al database migration.
1. È anche possibile accedere usando le credenziali testUser1@aadsqlmi.net con l'opzione di SSMS Active Directory - Universale con supporto MFA. Tuttavia, in questo caso, non è possibile usare il meccanismo Single Sign-On ed è necessario digitare una password. Non sarà necessario usare una macchina virtuale federata per accedere all'istanza gestita di SQL.
Come parte del membro del ruolo SELECT, è possibile selezionare dalla tabella test
```
Select * from test  --  and see one row (1,10)
```

Testare l'autenticazione a un'istanza gestita di SQL usando un membro di un gruppo migration di Windows. È necessario che l'utente aadsqlmi\testGroupUser sia stato aggiunto al gruppo migration prima della migrazione.

Accedere alla macchina virtuale federata con la sottoscrizione dell'istanza gestita di SQL di Azure come aadsqlmi\testGroupUser
Usando SSMS con l'autenticazione integrata di Active Directory, connettersi al server dell'istanza gestita di SQL di Azure e al database migration
1. È anche possibile accedere usando le credenziali testGroupUser@aadsqlmi.net con l'opzione di SSMS Active Directory - Universale con supporto MFA. Tuttavia, in questo caso, non è possibile usare il meccanismo Single Sign-On ed è necessario digitare una password. Non sarà necessario usare una macchina virtuale federata per accedere all'istanza gestita di SQL.

Come parte del ruolo db_owner, è possibile creare una nuova tabella.

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