使用 Azure SQL 設定和管理 Azure AD 驗證Configure and manage Azure AD authentication with Azure SQL

適用於: Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics (SQL DW)

本文說明如何建立和填入 Azure Active Directory (Azure AD) 實例,然後使用 Azure AD 搭配 Azure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsThis article shows you how to create and populate an Azure Active Directory (Azure AD) instance, and then use Azure AD with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. 如需總覽,請參閱 Azure Active Directory authenticationFor an overview, see Azure Active Directory authentication.

Azure AD 驗證方法Azure AD authentication methods

Azure AD authentication 支援下列驗證方法:Azure AD authentication supports the following authentication methods:

  • Azure AD 僅限雲端的身分識別Azure AD cloud-only identities
  • Azure AD 支援的混合式身分識別:Azure AD hybrid identities that support:
    • 具有兩個選項的雲端驗證,結合無縫單一登入 (SSO) Cloud authentication with two options coupled with seamless single sign-on (SSO)
      • Azure AD 密碼雜湊驗證Azure AD password hash authentication
      • Azure AD 傳遞驗證Azure AD pass-through authentication
    • 同盟驗證Federated authentication

如需 Azure AD 驗證方法,以及要選擇哪一種方法的詳細資訊,請參閱 為您的 Azure Active Directory 混合式身分識別解決方案選擇正確的驗證方法For more information on Azure AD authentication methods, and which one to choose, see Choose the right authentication method for your Azure Active Directory hybrid identity solution.

如需 Azure AD 混合式身分識別、設定和同步處理的詳細資訊,請參閱:For more information on Azure AD hybrid identities, setup, and synchronization, see:

建立並填入 Azure AD 實例Create and populate an Azure AD instance

建立 Azure AD 實例,並在其中填入使用者和群組。Create an Azure AD instance and populate it with users and groups. Azure AD 可以是初始 Azure AD 受控網域。Azure AD can be the initial Azure AD managed domain. Azure AD 也可以是與 Azure AD 同盟的內部部署 Active Directory 網域服務。Azure AD can also be an on-premises Active Directory Domain Services that is federated with the Azure AD.

如需詳細資訊,請參閱整合內部部署身分識別與 Azure Active Directory將您自己的網域名稱新增至 Azure ADMicrosoft Azure 現在支援與 Windows Server Active Directory 同盟管理您的 Azure AD 目錄使用 Windows PowerShell 管理 Azure AD混合式身分識別所需的連接埠和通訊協定For more information, see Integrating your on-premises identities with Azure Active Directory, Add your own domain name to Azure AD, Microsoft Azure now supports federation with Windows Server Active Directory, Administering your Azure AD directory, Manage Azure AD using Windows PowerShell, and Hybrid Identity Required Ports and Protocols.

將 Azure 訂用帳戶關聯或新增至 Azure Active DirectoryAssociate or add an Azure subscription to Azure Active Directory

  1. 讓目錄成為裝載資料庫之 Azure 訂用帳戶信任的目錄,以將 Azure 訂用帳戶與 Azure Active Directory 建立關聯。Associate your Azure subscription to Azure Active Directory by making the directory a trusted directory for the Azure subscription hosting the database. 如需詳細資訊,請參閱 將 Azure 訂用帳戶關聯或新增至您的 Azure Active Directory 租使用者For details, see Associate or add an Azure subscription to your Azure Active Directory tenant.

  2. 使用 Azure 入口網站中的目錄切換器,切換至與網域建立關聯的訂用帳戶。Use the directory switcher in the Azure portal to switch to the subscription associated with domain.

    重要

    每個 Azure 訂用帳戶都會與 Azure AD 執行個體有信任關係。Every Azure subscription has a trust relationship with an Azure AD instance. 這表示它信任該目錄來驗證使用者、服務和裝置。This means that it trusts that directory to authenticate users, services, and devices. 多個訂用帳戶可以信任相同的目錄,但是一個訂用帳戶只能信任一個目錄。Multiple subscriptions can trust the same directory, but a subscription trusts only one directory. 這個訂用帳戶與目錄之間存在的信任關係不同於訂用帳戶與所有其他 Azure 資源 (網站、資料庫等) 之間的關係,後者比較像是訂用帳戶的子資源。This trust relationship that a subscription has with a directory is unlike the relationship that a subscription has with all other resources in Azure (websites, databases, and so on), which are more like child resources of a subscription. 如果訂用帳戶已過期,則也會停止存取與該訂用帳戶相關聯的其他資源。If a subscription expires, then access to those other resources associated with the subscription also stops. 但目錄會保留在 Azure 中,而且您可以將其他訂用帳戶與該目錄產生關聯,並繼續管理目錄使用者。But the directory remains in Azure, and you can associate another subscription with that directory and continue to manage the directory users. 如需有關資源的詳細資訊,請參閱 了解 Azure 中的資源存取For more information about resources, see Understanding resource access in Azure. 若要深入了解此信任關聯性,請參閱如何將 Azure 訂用帳戶關聯或新增至 Azure Active DirectoryTo learn more about this trusted relationship see How to associate or add an Azure subscription to Azure Active Directory.

Azure AD 系統管理員 SQL Database 的伺服器Azure AD admin with a server in SQL Database

