Учебник. Перенос пользователей и групп Windows из экземпляра SQL Server в Управляемый экземпляр SQL Azure с использованием синтаксиса T-SQL DDL

Область применения: Управляемый экземпляр SQL Azure

В этой статье описан перенос пользователей и групп Windows, настроенных для SQL Server, в управляемый экземпляр SQL Azure с использованием синтаксиса T-SQL.

В этом руководстве описано следующее:

Необходимые компоненты

Для работы с этим руководством вам потребуется следующее:

• Домен Windows федеративный с идентификатором Microsoft Entra.
• доступ к Active Directory для создания пользователей и групп;
• SQL Server в локальной среде;
• Существующий управляемый экземпляр SQL. См. Краткое руководство. Создание Управляемого экземпляра SQL Azure.
  • Для sysadmin создания имен входа Microsoft Entra необходимо использовать в Управляемый экземпляр SQL.
• Создайте администратора Microsoft Entra для Управляемый экземпляр SQL.
• Вы можете подключиться к управляемому экземпляру SQL Azure из локальной сети. Дополнительные сведения см. в следующих статьях:
  • Connect your application to Azure SQL Managed Instance (Подключение приложения к управляемому экземпляру SQL Azure)
  • Quickstart. Configure a point-to-site connection to an Azure SQL Managed Instance from on-premises (Краткое руководство. Настройка подключения "точка — сеть" к Управляемому экземпляру SQL Azure)
  • Configure public endpoint in Azure SQL Managed Instance (Настройка общедоступной конечной точки в управляемом экземпляре SQL Azure)

Синтаксис T-SQL DDL

Ниже приведен синтаксис DDL T-SQL, используемый для поддержки миграции пользователей и групп Windows из экземпляра SQL Server в Управляемый экземпляр SQL с проверкой подлинности 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]

Аргументы

domainName
Указывает имя домена для пользователя.

userName
Указывает имя пользователя, определенного в базе данных.

= loginName@domainName.com
переназначает пользователя в имя входа Microsoft Entra

groupName
Указывает имя группы, определенной в базе данных.

Часть 1. Создание имен входа в SQL Server для пользователей и групп Windows

В приведенном ниже примере в SQL Server создается имя входа для учетной записи testUser1 в домене 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;

Создайте базу данных для этого теста.

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

Часть 2. Создание пользователей и групп Windows, добавление ролей и разрешений

Используйте описанный ниже синтаксис, чтобы создать тестового пользователя.

use migration;  
go

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

Проверьте разрешения пользователя:

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

Создайте роль и назначьте тестового пользователя для этой роли:

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

По следующему запросу отображаются имена пользователей, которым назначена указанная роль:

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

Используйте описанный ниже синтаксис, чтобы создать группу. Затем добавьте группу в роль 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)

Создайте тестовую таблицу и добавьте в нее данные с помощью следующего синтаксиса:

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

Часть 3. Резервное копирование и восстановление базы данных пользователей в управляемом экземпляре SQL

Создайте резервную копию базы данных, для которой выполняется миграция, используя инструкции из этой статьи или следующий синтаксис:

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

См. Краткое руководство. Восстановление резервной копии базы данных в Управляемый экземпляр SQL.

Часть 4. Перенос пользователей в управляемый экземпляр SQL Azure

Выполните команду ALTER USER, чтобы завершить миграцию в управляемом экземпляре SQL Azure.

1. Войдите в Управляемый экземпляр SQL с помощью учетной записи администратора Microsoft Entra для Управляемый экземпляр SQL. Затем создайте имя входа Microsoft Entra в Управляемый экземпляр SQL с помощью следующего синтаксиса. Дополнительные сведения см. в руководстве по обеспечению безопасности Управляемый экземпляр SQL в База данных SQL Azure с помощью субъектов сервера Microsoft Entra (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
   

2. Убедитесь, что база данных, таблица и субъекты успешно перенесены.

   -- 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. Используйте синтаксис ALTER USER для сопоставления локального пользователя с именем входа 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. Используйте синтаксис ALTER USER для сопоставления локальной группы с именем входа 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'
   

Часть 5. Тестирование проверки подлинности пользователя или группы Microsoft Entra

Проверка подлинности для Управляемый экземпляр SQL с помощью пользователя, ранее сопоставленного с именем входа Microsoft Entra с помощью синтаксиса ALTER USER.

1. Войдите на федеративную виртуальную машину, используя подписку управляемого экземпляра SQL Azure в качестве aadsqlmi\testUser1.

2. Из SQL Server Management Studio (SSMS) войдите в управляемый экземпляр SQL Azure, используя встроенную в доменные службы Active Directory проверку подлинности и подключитесь к базе данных migration.

   1. Кроме того, вы можете войти с помощью учетных данных testUser1@aadsqlmi.net, используя параметр SSMS Active Directory — универсальная с поддержкой MFA. Но в этом случае вы не сможете использовать механизм единого входа и вам придется ввести пароль. Для входа в управляемый экземпляр вам не потребуется использовать федеративную виртуальную машину.

3. Являясь членом роли SELECT, вы можете получать данные из таблицы test.

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

Проверьте аутентификацию в управляемом экземпляре для члена группы Windows migration. Перед началом миграции пользователя aadsqlmi\testGroupUser нужно добавить в группу migration.

1. Войдите на федеративную виртуальную машину, используя подписку управляемого экземпляра SQL Azure в качестве aadsqlmi\testGroupUser.

2. Используя SSMS со встроенной в доменные службы Active Directory проверкой подлинности, подключитесь к серверу управляемого экземпляра SQL Azure и к базе данных migration.

   1. Кроме того, вы можете войти с помощью учетных данных testGroupUser@aadsqlmi.net, используя параметр SSMS Active Directory — универсальная с поддержкой MFA. Но в этом случае вы не сможете использовать механизм единого входа и вам придется ввести пароль. Для входа в управляемый экземпляр SQL Azure вам не потребуется использовать федеративную виртуальную машину.

3. Являясь членом роли db_owner, вы можете создать новую таблицу.

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

Следующие шаги

Руководство по миграции из SQL Server в Управляемый экземпляр SQL Azure в автономном режиме с помощью DMS