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

Статья
10/20/2023

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

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

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

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

Примечание.

Идентификатор Microsoft Entra ранее был известен как Azure Active Directory (Azure AD).

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

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

Домен 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 имена пользователей и групп. Перед выполнением описанного ниже синтаксиса необходимо убедиться, что пользователь и группа есть в Active Directory (AD).

Пользователи: testUser1, testGroupUser
Группа: миграция — testGroupUser должна принадлежать группе миграции в AD

В приведенном ниже примере в 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.

Войдите в Управляемый экземпляр 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
```

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

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

Используйте синтаксис 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;

Используйте синтаксис 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.

Войдите на федеративную виртуальную машину, используя подписку управляемого экземпляра SQL Azure в качестве aadsqlmi\testUser1.
Из SQL Server Management Studio (SSMS) войдите в управляемый экземпляр SQL Azure, используя встроенную в доменные службы Active Directory проверку подлинности и подключитесь к базе данных migration.
1. Кроме того, вы можете войти с помощью учетных данных testUser1@aadsqlmi.net, используя параметр SSMS Active Directory — универсальная с поддержкой MFA. Но в этом случае вы не сможете использовать механизм единого входа и вам придется ввести пароль. Для входа в управляемый экземпляр вам не потребуется использовать федеративную виртуальную машину.
Являясь членом роли SELECT, вы можете получать данные из таблицы test.
```
Select * from test  --  and see one row (1,10)
```

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

Войдите на федеративную виртуальную машину, используя подписку управляемого экземпляра SQL Azure в качестве aadsqlmi\testGroupUser.
Используя SSMS со встроенной в доменные службы Active Directory проверкой подлинности, подключитесь к серверу управляемого экземпляра SQL Azure и к базе данных migration.
1. Кроме того, вы можете войти с помощью учетных данных testGroupUser@aadsqlmi.net, используя параметр SSMS Active Directory — универсальная с поддержкой MFA. Но в этом случае вы не сможете использовать механизм единого входа и вам придется ввести пароль. Для входа в управляемый экземпляр SQL Azure вам не потребуется использовать федеративную виртуальную машину.
Являясь членом роли db_owner, вы можете создать новую таблицу.
```
-- Create table named 'new' with a default schema
Create table dbo.new ( a int, b int)
```

Примечание.

Из-за известной проблемы проектирования для База данных SQL Azure создание инструкции таблицы, выполняемой в качестве члена группы, завершится ошибкой следующей ошибки: текущий обходной путь — создать таблицу с существующей схемой в случае выше dbo.new.

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.

<>

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

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

Поделиться через