Azure (中裝載 SQL Database 或 Azure Synapse) 的每一部 伺服器 都是以整個伺服器系統管理員的單一伺服器系統管理員帳戶啟動。Each server in Azure (which hosts SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the entire server. 建立第二個系統管理員帳戶做為 Azure AD 帳戶。Create a second administrator account as an Azure AD account. 此主體會建立為伺服器之 master 資料庫中的自主資料庫使用者。This principal is created as a contained database user in the master database of the server. 系統管理員帳戶是每個使用者資料庫中 db_owner 角色的成員,並輸入每個使用者資料庫做為 dbo 使用者。Administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user. 如需有關系統管理員帳戶的詳細資訊,請參閱 管理資料庫和登入。For more information about administrator accounts, see Managing Databases and Logins.

將 Azure Active Directory 與異地複寫搭配使用時,必須為主要和次要伺服器設定 Azure Active Directory 系統管理員。When using Azure Active Directory with geo-replication, the Azure Active Directory administrator must be configured for both the primary and the secondary servers. 如果伺服器沒有 Azure Active Directory 系統管理員,則 Azure Active Directory 登入和使用者會收到「 Cannot connect 到伺服器」錯誤。If a server does not have an Azure Active Directory administrator, then Azure Active Directory logins and users receive a Cannot connect to server error.

注意

不是以 Azure AD 帳戶為基礎的使用者 (包括伺服器系統管理員帳戶) 無法建立以 Azure AD 為基礎的使用者,因為他們沒有許可權可驗證具有 Azure AD 的建議資料庫使用者。Users that are not based on an Azure AD account (including the server administrator account) cannot create Azure AD-based users, because they do not have permission to validate proposed database users with the Azure AD.

布建 Azure AD admin (SQL 受控執行個體) Provision Azure AD admin (SQL Managed Instance)

重要

如果您要布建 Azure SQL 受控執行個體,請只遵循下列步驟。Only follow these steps if you are provisioning an Azure SQL Managed Instance. 這項作業只能由全域/公司系統管理員或 Azure AD 中的特殊許可權角色管理員執行。This operation can only be executed by Global/Company administrator or a Privileged Role Administrator in Azure AD.

公開預覽 中,您可以將 目錄讀取 者角色指派給 Azure AD 中的群組。In public preview, you can assign the Directory Readers role to a group in Azure AD. 然後,群組擁有者可以將受控實例身分識別新增為此群組的成員,這可讓您為 SQL 受控執行個體布建 Azure AD 系統管理員。The group owners can then add the managed instance identity as a member of this group, which would allow you to provision an Azure AD admin for the SQL Managed Instance. 如需這項功能的詳細資訊,請參閱 Azure Active Directory 中適用於 Azure SQL 的 Directory 讀者角色For more information on this feature, see Directory Readers role in Azure Active Directory for Azure SQL.

您的 SQL 受控執行個體需要讀取 Azure AD 的許可權,才能順利完成工作,例如透過安全性群組成員資格或建立新使用者來驗證使用者。Your SQL Managed Instance needs permissions to read Azure AD to successfully accomplish tasks such as authentication of users through security group membership or creation of new users. 若要這樣做,您必須授與 SQL 受控執行個體許可權,以讀取 Azure AD。For this to work, you need to grant the SQL Managed Instance permission to read Azure AD. 您可以使用 Azure 入口網站或 PowerShell 來進行此作業。You can do this using the Azure portal or PowerShell.

Azure 入口網站Azure portal

若要授與您的 SQL 受控執行個體 Azure AD 使用 Azure 入口網站的 [讀取] 許可權,請以 Azure AD 的全域/公司系統管理員身分登入,並遵循下列步驟:To grant your SQL Managed Instance Azure AD read permission using the Azure portal, log in as Global/Company administrator in Azure AD and follow these steps:

  1. Azure 入口網站的右上角,從可能的 Active directory 下拉式清單中選取您的連接。In the Azure portal, in the upper-right corner, select your connection from a drop-down list of possible Active Directories.

  2. 選擇正確的 Active Directory 做為預設 Azure AD。Choose the correct Active Directory as the default Azure AD.

    此步驟會將與 Active Directory 相關聯的訂用帳戶連結至 SQL 受控執行個體,確定 Azure AD 實例和 SQL 受控執行個體都使用相同的訂用帳戶。This step links the subscription associated with Active Directory to the SQL Managed Instance, making sure that the same subscription is used for both the Azure AD instance and the SQL Managed Instance.

  3. 流覽至您想要用於 Azure AD 整合的 SQL 受控執行個體。Navigate to the SQL Managed Instance you want to use for Azure AD integration.

    Azure 入口網站的螢幕擷取畫面,其中顯示所選 SQL 受控實例的 Active Directory 系統管理頁面開啟。

  4. 選取 [Active Directory 管理員] 頁面頂端的橫幅,並對目前的使用者授與權限。Select the banner on top of the Active Directory admin page and grant permission to the current user.

    對話方塊的螢幕擷取畫面,可授與許可權給 SQL 受控實例以存取 Active Directory。

  5. 作業成功之後,會在右上角顯示下列通知:After the operation succeeds, the following notification will show up in the top-right corner:

    確認已成功更新受控實例的 active directory 讀取權限的通知螢幕擷取畫面。

  6. 現在您可以選擇您的 SQL 受控執行個體 Azure AD 系統管理員。Now you can choose your Azure AD admin for your SQL Managed Instance. 若要這麼做,請在 [Active Directory 系統管理員] 頁面上,選取 [設定系統管理員] 命令。For that, on the Active Directory admin page, select Set admin command.

    螢幕擷取畫面,顯示在所選 SQL 受控實例的 Active Directory 系統管理頁面上,反白顯示的 [設定管理] 命令。

  7. 在 [Azure AD 管理] 頁面上,搜尋使用者,選取要成為系統管理員的使用者或群組,然後選取 [ 選取]。On the Azure AD admin page, search for a user, select the user or group to be an administrator, and then select Select.

    [Active Directory 系統管理員] 頁面會顯示您 Active Directory 的所有成員和群組。The Active Directory admin page shows all members and groups of your Active Directory. 呈現灰色的使用者或群組無法選取,因為他們不受支援成為 Azure AD 系統管理員。Users or groups that are grayed out can't be selected because they aren't supported as Azure AD administrators. 請參閱 Azure AD 功能和限制 中支援的系統管理員清單。See the list of supported admins in Azure AD Features and Limitations. Azure 角色型存取控制 (Azure RBAC) 只適用于 Azure 入口網站,不會傳播至 SQL Database、SQL 受控執行個體或 Azure Synapse。Azure role-based access control (Azure RBAC) applies only to the Azure portal and isn't propagated to SQL Database, SQL Managed Instance, or Azure Synapse.

    新增 Azure Active Directory 管理員

  8. 在 Active Directory 管理] 頁面的頂端,選取 [ 儲存]。At the top of the Active Directory admin page, select Save.

    Active Directory 系統管理員頁面的螢幕擷取畫面,其中的 [設定管理員] 和 [移除系統管理員] 按鈕旁邊的頂端列中有 [儲存] 按鈕。

    變更系統管理員的程序可能需要幾分鐘的時間。The process of changing the administrator may take several minutes. 接著,新的系統管理員就會出現在 [Active Directory 系統管理員] 方塊中。Then the new administrator appears in the Active Directory admin box.

