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

適用対象: Azure SQL Managed Instance

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

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

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

前提条件

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

T-SQL DDL 構文

Azure AD 認証を使用して 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
Azure AD ログインにユーザーを再マッピングします

groupName
データベース内で識別されるグループの名前を指定します。

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

重要

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

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

以下の例では、ドメイン aadsqlmitestUser1 という名前のアカウントのログインを 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 の移行プロセスを完了します。

  1. SQL Managed Instance 用の Azure AD 管理者アカウントを使用して、SQL Managed Instance にサインインします。 次に、以下の構文を使用して、SQL Managed Instance に Azure AD ログインを作成します。 詳細については、「チュートリアル:Azure AD サーバー プリンシパル (ログイン) を使用した Azure 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
    
  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 構文を使用して、オンプレミス ユーザーを Azure AD ログインにマップします。

    /** 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 構文を使用して、オンプレミス グループを Azure AD ログインにマップします。

    /** 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: Azure AD ユーザーまたはグループの認証をテストする

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

  1. aadsqlmi\testUser1 という Azure SQL Managed Instance サブスクリプションを使用して、フェデレーション VM にログインします

  2. SQL Server Management Studio (SSMS) と Active Directory 統合認証を使用して、SQL Managed Instance にサインインし、データベース migration に接続します。

    1. SSMS オプション [Active Directory - MFA サポートで汎用] で testUser1@aadsqlmi.net の資格情報を使用してサインインすることもできます。 ただし、この場合、シングル サインオン メカニズムを使用することはできず、パスワードを入力する必要があります。 フェデレーション VM を使用して SQL Managed Instance にログインする必要はありません。
  3. ロール メンバー SELECT の一部として、test テーブルから選択できます

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

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

  1. aadsqlmi\testGroupUser という Azure SQL Managed Instance サブスクリプションを使用して、フェデレーション VM にログインします

  2. SSMS と Active Directory 統合認証を使用して、Azure SQL Managed Instance サーバーとデータベース migration に接続します

    1. SSMS オプション [Active Directory - MFA サポートで汎用] で testGroupUser@aadsqlmi.net の資格情報を使用してサインインすることもできます。 ただし、この場合、シングル サインオン メカニズムを使用することはできず、パスワードを入力する必要があります。 フェデレーション VM を使用して SQL Managed Instance にログインする必要はありません。
  3. 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 に移行する