教學課程：建立及使用 Azure Active Directory 伺服器登入

  • 發行項

適用於： Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics (僅適用於專用的 SQL 集區)

注意

Azure Active Directory (Azure AD) 伺服器主體 (登入) 目前可供 Azure SQL Database 公開預覽。 Azure SQL 受控執行個體已使用 Azure AD 登入。

本文會引導您在 Azure SQL 的虛擬 master 資料庫中建立和使用 Azure Active Directory (Azure AD) 主體 (登入)。

在本教學課程中，您會了解如何：

  • 使用 Azure SQL Database 的新語法延伸模組，在虛擬 master 資料庫中建立 Azure AD 登入
  • 在虛擬 master 資料庫中建立對應至 Azure AD 登入的使用者
  • 將伺服器角色授與 Azure AD 使用者
  • 停用 Azure AD 登入

必要條件

建立 Azure AD 登入

  1. 建立 Azure AD 帳戶的 Azure SQL Database 登入。 我們會在範例中使用存在 Azure AD 網域 contoso 中的 bob@contoso.com。 您也可以從 Azure AD 群組或服務主體 (應用程式) 建立登入。 例如，mygroup Azure AD 群組是由此群組成員的 Azure AD 帳戶所組成。 如需詳細資訊，請參閱 CREATE LOGIN (Transact-SQL)。

    注意

    第一個 Azure AD 登入必須由 Azure Active Directory 管理員所建立。Azure AD 管理員可以是 Azure AD 使用者或群組。 SQL 登入無法建立 Azure AD 登入。

  2. 使用 SQL Server Management Studio (SSMS)，以為伺服器設定的 Azure AD 系統管理員帳戶登入您的 SQL Database。

  3. 執行下列查詢：

    Use master
CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER
GO

  4. 檢查 sys.server_principals 中已建立的登入。 執行以下查詢：

    SELECT name, type_desc, type, is_disabled 
FROM sys.server_principals
WHERE type_desc like 'external%'

    您應該會看到類似以下的輸出：

    Name                            type_desc       type   is_disabled 
bob@contoso.com                 EXTERNAL_LOGIN  E      0

  5. 已在虛擬 master 資料庫中建立登入 bob@contoso.com。

從 Azure AD 登入建立使用者

  1. 既然我們已建立 Azure AD 登入，就可以建立對應至虛擬 master 資料庫中 Azure AD 登入的資料庫層級 Azure AD 使用者。 我們會繼續使用範例 bob@contoso.com 以在虛擬 master 資料庫中建立使用者，因為我們想要示範如何將使用者新增至特殊角色。 只有 Azure AD 管理員或 SQL Server 管理員才能在虛擬 master 資料庫中建立使用者。

  2. 我們使用的是虛擬 master 資料庫，但如果您想要在其他資料庫中建立使用者，則可以切換至您選擇的資料庫。 執行下列查詢。

    Use master
CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com]

    提示

    雖然不是必須使用 Azure AD 使用者別名 (例如 bob@contoso.com)，但最佳做法建議 Azure AD 使用者和 Azure AD 登入使用相同的別名。

  3. 檢查 sys.database_principals 中已建立的使用者。 執行以下查詢：

    SELECT name, type_desc, type 
FROM sys.database_principals 
WHERE type_desc like 'external%'

    您應該會看到類似以下的輸出：

    Name                            type_desc       type
bob@contoso.com                 EXTERNAL_USER   E

注意

仍支援在沒有 Azure AD 登入的情況下建立 Azure AD 使用者的現有語法，但必須在 SQL Database (沒有登入) 內建立自主使用者。

例如： CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER 。

將伺服器層級角色授與 Azure AD 登入

您可以將登入新增至固定伺服器層級角色，例如 ##MS_DefinitionReader##、##MS_ServerStateReader## 或 ##MS_ServerStateManager## 角色。

注意

此處提及的伺服器層級角色不適用於 Azure AD 群組。

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateManager## ADD MEMBER [AzureAD_object];

在使用者重新連線之前，權限不會生效。 也會排清 DBCC 快取：

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

請執行下列查詢，檢查屬於伺服器層級角色的 Azure AD 登入：

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       FROM sys.server_role_members AS server_role_members
       INNER JOIN sys.server_principals AS roles
       ON server_role_members.role_principal_id = roles.principal_id
       INNER JOIN sys.server_principals AS members 
       ON server_role_members.member_principal_id = members.principal_id;

為 Azure AD 使用者授與特殊角色

SQL Database 的特殊角色可以指派給虛擬 master 資料庫中的使用者。

若要將其中一個特殊資料庫角色授與使用者，使用者必須存在於虛擬 master 資料庫中。

您可以執行下列查詢，將使用者新增至某個角色：

ALTER ROLE [dbmanager] ADD MEMBER [AzureAD_object]

請執行下列查詢，從角色中移除使用者：

ALTER ROLE [dbmanager] DROP MEMBER [AzureAD_object]

AzureAD_object 可以是Azure AD 的 Azure AD 使用者、群組或服務主體。

我們在範例中建立了使用者 bob@contoso.com。 讓我們授與使用者 dbmanager 和 loginmanager 角色。

  1. 執行下列查詢：

    ALTER ROLE [dbmanager] ADD MEMBER [bob@contoso.com] 
ALTER ROLE [loginmanager] ADD MEMBER [bob@contoso.com]

  2. 執行下列查詢以檢查資料庫角色指派：

    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'and DP2.name like 'bob%'

    您應該會看到類似以下的輸出：

    DatabaseRoleName       DatabaseUserName 
dbmanager              bob@contoso.com
loginmanager           bob@contoso.com

選擇性 - 停用登入

ALTER LOGIN (Transact-SQL) DDL 語法可用來啟用或停用 Azure SQL Database 的 Azure AD 登入。

ALTER LOGIN [bob@contoso.com] DISABLE

若要讓 DISABLE 或 ENABLE 變更立即生效，您必須使用下列 T-SQL 命令清除驗證快取和 TokenAndPermUserStore 快取：

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

執行下列查詢可檢查登入是否已遭停用：

SELECT name, type_desc, type 
FROM sys.server_principals 
WHERE is_disabled = 1

這種使用案例可能是允許異地複本使用唯讀，但拒絕主伺服器連線。

另請參閱

如需詳細資訊與範例，請參閱：