為您的 SQL 受控執行個體布建 Azure AD 管理員之後,您就可以開始使用 CREATE LOGIN 語法) (登入建立 Azure AD 伺服器主體。After provisioning an Azure AD admin for your SQL Managed Instance, you can begin to create Azure AD server principals (logins) with the CREATE LOGIN syntax. 如需詳細資訊,請參閱 SQL 受控執行個體總覽For more information, see SQL Managed Instance overview.

提示

若稍後要移除系統管理員,請在 [Active Directory 系統管理員] 頁面頂端,選取 [移除系統管理員],然後選取 [儲存]。To later remove an Admin, at the top of the Active Directory admin page, select Remove admin, and then select Save.

PowerShellPowerShell

若要授與您的 SQL 受控執行個體 Azure AD 使用 PowerShell 的讀取權限,請執行下列腳本:To grant your SQL Managed Instance Azure AD read permission by using the PowerShell, run this script:

# Gives Azure Active Directory read permission to a Service Principal representing the SQL Managed Instance.
# Can be executed only by a "Company Administrator", "Global Administrator", or "Privileged Role Administrator" type of user.

$aadTenant = "<YourTenantId>" # Enter your tenant ID
$managedInstanceName = "MyManagedInstance"

# Get Azure AD role "Directory Users" and create if it doesn't exist
$roleName = "Directory Readers"
$role = Get-AzureADDirectoryRole | Where-Object {$_.displayName -eq $roleName}
if ($role -eq $null) {
    # Instantiate an instance of the role template
    $roleTemplate = Get-AzureADDirectoryRoleTemplate | Where-Object {$_.displayName -eq $roleName}
    Enable-AzureADDirectoryRole -RoleTemplateId $roleTemplate.ObjectId
    $role = Get-AzureADDirectoryRole | Where-Object {$_.displayName -eq $roleName}
}

# Get service principal for your SQL Managed Instance
$roleMember = Get-AzureADServicePrincipal -SearchString $managedInstanceName
$roleMember.Count
if ($roleMember -eq $null) {
    Write-Output "Error: No Service Principals with name '$    ($managedInstanceName)', make sure that managedInstanceName parameter was     entered correctly."
    exit
}
if (-not ($roleMember.Count -eq 1)) {
    Write-Output "Error: More than one service principal with name pattern '$    ($managedInstanceName)'"
    Write-Output "Dumping selected service principals...."
    $roleMember
    exit
}

# Check if service principal is already member of readers role
$allDirReaders = Get-AzureADDirectoryRoleMember -ObjectId $role.ObjectId
$selDirReader = $allDirReaders | where{$_.ObjectId -match     $roleMember.ObjectId}

if ($selDirReader -eq $null) {
    # Add principal to readers role
    Write-Output "Adding service principal '$($managedInstanceName)' to     'Directory Readers' role'..."
    Add-AzureADDirectoryRoleMember -ObjectId $role.ObjectId -RefObjectId     $roleMember.ObjectId
    Write-Output "'$($managedInstanceName)' service principal added to     'Directory Readers' role'..."

    #Write-Output "Dumping service principal '$($managedInstanceName)':"
    #$allDirReaders = Get-AzureADDirectoryRoleMember -ObjectId $role.ObjectId
    #$allDirReaders | where{$_.ObjectId -match $roleMember.ObjectId}
}
else {
    Write-Output "Service principal '$($managedInstanceName)' is already     member of 'Directory Readers' role'."
}

適用于 SQL 受控執行個體的 PowerShellPowerShell for SQL Managed Instance

若要執行 PowerShell Cmdlet,Azure PowerShell 必須已安裝且正在執行中。To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. 如需詳細資訊,請參閱 如何安裝和設定 Azure PowerShellFor detailed information, see How to install and configure Azure PowerShell.

重要

Azure SQL 受控執行個體仍支援 PowerShell Azure Resource Manager (RM) 模組,但未來所有的開發都是針對 Az. Sql 模組。The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Managed Instance, but all future development is for the Az.Sql module. AzureRM 模組在至少 2020 年 12 月之前都還會持續收到 Bug 修正。The AzureRM module will continue to receive bug fixes until at least December 2020. Az 模組和 AzureRm 模組中命令的引數本質上完全相同。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 如需其相容性的詳細資訊,請參閱新的 Azure PowerShell Az 模組簡介For more about their compatibility, see Introducing the new Azure PowerShell Az module.

若要佈建 Azure AD 系統管理員,請執行下列 Azure PowerShell 命令:To provision an Azure AD admin, execute the following Azure PowerShell commands:

  • Connect-AzAccountConnect-AzAccount
  • Select-AzSubscriptionSelect-AzSubscription

下表列出用來布建和管理 SQL 受控執行個體 Azure AD 系統管理員的 Cmdlet:The cmdlets used to provision and manage Azure AD admin for your SQL Managed Instance are listed in the following table:

Cmdlet 名稱Cmdlet name 描述Description
設定-AzSqlInstanceActiveDirectoryAdministratorSet-AzSqlInstanceActiveDirectoryAdministrator 為目前訂用帳戶中的 SQL 受控執行個體布建 Azure AD 系統管理員。Provisions an Azure AD administrator for the SQL Managed Instance in the current subscription. (必須來自目前的訂用帳戶) (Must be from the current subscription)
移除-AzSqlInstanceActiveDirectoryAdministratorRemove-AzSqlInstanceActiveDirectoryAdministrator 移除目前訂用帳戶中 SQL 受控執行個體的 Azure AD 系統管理員。Removes an Azure AD administrator for the SQL Managed Instance in the current subscription.
AzSqlInstanceActiveDirectoryAdministratorGet-AzSqlInstanceActiveDirectoryAdministrator 傳回目前訂用帳戶中 SQL 受控執行個體 Azure AD 系統管理員的相關資訊。Returns information about an Azure AD administrator for the SQL Managed Instance in the current subscription.

下列命令會取得名為 ManagedInstance01 之 SQL 受控執行個體的 Azure AD 系統管理員相關資訊,該資源群組與名為 ResourceGroup01 的資源群組相關聯。The following command gets information about an Azure AD administrator for a SQL Managed Instance named ManagedInstance01 that is associated with a resource group named ResourceGroup01.

Get-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01"

下列命令會針對名為 ManagedInstance01 的 SQL 受控執行個體,布建名為 Dba 的 Azure AD 系統管理員群組。The following command provisions an Azure AD administrator group named DBAs for the SQL Managed Instance named ManagedInstance01. 此伺服器與資源群組 ResourceGroup01 相關聯。This server is associated with resource group ResourceGroup01.

Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01" -DisplayName "DBAs" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353b"

下列命令會移除與資源群組 ResourceGroup01 關聯之 SQL 受控執行個體名為 ManagedInstanceName01 的 Azure AD 系統管理員。The following command removes the Azure AD administrator for the SQL Managed Instance named ManagedInstanceName01 associated with the resource group ResourceGroup01.

Remove-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstanceName01" -Confirm -PassThru

布建 Azure AD 系統管理員 (SQL Database) Provision Azure AD admin (SQL Database)

重要

如果您要為 SQL Database 或 Azure Synapse 布建 伺服器 ,請僅遵循下列步驟。Only follow these steps if you are provisioning a server for SQL Database or Azure Synapse.

下列兩個程式示範如何在 Azure 入口網站和使用 PowerShell 為您的伺服器布建 Azure Active Directory 系統管理員。The following two procedures show you how to provision an Azure Active Directory administrator for your server in the Azure portal and by using PowerShell.

Azure 入口網站Azure portal

  1. Azure 入口網站的右上角,選取您的連線以顯示可能的 Active Directory 下拉式清單。In the Azure portal, in the upper-right corner, select your connection to drop down a list of possible Active Directories. 選擇正確的 Active Directory 做為預設 Azure AD。Choose the correct Active Directory as the default Azure AD. 此步驟會將與訂用帳戶相關聯的 Active Directory 與伺服器連結,以確定 Azure AD 和伺服器都使用相同的訂用帳戶。This step links the subscription-associated Active Directory with server making sure that the same subscription is used for both Azure AD and the server.

  2. 搜尋並選取 [SQL server]。Search for and select SQL server.

    搜尋並選取 SQL server

    注意

    在此頁面上,於選取 [SQL Server] 之前,您可以選取名稱旁的 星星 將該類別設為「我的最愛」,並將 [SQL Server] 新增至左側的導覽列。On this page, before you select SQL servers, you can select the star next to the name to favorite the category and add SQL servers to the left navigation bar.

  3. 在 [ SQL Server ] 頁面上,選取 [ Active Directory 系統管理員]。On the SQL Server page, select Active Directory admin.

  4. 在 [Active Directory 系統管理員] 頁面中,選取 [設定系統管理員]。In the Active Directory admin page, select Set admin.

    設定 Active Directory 管理員的 SQL server

  5. 在 [ 新增系統管理員 ] 頁面中,搜尋使用者,選取要成為系統管理員的使用者或群組,然後選取 [ 選取]。In the Add admin page, search for a user, select the user or group to be an administrator, and then select Select. [Active Directory 系統管理員] 頁面會顯示您 Active Directory 的所有成員和群組。(The Active Directory admin page shows all members and groups of your Active Directory. 呈現灰色的使用者或群組無法選取,因為他們不受支援成為 Azure AD 系統管理員。Users or groups that are grayed out cannot be selected because they are not supported as Azure AD administrators. (在 SQL Database 或 Azure Synapse 使用 Azure Active Directory 驗證驗證的 Azure AD 功能與限制] 區段中,查看支援的系統管理員清單。 ) azure RBAC (僅適用于入口網站,不會傳播至) 。(See the list of supported admins in the Azure AD Features and Limitations section of Use Azure Active Directory Authentication for authentication with SQL Database or Azure Synapse.) Azure role-based access control (Azure RBAC) applies only to the portal and is not propagated to SQL Server.

    選取 Azure Active Directory 管理員

  6. Active Directory 管理 ] 頁面的頂端,選取 [ 儲存]。At the top of the Active Directory admin page, select SAVE.

    儲存系統管理員

