Tutorial: Memigrasikan pengguna dan grup Windows dalam instans SQL Server ke Azure SQL Managed Instance menggunakan sintaks T-SQL DDL

Artikel
10/20/2023

Berlaku untuk:Azure SQL Managed Instance

Artikel ini membawa Anda melalui proses migrasi pengguna dan grup Windows lokal Anda di SQL Server Anda ke Azure SQL Managed Instance menggunakan sintaks T-SQL.

Dalam tutorial ini, Anda akan mempelajari cara:

Membuat login untuk SQL Server
Membuat database uji untuk migrasi
Membuat login, pengguna, dan peran
Mencadangkan dan memulihkan database Anda ke SQL Managed Instance (MI)
Melakukan migrasi pengguna secara manual ke MI menggunakan sintaks UBAH PENGGUNA
Menguji autentikasi dengan pengguna baru yang dipetakan

Catatan

ID Microsoft Entra sebelumnya dikenal sebagai Azure Active Directory (Azure AD).

Prasyarat

Untuk menyelesaikan tutorial ini, prasyarat berikut berlaku:

Domain Windows digabungkan dengan ID Microsoft Entra.
Akses ke Direktori Aktif untuk membuat pengguna/grup.
SQL Server yang ada di lingkungan lokal Anda.
SQL Managed Instance yang ada. Lihat Mulai Cepat: Membuat SQL Managed Instance.
- sysadmin di SQL Managed Instance harus digunakan untuk membuat login Microsoft Entra.
Buat admin Microsoft Entra untuk SQL Managed Instance.
Anda dapat tersambung ke SQL Managed Instance dalam jaringan Anda. Lihat artikel berikut ini untuk informasi tambahan:

Sintaks T-SQL DDL

Di bawah ini adalah sintaks T-SQL DDL yang digunakan untuk mendukung migrasi pengguna dan grup Windows dari instans SQL Server ke SQL Managed Instance dengan autentikasi 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]

Argumen

domainName
Menentukan nama domain pengguna.

userName
Menentukan nama pengguna yang diidentifikasi di dalam database.

= loginName@domainName.com
Memetakan ulang pengguna ke login Microsoft Entra

groupName
Menentukan nama grup yang diidentifikasi di dalam database.

Bagian 1: Membuat login di SQL Server untuk pengguna dan grup Windows

Penting

Sintaks berikut membuat pengguna dan grup masuk di SQL Server Anda. Anda harus memastikan bahwa pengguna dan grup ada di dalam Direktori Aktif (AD) Anda sebelum mengeksekusi sintaks di bawah ini.

Pengguna: testUSer1, testGroupUser
Grup: migrasi - testGroupUser harus termasuk dalam grup migrasi di AD

Contoh di bawah ini membuat login di SQL Server untuk akun bernama testUser1 di bawah domain 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;

Buat database untuk pengujian ini.

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

Bagian 2: Membuat pengguna dan grup Windows, lalu menambahkan peran dan izin

Gunakan sintaks berikut untuk membuat pengguna pengujian.

use migration;  
go

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

Periksa izin pengguna:

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

Buat peran dan tetapkan pengguna pengujian Anda ke peran ini:

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

Gunakan kueri berikut untuk menampilkan nama pengguna yang ditetapkan ke peran tertentu:

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

Gunakan sintaks berikut untuk membuat grup. Kemudian tambahkan grup ke peran 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)

Buat tabel uji dan tambahkan beberapa data menggunakan sintaks berikut:

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

Bagian 3: Mencadangkan dan memulihkan database pengguna individu ke SQL Managed Instance

Buat cadangan database migrasi menggunakan artikel Salin Database dengan Pencadangan dan Pemulihan, atau gunakan sintaks berikut:

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

Ikuti Mulai cepat kami: Memulihkan database ke SQL Managed Instance.

Bagian 4: Memigrasikan pengguna ke SQL Managed Instance

Jalankan perintah UBAH PENGGUNA untuk menyelesaikan proses migrasi pada SQL Managed Instance.

Masuk ke SQL Managed Instance Anda menggunakan akun admin Microsoft Entra untuk SQL Managed Instance. Kemudian buat login Microsoft Entra Anda di SQL Managed Instance menggunakan sintaks berikut. Untuk informasi selengkapnya, lihat Tutorial: Keamanan SQL Managed Instance di Azure SQL Database menggunakan prinsipal server Microsoft Entra (login).

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

Periksa migrasi Anda untuk database, tabel, dan prinsip yang benar.

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

Gunakan sintaks ALTER USER untuk memetakan pengguna lokal ke login 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;

Gunakan sintaks ALTER USER untuk memetakan grup lokal ke login 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'

Bagian 5: Menguji autentikasi pengguna atau grup Microsoft Entra

Uji autentikasi ke SQL Managed Instance menggunakan pengguna yang sebelumnya dipetakan ke login Microsoft Entra menggunakan sintaks ALTER USER.

Masuk ke komputer virtual federasi menggunakan langganan Azure SQL Managed Instance Anda sebagai aadsqlmi\testUser1
Menggunakan SQL Server Management Studio (SSMS), masuk ke SQL Managed Instance Anda menggunakan autentikasi Direktori Aktif Terintegrasi, yang menyambungkan ke database migration.
1. Anda juga dapat masuk menggunakan testUser1@aadsqlmi.netkredensial dengan opsi SSMS Direktori Aktif – Universal dengan dukungan MFA. Namun, dalam hal ini, Anda tidak dapat menggunakan mekanisme Akses Menyeluruh dan Anda harus mengetikkan kata sandi. Anda tidak perlu menggunakan komputer virtual federasi untuk masuk ke SQL Managed Instance Anda.
Sebagai bagian dari anggota peran PILIH,Anda dapat memilih dari test tabel
```
Select * from test  --  and see one row (1,10)
```

Uji autentikasi ke SQL Managed Instance menggunakan anggota grup Windowsmigration. Pengguna aadsqlmi\testGroupUser seharusnya ditambahkan ke grup migration sebelum migrasi.

Masuk ke komputer virtual federasi menggunakan langganan Azure SQL Managed Instance Anda sebagai aadsqlmi\testGroupUser
Menggunakan SSMS dengan autentikasi Direktori Aktif Terintegrasi, sambungkan ke server Azure SQL Managed Instance dan database migration
1. Anda juga dapat masuk menggunakan testGroupUser@aadsqlmi.netkredensial dengan opsi SSMS Direktori Aktif – Universal dengan dukungan MFA. Namun, dalam hal ini, Anda tidak dapat menggunakan mekanisme Akses Menyeluruh dan Anda harus mengetikkan kata sandi. Anda tidak perlu menggunakan komputer virtual federasi untuk masuk ke SQL Managed Instance Anda.

Sebagai bagian dari db_owner peran, Anda dapat membuat tabel baru.

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