チュートリアル:T-SQL DDL 構文を使用して SQL Server インスタンスの Windows ユーザーとグループを Azure SQL Managed Instance に移行する

[アーティクル]
10/31/2023

適用対象:Azure SQL Managed Instance

この記事では、T-SQL 構文を使用して、オンプレミスの SQL Server 内の Windows ユーザーとグループを Azure SQL Managed Instance に移行するプロセスについて説明します。

このチュートリアルでは、以下の内容を学習します。

SQL Server のログインを作成する
移行用のテストデータベースを作成する
ログイン、ユーザー、およびロールを作成する
データベースをバックアップして SQL Managed Instance (MI) に復元する
ALTER USER 構文を使用して手動でユーザーを MI に移行する
新しいマップされたユーザーによる認証のテスト

Note

Microsoft Entra ID の、旧称は Azure Active Directory（Azure AD）です。

前提条件

このチュートリアルを完了するには、次の前提条件が適用されます。

Windows ドメインは Microsoft Entra ID とフェデレーションされています。
ユーザー/グループを作成できる Active Directory へのアクセス権。
オンプレミス環境の既存の SQL Server。
既存の SQL Managed Instance 。「クイックスタート:Azure SQL Managed Instance を作成する」を参照してください。
- Microsoft Entra ログインを作成するには、SQL Managed Instance 内の sysadmin が使用される必要があります。
SQL managed instance の Microsoft Entra 管理者を作成します。
ネットワーク内の SQL Managed Instance に接続できます。追加情報については、次の記事をご覧ください：

T-SQL DDL 構文

Microsoft Entra 認証を使用して Windows ユーザーとグループを SQL Server インスタンスから SQL Managed Instance に移行する処理をサポートするために使用される T-SQL DDL 構文を次に示します。

-- 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 サーバーでユーザーとグループのログインを作成します。以下の構文を実行する前に、ユーザーとグループが Active Directory (AD) 内に存在することを確認する必要があります。

Users: testUser1, testGroupUser
Group: migration - testGroupUser は AD の移行グループに属している必要があります

以下の例では、ドメイン aadsqlmi の testUser1 という名前のアカウントのログインを SQL Server で作成します。

-- 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 Managed Instance に復元する

「バックアップと復元によるデータベースのコピー」の記事を使用するか、次の構文を使用して、移行データベースのバックアップを作成します。

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

「クイックスタート:SSMS を使用してデータベースを SQL Managed Instance に復元する」を参照してください。

パート 4:SQL Managed Instance にユーザーを移行する

ALTER USER コマンドを実行して、SQL Managed Instance の移行プロセスを完了します。

SQL Managed Instance の Microsoft Entra 管理者アカウントを使用して、SQL Managed Instance にサインインします。次に、以下の構文を使用して、SQL Managed Instance に Microsoft Entra ログインを作成します。詳細については、「チュートリアル:Microsoft Entra サーバープリンシパル (ログイン) を使用した Azure SQL Database の SQL Managed Instance セキュリティ」を参照してください。
```
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 ユーザーまたはグループの認証をテストする

ALTER USER 構文を使用して、以前に Microsoft Entra ログインにマップされたユーザーを使用して、SQL Managed Instance への認証をテストします。

aadsqlmi\testUser1 という Azure SQL Managed Instance サブスクリプションを使用して、フェデレーション VM にログインします
SQL Server Management Studio (SSMS) と Active Directory 統合認証を使用して、SQL Managed Instance にサインインし、データベース migration に接続します。
1. SSMS オプション [Active Directory - MFA サポートで汎用] で testUser1@aadsqlmi.net の資格情報を使用してサインインすることもできます。ただし、この場合、シングルサインオンメカニズムを使用することはできず、パスワードを入力する必要があります。フェデレーション VM を使用して SQL Managed Instance にログインする必要はありません。
ロールメンバー SELECT の一部として、test テーブルから選択できます
```
Select * from test  --  and see one row (1,10)
```

Windows グループ migration のメンバーを使用して、SQL Managed Instance に対する認証をテストします。移行の前に、ユーザー aadsqlmi\testGroupUser がグループ migration に追加されている必要があります。

aadsqlmi\testGroupUser という Azure SQL Managed Instance サブスクリプションを使用して、フェデレーション VM にログインします
SSMS と Active Directory 統合認証を使用して、Azure SQL Managed Instance サーバーとデータベース migration に接続します
1. SSMS オプション [Active Directory - MFA サポートで汎用] で testGroupUser@aadsqlmi.net の資格情報を使用してサインインすることもできます。ただし、この場合、シングルサインオンメカニズムを使用することはできず、パスワードを入力する必要があります。フェデレーション VM を使用して SQL Managed Instance にログインする必要はありません。

db_owner ロールの一部として、新しいテーブルを作成できます。

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

Note

Azure SQL Database の設計上の既知の問題により、グループのメンバーとして実行されるテーブルの作成ステートメントは、次のエラーで失敗します。

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.

現在の回避策は、上記の <dbo.new> の場合に既存のスキーマを使用してテーブルを作成することです

次のステップ

チュートリアル:DMS を使用してオフラインで SQL Server を Azure SQL Managed Instance に移行する

チュートリアル:T-SQL DDL 構文を使用して SQL Server インスタンスの Windows ユーザーとグループを Azure SQL Managed Instance に移行する

前提条件

T-SQL DDL 構文

引数

パート 1: SQL Server で Windows ユーザーとグループのログインを作成する

パート 2: Windows ユーザーとグループを作成し、ロールとアクセス許可を追加する

パート 3: 個々のユーザーデータベースをバックアップして SQL Managed Instance に復元する

パート 4:SQL Managed Instance にユーザーを移行する

パート 5: Microsoft Entra ユーザーまたはグループの認証をテストする

次のステップ

フィードバック

フィードバック

その他のリソース

チュートリアル:T-SQL DDL 構文を使用して SQL Server インスタンスの Windows ユーザーとグループを Azure SQL Managed Instance に移行する

前提条件

T-SQL DDL 構文

引数

パート 1: SQL Server で Windows ユーザーとグループのログインを作成する

パート 2: Windows ユーザーとグループを作成し、ロールとアクセス許可を追加する

パート 3: 個々のユーザー データベースをバックアップして SQL Managed Instance に復元する

パート 4:SQL Managed Instance にユーザーを移行する

パート 5: Microsoft Entra ユーザーまたはグループの認証をテストする

次のステップ

フィードバック

フィードバック

その他のリソース

パート 3: 個々のユーザーデータベースをバックアップして SQL Managed Instance に復元する