變更系統管理員的程序可能需要幾分鐘的時間。The process of changing the administrator may take several minutes. 接著,新的系統管理員就會出現在 [Active Directory 系統管理員] 方塊中。Then the new administrator appears in the Active Directory admin box.

注意

設定 Azure AD 管理員時,新的系統管理員名稱 (使用者或群組) 不能以伺服器驗證使用者的身分存在於虛擬 master 資料庫中。When setting up the Azure AD admin, the new admin name (user or group) cannot already be present in the virtual master database as a server authentication user. 如果存在,Azure AD 系統管理員設定將會失敗;其中會復原其建立並指出這樣的系統管理員 (名稱) 已經存在。If present, the Azure AD admin setup will fail; rolling back its creation and indicating that such an admin (name) already exists. 由於這類伺服器驗證使用者不是 Azure AD 的一部分,因此使用 Azure AD 驗證連接到伺服器的工作會失敗。Since such a server authentication user is not part of the Azure AD, any effort to connect to the server using Azure AD authentication fails.

若稍後要移除系統管理員,請在 [Active Directory 系統管理員] 頁面頂端,選取 [移除系統管理員],然後選取 [儲存]。To later remove an Admin, at the top of the Active Directory admin page, select Remove admin, and then select Save.

適用于 SQL Database 和 Azure Synapse 的 PowerShellPowerShell for SQL Database and Azure Synapse

若要執行 PowerShell Cmdlet,Azure PowerShell 必須已安裝且正在執行中。To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. 如需詳細資訊,請參閱 如何安裝和設定 Azure PowerShellFor detailed information, see How to install and configure Azure PowerShell. 若要佈建 Azure AD 系統管理員,請執行下列 Azure PowerShell 命令:To provision an Azure AD admin, execute the following Azure PowerShell commands:

  • Connect-AzAccountConnect-AzAccount
  • Select-AzSubscriptionSelect-AzSubscription

用來布建和管理 SQL Database 和 Azure Synapse Azure AD 管理員的 Cmdlet:Cmdlets used to provision and manage Azure AD admin for SQL Database and Azure Synapse:

Cmdlet 名稱Cmdlet name 描述Description
Set-AzSqlServerActiveDirectoryAdministratorSet-AzSqlServerActiveDirectoryAdministrator 為裝載 SQL Database 或 Azure Synapse 的伺服器布建 Azure Active Directory 系統管理員。Provisions an Azure Active Directory administrator for the server hosting SQL Database or Azure Synapse. (必須來自目前的訂用帳戶) (Must be from the current subscription)
移除-AzSqlServerActiveDirectoryAdministratorRemove-AzSqlServerActiveDirectoryAdministrator 移除裝載 SQL Database 或 Azure Synapse 之伺服器的 Azure Active Directory 系統管理員。Removes an Azure Active Directory administrator for the server hosting SQL Database or Azure Synapse.
AzSqlServerActiveDirectoryAdministratorGet-AzSqlServerActiveDirectoryAdministrator 傳回目前為裝載 SQL Database 或 Azure Synapse 的伺服器所設定 Azure Active Directory 系統管理員的相關資訊。Returns information about an Azure Active Directory administrator currently configured for the server hosting SQL Database or Azure Synapse.

