共用方式為

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

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

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

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

  • 使用 Azure SQL 資料庫的新語法延伸項目，在虛擬 master 資料庫中建立 Microsoft Entra 登入
  • 在虛擬 master 資料庫中建立對應至 Microsoft Entra 登入的使用者
  • 使用 WITH OBJECT_ID 的語法來表示非唯一的顯示名稱
  • 將伺服器角色授與 Microsoft Entra 使用者
  • 停用 Microsoft Entra 登入

注意

Microsoft Entra 伺服器主體 (登入) 目前處於 Azure SQL 資料庫的公開預覽狀態。 Azure SQL 受控執行個體和 SQL Server 2022 和更新版本已經可以在正式發行中使用 Microsoft Entra 登入。

必要條件

注意

對於在 Microsoft Entra ID 中處理非唯一顯示名稱的組織 （特別是服務主體），請確定您有可供您計劃建立登入的任何服務主體使用的物件識別碼資訊。 WITH OBJECT_ID語法延伸可以幫助解決這些情況。

建立 Microsoft Entra 登入

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

    注意

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

    提示

    如果您在 Microsoft Entra ID 中遇到非唯一顯示名稱的問題，您可以使用 WITH OBJECT_ID 語法擴展。 此功能對於具有重複顯示名稱的服務主體特別有用。 例如：

    CREATE LOGIN [myapp4466e] FROM EXTERNAL PROVIDER 
  WITH OBJECT_ID = 'aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb'

    如需詳細資訊，請參閱 Microsoft Entra 登入和具有非唯一顯示名稱的使用者。

  2. 使用 SQL Server Management Studio (SSMS)，使用為伺服器設定的 Microsoft Entra 管理帳戶登入您的 SQL Database。

  3. 展開 資料庫>系統資料庫。 以滑鼠右鍵按兩下 master 資料庫，然後選取 [[新增查詢]，以在 master 資料庫的內容中開啟新的查詢視窗。

  4. 執行下列查詢：

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

  5. 檢查 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

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

在 Microsoft Entra ID 中建立使用者

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

  2. 我們仍在使用虛擬 master 資料庫，但如果您想要在其他資料庫中建立使用者，您可以重新連線到您選擇的資料庫。 執行下列查詢。

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

    提示

    雖然不需要使用 Microsoft Entra 使用者別名 （例如 bob@contoso.com），建議最佳做法是針對 Microsoft Entra 使用者和 Microsoft Entra 登入使用相同的別名。

    如果您需要直接從具有特定物件 ID 的外部提供者建立使用者 （例如，解決非唯一顯示名稱問題），您也可以使用：

    CREATE USER [user_name] FROM EXTERNAL PROVIDER 
  WITH OBJECT_ID = 'objectid'

  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 登入。 如需任何資訊，請參閱具有非唯一顯示名稱的 Microsoft Entra 登入和使用者。

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

您可以將登入新增至 固定伺服器層級角色，例如位於 資料庫中的 ##MS_DefinitionReader##、##MS_ServerStateReader##或 master 角色。

注意

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

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

相關內容

  • Last updated on