教學課程：建立並利用 Microsoft Entra 伺服器登入

  • 發行項

適用於：Azure SQL 資料庫Azure SQL 受控執行個體Azure Synapse Analytics (僅限專用 SQL 集區)

本文會引導您在 Azure SQL 的虛擬 master 資料庫中建立及使用 Microsoft Entra ID (先前稱為 Azure Active Directory)支援的登入。

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

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

注意

Microsoft Entra 伺服器主體 (登入) 目前處於 Azure SQL 資料庫的公開預覽狀態。 Azure SQL 受控執行個體已使用 Microsoft Entra 登入。

必要條件

建立 Microsoft Entra 登入

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

    注意

    第一次 Microsoft Entra 登入必須由 Microsoft Entra 管理員建立。Microsoft Entra 管理員可以是 Microsoft Entra 使用者或群組。 SQL 登入無法建立 Microsoft Entra 登入。

  2. 使用 SQL Server Management Studio (SSMS)，使用為伺服器設定的 Microsoft Entra 管理帳戶登入您的 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。

在 Microsoft Entra ID 中建立使用者

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

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

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

    提示

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

  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

注意

目前仍支援在沒有 Microsoft Entra 登入的情況下建立 Microsoft Entra 使用者的現有語法。 執行下列語法會在您所連線的特定資料庫內建立資料庫內部使用者。 重要的是，即使虛擬 master 資料庫中有相同名稱的登入，此使用者也不會與任何登入相關聯。

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

授與 Microsoft Entra 登入伺服器層級的角色

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

注意

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

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

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

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;

向 Microsoft Entra 使用者授與特殊角色 

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

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

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

ALTER ROLE [dbmanager] ADD MEMBER [AzureAD_object]

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

ALTER ROLE [dbmanager] DROP MEMBER [AzureAD_object]

AzureAD_object 可以是 Microsoft Entra ID 中的 Microsoft Entra 使用者、群組或服務主體。

我們在範例中建立了使用者 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 資料庫的 Microsoft Entra 登入。

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

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

另請參閱

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