使用 PowerShell 命令 get-help 來查看每個命令的詳細資訊。Use PowerShell command get-help to see more information for each of these commands. 例如: get-help Set-AzSqlServerActiveDirectoryAdministratorFor example, get-help Set-AzSqlServerActiveDirectoryAdministrator.

下列指令碼會在名為 Group-23 的資源群組中,為 demo_server 伺服器佈建名為 DBA_Group (物件識別碼 40b79501-b343-44ed-9ce7-da4c8cc7353f) 的 Azure AD 系統管理員群組:The following script provisions an Azure AD administrator group named DBA_Group (object ID 40b79501-b343-44ed-9ce7-da4c8cc7353f) for the demo_server server in a resource group named Group-23:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" -DisplayName "DBA_Group"

DisplayName 輸入參數可接受 Azure AD 顯示名稱或「使用者主體名稱」。The DisplayName input parameter accepts either the Azure AD display name or the User Principal Name. 例如,DisplayName="John Smith"DisplayName="johns@contoso.com"For example, DisplayName="John Smith" and DisplayName="johns@contoso.com". Azure AD 群組只支援 Azure AD 顯示名稱。For Azure AD groups only the Azure AD display name is supported.

注意

Azure PowerShell 命令 Set-AzSqlServerActiveDirectoryAdministrator 不會阻止您為不支援的使用者佈建 Azure AD 系統管理員。The Azure PowerShell command Set-AzSqlServerActiveDirectoryAdministrator does not prevent you from provisioning Azure AD admins for unsupported users. 您可以佈建不支援的使用者,但是該使用者無法連線到資料庫。An unsupported user can be provisioned, but can not connect to a database.

下列範例使用選用的 ObjectIDThe following example uses the optional ObjectID:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" `
    -DisplayName "DBA_Group" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353f"

注意

DisplayName 並非唯一時,就需要 Azure AD ObjectIDThe Azure AD ObjectID is required when the DisplayName is not unique. 若要擷取 ObjectIDDisplayName 的值,請使用 Azure 傳統入口網站的 [Active Directory] 區段,然後檢視使用者或群組的屬性。To retrieve the ObjectID and DisplayName values, use the Active Directory section of Azure Classic Portal, and view the properties of a user or group.

下列範例會傳回伺服器目前 Azure AD 管理員的相關資訊:The following example returns information about the current Azure AD admin for the server:

Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" | Format-List

下列範例會移除 Azure AD 系統管理員:The following example removes an Azure AD administrator:

Remove-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server"

注意

您也可以使用 REST API 來佈建 Azure Active Directory 系統管理員。You can also provision an Azure Active Directory Administrator by using the REST APIs. 如需詳細資訊,請參閱 Azure SQL Database 之 Azure SQL Database 作業的 Service Management REST API 參考和作業For more information, see Service Management REST API Reference and Operations for Azure SQL Database Operations for Azure SQL Database

設定用戶端電腦Configure your client computers

在所有用戶端電腦上,您的應用程式或使用者使用 Azure AD 身分識別連線至 SQL Database 或 Azure Synapse,您必須安裝下列軟體:On all client machines, from which your applications or users connect to SQL Database or Azure Synapse using Azure AD identities, you must install the following software:

您可以符合這些需求,方法如下︰You can meet these requirements by:

建立對應至 Azure AD 身分識別的包含使用者Create contained users mapped to Azure AD identities

因為 SQL 受控執行個體支援 Azure AD 伺服器主體 (登入) ,所以不需要使用自主資料庫使用者。Because SQL Managed Instance supports Azure AD server principals (logins), using contained database users is not required. Azure AD 伺服器主體 (登入) 可讓您從 Azure AD 使用者、群組或應用程式建立登入。Azure AD server principals (logins) enable you to create logins from Azure AD users, groups, or applications. 這表示您可以使用 Azure AD 伺服器登入,而不是自主資料庫使用者,向 SQL 受控執行個體進行驗證。This means that you can authenticate with your SQL Managed Instance by using the Azure AD server login rather than a contained database user. 如需詳細資訊,請參閱 SQL 受控執行個體總覽For more information, see SQL Managed Instance overview. 如需建立 Azure AD 伺服器主體 (登入) 的語法,請參閱 CREATE LOGINFor syntax on creating Azure AD server principals (logins), see CREATE LOGIN.

不過,使用 Azure Active Directory authentication 搭配 SQL Database 和 Azure Synapse 需要使用以 Azure AD 身分識別為基礎的自主資料庫使用者。However, using Azure Active Directory authentication with SQL Database and Azure Synapse requires using contained database users based on an Azure AD identity. 自主資料庫使用者沒有 master 資料庫的登入,並且對應到與資料庫相關聯之 Azure AD 中的身分識別。A contained database user does not have a login in the master database, and maps to an identity in Azure AD that is associated with the database. Azure AD 身分識別可以是個別的使用者帳戶或群組。The Azure AD identity can be either an individual user account or a group. 如需有關自主資料庫使用者的詳細資訊,請參閱 自主資料庫使用者 - 使資料庫可攜For more information about contained database users, see Contained Database Users- Making Your Database Portable.

注意

您無法使用 Azure 入口網站建立資料庫使用者 (系統管理員除外)。Database users (with the exception of administrators) cannot be created using the Azure portal. Azure 角色不會傳播至 SQL Database、SQL 受控執行個體或 Azure Synapse 中的資料庫。Azure roles are not propagated to the database in SQL Database, the SQL Managed Instance, or Azure Synapse. Azure 角色可用來管理 Azure 資源,且不會套用至資料庫許可權。Azure roles are used for managing Azure Resources, and do not apply to database permissions. 例如, SQL Server 參與者 角色並不會授與在 SQL DATABASE、SQL 受控執行個體或 Azure Synapse 中連接至資料庫的存取權。For example, the SQL Server Contributor role does not grant access to connect to the database in SQL Database, the SQL Managed Instance, or Azure Synapse. 存取權限必須使用 Transact-SQL 陳述式直接在資料庫中授與。The access permission must be granted directly in the database using Transact-SQL statements.

警告

: & 在 t-sql 和語句中包含做為使用者名稱時 CREATE LOGINCREATE USER 不支援特殊字元,例如冒號或連字號。Special characters like colon : or ampersand & when included as user names in the T-SQL CREATE LOGIN and CREATE USER statements are not supported.

若要建立以 Azure AD 為基礎的自主資料庫使用者 (而非擁有資料庫的伺服器系統管理員),請以至少具有 ALTER ANY USER 權限的使用者身分,使用 Azure AD 身分識別來連線到資料庫。To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. 然後使用下列的 Transact-SQL 語法:Then use the following Transact-SQL syntax:

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

Azure_AD_principal_name 可以是 Azure AD 使用者的使用者主體名稱或 Azure AD 群組的顯示名稱。Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.

範例: 建立代表 Azure AD 同盟或受控網域使用者的自主資料庫使用者:Examples: To create a contained database user representing an Azure AD federated or managed domain user:

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;

若要建立代表 Azure AD 或同盟網域群組的自主資料庫使用者,請輸入安全性群組的顯示名稱:To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:

CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;

若要建立代表使用 Azure AD 權杖進行連線之應用程式的自主資料庫使用者︰To create a contained database user representing an application that connects using an Azure AD token:

CREATE USER [appName] FROM EXTERNAL PROVIDER;

注意

此命令需要代表登入的使用者 (「外部提供 ) 者」 Azure AD 的 SQL 存取權。This command requires that SQL access Azure AD (the "external provider") on behalf of the logged-in user. 有時候,會導致 Azure AD 將例外狀況傳回給 SQL 的情況發生。Sometimes, circumstances will arise that cause Azure AD to return an exception back to SQL. 在這些情況下,使用者會看到 SQL 錯誤33134,其中應該包含 Azure AD 特定的錯誤訊息。In these cases, the user will see SQL error 33134, which should contain the Azure AD-specific error message. 大部分的情況下,此錯誤會指出拒絕存取,或使用者必須註冊 MFA 以存取資源,或必須透過 preauthorization 處理第一方應用程式之間的存取。Most of the time, the error will say that access is denied, or that the user must enroll in MFA to access the resource, or that access between first-party applications must be handled via preauthorization. 在前兩個案例中,問題通常是由使用者的 Azure AD 租使用者中設定的條件式存取原則所造成:它們會防止使用者存取外部提供者。In the first two cases, the issue is usually caused by Conditional Access policies that are set in the user's Azure AD tenant: they prevent the user from accessing the external provider. 更新 CA 原則以允許存取應用程式 ' 00000002-0000-0000-c000-000000000000 ' (Azure AD 圖形 API) 的應用程式識別碼應可解決此問題。Updating the CA policies to allow access to the application '00000002-0000-0000-c000-000000000000' (the application ID of the Azure AD Graph API) should resolve the issue. 如果錯誤指出必須透過 preauthorization 處理第一方應用程式之間的存取,問題是因為使用者以服務主體的形式登入。In the case that the error says access between first-party applications must be handled via preauthorization, the issue is because the user is signed in as a service principal. 如果使用者改為執行命令,則應該會成功。The command should succeed if it is executed by a user instead.

提示

您無法從 Azure Active Directory 直接建立使用者,除了與您的 Azure 訂用帳戶相關聯的 Azure Active Directory 以外。You cannot directly create a user from an Azure Active Directory other than the Azure Active Directory that is associated with your Azure subscription. 不過,在相關聯 Active Directory (稱為外部使用者) 中匯入之使用者的其他 Active Directory 成員可以新增至租用戶 Active Directory 中的 Active Directory 群組。However, members of other Active Directories that are imported users in the associated Active Directory (known as external users) can be added to an Active Directory group in the tenant Active Directory. 藉由建立該 AD 群組的自主資料庫使用者,來自外部 Active Directory 的使用者可以存取 SQL Database。By creating a contained database user for that AD group, the users from the external Active Directory can gain access to SQL Database.

如需有關根據 Azure Active Directory 身分識別建立自主資料庫使用者的詳細資訊,請參閱 CREATE USER (Transact-SQL)For more information about creating contained database users based on Azure Active Directory identities, see CREATE USER (Transact-SQL).

注意

移除伺服器的 Azure Active Directory 系統管理員可防止任何 Azure AD 的驗證使用者連接到伺服器。Removing the Azure Active Directory administrator for the server prevents any Azure AD authentication user from connecting to the server. 必要時,SQL Database 系統管理員可以手動刪除無法使用的 Azure AD 使用者。If necessary, unusable Azure AD users can be dropped manually by a SQL Database administrator.

注意

如果您收到 連線逾時過期,您可能需要將 TransparentNetworkIPResolution 連接字串的參數設定為 false。If you receive a Connection Timeout Expired, you may need to set the TransparentNetworkIPResolution parameter of the connection string to false. 如需詳細資訊,請參閱 .NET Framework 4.6.1 的連線逾時問題 - TransparentNetworkIPResolutionFor more information, see Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution.

當您建立資料庫使用者時,該使用者會獲得 CONNECT 權限,而可以用 PUBLIC 角色的成員身分連線到該資料庫。When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. 一開始提供給使用者的權限僅限於已授與 PUBLIC 角色的任何權限,或已授與其所屬任何 Azure AD 群組的任何權限。Initially the only permissions available to the user are any permissions granted to the PUBLIC role, or any permissions granted to any Azure AD groups that they are a member of. 一旦您佈建以 Azure AD 為基礎的自主資料庫使用者,您可以使用您授與權限給任何其他類型使用者的相同方式,授與該使用者額外的權限。Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, the same way as you grant permission to any other type of user. 通常會授與權限給資料庫角色並新增使用者至角色。Typically grant permissions to database roles, and add users to roles. 如需詳細資訊,請參閱 Database Engine 權限基本概念For more information, see Database Engine Permission Basics. 如需有關特殊 SQL Database 角色的詳細資訊,請參閱 管理 Azure SQL Database 的資料庫和登入For more information about special SQL Database roles, see Managing Databases and Logins in Azure SQL Database. 以外部使用者身分匯入至管理網域的同盟網域使用者帳戶必須使用受控網域身分識別。A federated domain user account that is imported into a managed domain as an external user, must use the managed domain identity.

注意

Azure AD 使用者會在資料庫中繼資料中標示為類型 E (EXTERNAL_USER),而群組則標示為類型 X (EXTERNAL_GROUPS)。Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and for groups with type X (EXTERNAL_GROUPS). 如需詳細資訊,請參閱 sys.database_principalsFor more information, see sys.database_principals.

使用 SSMS 或 SSDT 連接到資料庫Connect to the database using SSMS or SSDT

若要確認 Azure AD 系統管理員已正確設定,請使用 Azure AD 系統管理員帳戶連接到 master 資料庫。To confirm the Azure AD administrator is properly set up, connect to the master database using the Azure AD administrator account. 若要佈建以 Azure AD 為基礎的自主資料庫使用者 (而非擁有資料庫的伺服器系統管理員),請利用有權存取資料庫的 Azure AD 身分識別連線到資料庫。To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity that has access to the database.

重要

Visual Studio 2015 中的 SQL Server 2016 Management StudioSQL Server Data Tools 提供 Azure Active Directory 驗證支援。Support for Azure Active Directory authentication is available with SQL Server 2016 Management Studio and SQL Server Data Tools in Visual Studio 2015. SSMS 的 2016 年 8 月版本也支援 Active Directory 通用驗證,讓系統管理員能夠使用電話、簡訊、含有 PIN 的智慧卡或行動應用程式通知來要求 Multi-Factor Authentication。The August 2016 release of SSMS also includes support for Active Directory Universal Authentication, which allows administrators to require Multi-Factor Authentication using a phone call, text message, smart cards with pin, or mobile app notification.

使用 Azure AD 身分識別以使用 SSMS 或 SSDT 進行連線Using an Azure AD identity to connect using SSMS or SSDT

下列程式示範如何使用 SQL Server Management Studio 或 SQL Server 資料庫工具,以 Azure AD 的身分識別連接到 SQL Database。The following procedures show you how to connect to SQL Database with an Azure AD identity using SQL Server Management Studio or SQL Server Database Tools.

Active Directory 整合驗證Active Directory integrated authentication

如果您使用來自同盟網域的 Azure Active Directory 認證來登入 Windows,或是已設定無縫單一登入進行傳遞和密碼雜湊驗證的受控網域,請使用此方法。Use this method if you are logged into Windows using your Azure Active Directory credentials from a federated domain, or a managed domain that is configured for seamless single sign-on for pass-through and password hash authentication. 如需詳細資訊,請參閱 Azure Active Directory 無縫單一登入For more information, see Azure Active Directory Seamless Single Sign-On.

  1. 啟動 Management Studio 或 Data Tools,並在 [ 連接到伺服器 ] (或 [ 連接到資料庫引擎) ] 對話方塊的 [ 驗證 ] 方塊中,選取 [ Azure Active Directory 整合]。Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Integrated. 不需要密碼或沒有密碼可輸入,因為現有的認證將會在連接時出現。No password is needed or can be entered because your existing credentials will be presented for the connection.

    選取 AD 整合式驗證

  2. 選取 [選項] 按鈕,然後在 [連線屬性] 頁面的 [連線到資料庫] 方塊中,鍵入您想要連線的使用者資料庫名稱。Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to. 如需詳細資訊,請參閱有關 SSMS 17. x 和6.x 連接屬性之間差異的 多重要素 Azure AD 驗證For more information, see the article Multi-factor Azure AD auth on the differences between the Connection Properties for SSMS 17.x and 18.x.

    選取資料庫名稱

Active Directory 密碼驗證Active Directory password authentication

使用 Azure AD 受控網域連接到 Azure AD 主體名稱時,請使用這個方法。Use this method when connecting with an Azure AD principal name using the Azure AD managed domain. 您也可以在不存取網域的情況下將它用於同盟帳戶,例如在遠端工作時。You can also use it for federated accounts without access to the domain, for example, when working remotely.

您可以使用這個方法,透過 Azure AD 僅限雲端的身分識別使用者,或使用 Azure AD 混合式身分識別的使用者,在 SQL Database 或 SQL 受控執行個體中驗證資料庫。Use this method to authenticate to the database in SQL Database or the SQL Managed Instance with Azure AD cloud-only identity users, or those who use Azure AD hybrid identities. 此方法支援想要使用其 Windows 認證的使用者,但其本機電腦未加入網域 (例如,使用遠端存取) 。This method supports users who want to use their Windows credential, but their local machine is not joined with the domain (for example, using remote access). 在此情況下,Windows 使用者可以指定其網域帳戶和密碼,並且可以在 SQL Database、SQL 受控執行個體或 Azure Synapse 中向資料庫進行驗證。In this case, a Windows user can indicate their domain account and password, and can authenticate to the database in SQL Database, the SQL Managed Instance, or Azure Synapse.

  1. 啟動 Management Studio 或 Data Tools,並在 [ 連接到伺服器 ] (或 [ 連接到資料庫引擎) ] 對話方塊的 [ 驗證 ] 方塊中,選取 [ Azure Active Directory-密碼]。Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Password.

  2. 在 [ 使用者名稱 ] 方塊中,以 username @ domain.com 格式輸入您的 Azure Active Directory 使用者名稱。In the User name box, type your Azure Active Directory user name in the format username@domain.com. 使用者名稱必須是來自 Azure Active Directory 的帳戶,或是來自具有 Azure Active Directory 的受控或同盟網域的帳戶。User names must be an account from Azure Active Directory or an account from a managed or federated domain with Azure Active Directory.

  3. 在 [ 密碼 ] 方塊中,輸入 Azure Active Directory 帳戶或受控/同盟網域帳戶的使用者密碼。In the Password box, type your user password for the Azure Active Directory account or managed/federated domain account.

    選取 AD 密碼驗證

  4. 選取 [選項] 按鈕,然後在 [連線屬性] 頁面的 [連線到資料庫] 方塊中,鍵入您想要連線的使用者資料庫名稱。Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to. (請參閱上一個選項中的圖形。)(See the graphic in the previous option.)

Active Directory 互動式驗證Active Directory interactive authentication

使用此方法進行互動式驗證,不論是否有 Multi-Factor Authentication (MFA) ,並以互動方式要求密碼。Use this method for interactive authentication with or without Multi-Factor Authentication (MFA), with password being requested interactively. 您可以使用此方法來驗證 SQL Database、SQL 受控執行個體和 Azure Synapse 中的資料庫,以 Azure AD 僅限雲端的身分識別使用者,或使用 Azure AD 混合式身分識別的使用者。This method can be used to authenticate to the database in SQL Database, the SQL Managed Instance, and Azure Synapse for Azure AD cloud-only identity users, or those who use Azure AD hybrid identities.

如需詳細資訊,請參閱 使用多重要素 Azure AD 驗證搭配 SQL Database 和 Azure Synapse (適用于 MFA 的 SSMS 支援) For more information, see Using multi-factor Azure AD authentication with SQL Database and Azure Synapse (SSMS support for MFA).

從用戶端應用程式使用 Azure AD 身分識別來連接Using an Azure AD identity to connect from a client application

下列程式示範如何從用戶端應用程式使用 Azure AD 的身分識別連接到 SQL Database。The following procedures show you how to connect to a SQL Database with an Azure AD identity from a client application.

Active Directory 整合驗證Active Directory integrated authentication

若要使用整合式 Windows 驗證,您的網域 Active Directory 必須與 Azure Active Directory 同盟,或必須是針對傳遞或密碼雜湊驗證設定無縫單一登入的受控網域。To use integrated Windows authentication, your domain's Active Directory must be federated with Azure Active Directory, or should be a managed domain that is configured for seamless single sign-on for pass-through or password hash authentication. 如需詳細資訊,請參閱 Azure Active Directory 無縫單一登入For more information, see Azure Active Directory Seamless Single Sign-On.

注意

MSAL.NET (的 Microsoft 身分識別。 整合式 Windows 驗證的用戶端) 不支援傳遞和密碼雜湊驗證的無縫單一登入。MSAL.NET (Microsoft.Identity.Client) for integrated Windows authentication is not supported for seamless single sign-on for pass-through and password hash authentication.

您的用戶端應用程式 (或服務) 連接到資料庫,必須在使用者的網域認證下,于已加入網域的電腦上執行。Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user's domain credentials.

若要使用整合式驗證和 Azure AD 識別來連接到資料庫,則必須將資料庫連接字串中的驗證關鍵字設定為 Active Directory IntegratedTo connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated. 下列 C# 程式碼範例會使用 ADO.NET。The following C# code sample uses ADO .NET.

string ConnectionString = @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

不支援使用連接字串關鍵字 Integrated Security=True 來連接到 Azure SQL Database。The connection string keyword Integrated Security=True is not supported for connecting to Azure SQL Database. 建立 ODBC 連接時,您必須移除空格,並將「驗證」設為 'ActiveDirectoryIntegrated'。When making an ODBC connection, you will need to remove spaces and set Authentication to 'ActiveDirectoryIntegrated'.

Active Directory 密碼驗證Active Directory password authentication

若要使用 Azure AD 僅限雲端的身分識別使用者帳戶,或使用 Azure AD 混合式身分識別的使用者帳戶來連線到資料庫,則必須將 Authentication 關鍵字設定為 Active Directory PasswordTo connect to a database using Azure AD cloud-only identity user accounts, or those who use Azure AD hybrid identities, the Authentication keyword must be set to Active Directory Password. 連接字串必須包含使用者識別碼 (UID) 及密碼 (PWD) 關鍵字和值。The connection string must contain User ID/UID and Password/PWD keywords and values. 下列 C# 程式碼範例會使用 ADO.NET。The following C# code sample uses ADO .NET.

string ConnectionString =
@"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; Initial Catalog=testdb;  UID=bob@contoso.onmicrosoft.com; PWD=MyPassWord!";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

深入了解使用 Azure AD 驗證 GitHub 示範上所提供之示範程式碼範例的 Azure AD 驗證方法。Learn more about Azure AD authentication methods using the demo code samples available at Azure AD Authentication GitHub Demo.

Azure AD 權杖Azure AD token

此驗證方法可讓仲介層服務取得 (JWT) 的 JSON Web 權杖 ,藉由從 Azure AD 取得權杖,在 SQL DATABASE、SQL 受控執行個體或 Azure Synapse 中連接到資料庫。This authentication method allows middle-tier services to obtain JSON Web Tokens (JWT) to connect to the database in SQL Database, the SQL Managed Instance, or Azure Synapse by obtaining a token from Azure AD. 這個方法會使用以憑證為基礎的驗證,來啟用各種應用程式案例,包括服務身分識別、服務主體和應用程式。This method enables various application scenarios including service identities, service principals, and applications using certificate-based authentication. 您必須完成四個基本步驟,才能使用 Azure AD 權杖驗證︰You must complete four basic steps to use Azure AD token authentication:

  1. 使用 Azure Active Directory 註冊您的應用程式,並取得程式碼的用戶端識別碼。Register your application with Azure Active Directory and get the client ID for your code.
  2. 建立代表應用程式的資料庫使用者。Create a database user representing the application. (稍早在步驟 6 中已完成)。(Completed earlier in step 6.)
  3. 在執行應用程式的用戶端電腦上建立憑證。Create a certificate on the client computer runs the application.
  4. 將憑證加入應用程式當做索引鍵。Add the certificate as a key for your application.

範例連接字串︰Sample connection string:

string ConnectionString =@"Data Source=n9lxnyuzhv.database.windows.net; Initial Catalog=testdb;"
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your JWT token"
conn.Open();

如需詳細資訊,請參閱 SQL Server 安全性部落格For more information, see SQL Server Security Blog. 如需新增憑證的詳細資訊,請參閱開始在 Azure Active Directory 中使用憑證式驗證For information about adding a certificate, see Get started with certificate-based authentication in Azure Active Directory.

sqlcmdsqlcmd

下列陳述式中使用 sqlcmd 13.1 進行連線,從 下載中心即可取得此版本。The following statements, connect using version 13.1 of sqlcmd, which is available from the Download Center.

注意

sqlcmd 使用 -G 命令無法搭配系統身分識別運作,而且需要使用者主體登入。sqlcmd with the -G command does not work with system identities, and requires a user principal login.

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -G  
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -U bob@contoso.com -P MyAADPassword -G -l 30

針對 Azure AD 驗證進行疑難排解Troubleshoot Azure AD authentication

下列 blog 中可找到疑難排解 Azure AD 驗證問題的指引: https://techcommunity.microsoft.com/t5/azure-sql-database/troubleshooting-problems-related-to-azure-ad-authentication-with/ba-p/1062991Guidance on troubleshooting issues with Azure AD authentication can be found in the following blog: https://techcommunity.microsoft.com/t5/azure-sql-database/troubleshooting-problems-related-to-azure-ad-authentication-with/ba-p/1062991

後續步驟Next steps