使用適用於 SQL 驗證的 Azure Active Directory AuthenticationUse Azure Active Directory Authentication for authentication with SQL

Azure Active Directory 驗證是使用 Azure Active Directory (Azure AD) 中的身分識別來連線到 Azure SQL Database受控執行個體SQL 資料倉儲的機制。Azure Active Directory authentication is a mechanism of connecting to Azure SQL Database, Managed Instance, and SQL Data Warehouse by using identities in Azure Active Directory (Azure AD).

注意

本主題適用於 Azure SQL 伺服器,以及在 Azure SQL Server 上建立的 SQL Database 和 SQL 資料倉儲資料庫。This topic applies to Azure SQL server, and to both SQL Database and SQL Data Warehouse databases that are created on the Azure SQL server. 為了簡單起見,參考 SQL Database 和 SQL 資料倉儲時都會使用 SQL Database。For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.

您可以使用 Azure AD 驗證,在單一中央位置集中管理資料庫使用者和其他 Microsoft 服務的身分識別。With Azure AD authentication, you can centrally manage the identities of database users and other Microsoft services in one central location. 中央識別碼管理提供單一位置以管理資料庫使用者並簡化權限管理。Central ID management provides a single place to manage database users and simplifies permission management. 包括以下優點:Benefits include the following:

  • 它提供 SQL Server 驗證的替代方案。It provides an alternative to SQL Server authentication.
  • 協助停止跨資料庫伺服器使用過多的使用者身分識別。Helps stop the proliferation of user identities across database servers.
  • 允許在單一位置變換密碼。Allows password rotation in a single place.
  • 客戶可以使用外部 (Azure AD) 群組來管理資料庫權限。Customers can manage database permissions using external (Azure AD) groups.
  • 它可以藉由啟用整合式 Windows 驗證和 Azure Active Directory 支援的其他形式驗證來避免儲存密碼。It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
  • Azure AD 驗證會使用自主資料庫使用者,在資料庫層級驗證身分。Azure AD authentication uses contained database users to authenticate identities at the database level.
  • Azure AD 針對連線到 SQL Database 的應用程式支援權杖型驗證。Azure AD supports token-based authentication for applications connecting to SQL Database.
  • Azure AD 驗證本機 Azure Active Directory 的 ADFS (網域同盟) 或原生使用者/密碼驗證,而不需進行網域同步處理。Azure AD authentication supports ADFS (domain federation) or native user/password authentication for a local Azure Active Directory without domain synchronization.
  • Azure AD 支援來自 SQL Server Management Studio 的連線,其中使用包含 Multi-Factor Authentication (MFA) 的 Active Directory 通用驗證。Azure AD supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA). MFA 包含增強式驗證功能,其中提供一系列簡易的驗證選項,例如電話、簡訊、含有 PIN 的智慧卡或行動應用程式通知。MFA includes strong authentication with a range of easy verification options — phone call, text message, smart cards with pin, or mobile app notification. 如需詳細資訊,請參閱 適用於與 SQL Database 和 SQL 資料倉儲搭配使用之 Azure AD MFA 的 SSMS 支援For more information, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.
  • Azure AD 支援從 SQL Server Data Tools (SSDT) 使用 Active Directory 互動式驗證的類似連線。Azure AD supports similar connections from SQL Server Data Tools (SSDT) that use Active Directory Interactive Authentication. 如需詳細資訊,請參閱 SQL Server Data Tools (SSDT) 中的 Azure Active Directory 支援For more information, see Azure Active Directory support in SQL Server Data Tools (SSDT).

注意

使用 Azure Active Directory 帳戶不支援連線到 Azure VM 上執行的 SQL Server。Connecting to SQL Server running on an Azure VM is not supported using an Azure Active Directory account. 請改用 Active Directory 網域帳戶。Use a domain Active Directory account instead.

設定步驟包括以下設定和使用 Azure Active Directory 驗證的程序。The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1. 建立和填入 Azure AD。Create and populate Azure AD.
  2. 選用:和目前與 Azure 訂用帳戶相關聯的 Active Directory 產生關聯並加以變更。Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
  3. 為 Azure SQL Database 伺服器、「受控執行個體」或 Azure SQL 資料倉儲建立 Azure Active Directory 系統管理員。Create an Azure Active Directory administrator for the Azure SQL Database server, the Managed Instance, or the Azure SQL Data Warehouse.
  4. 設定用戶端電腦。Configure your client computers.
  5. 在對應至 Azure AD 身分識別的資料庫中建立自主資料庫使用者。Create contained database users in your database mapped to Azure AD identities.
  6. 使用 Azure AD 身分識別連接到您的資料庫。Connect to your database by using Azure AD identities.

注意

若要了解如何建立和填入 Azure AD,然後搭配 Azure SQL Database、「受控執行個體」及「SQL 資料倉儲」來設定 Azure AD,請參閱使用 Azure SQL Database 設定 Azure ADTo learn how to create and populate Azure AD, and then configure Azure AD with Azure SQL Database, Managed Instance, and SQL Data Warehouse, see Configure Azure AD with Azure SQL Database.

信認架構Trust architecture

下列高階圖表摘要說明搭配使用 Azure AD 驗證與 Azure SQL Database 的解決方案架構。The following high-level diagram summarizes the solution architecture of using Azure AD authentication with Azure SQL Database. 相同的概念適用於 SQL 資料倉儲。The same concepts apply to SQL Data Warehouse. 若要支援 Azure AD 原生使用者密碼,只會考慮雲端部分和 Azure AD/Azure SQL Database。To support Azure AD native user password, only the Cloud portion and Azure AD/Azure SQL Database is considered. 若要支援同盟驗證 (或 Windows 認證的使用者/密碼),需要與 ADFS 區塊進行通訊。To support Federated authentication (or user/password for Windows credentials), the communication with ADFS block is required. 箭頭表示通訊路徑。The arrows indicate communication pathways.

aad 驗證圖表

下圖表示允許用戶端藉由提交權杖連線到資料庫的同盟、信任和主控關聯性。The following diagram indicates the federation, trust, and hosting relationships that allow a client to connect to a database by submitting a token. 此權杖是由 Azure AD 所驗證,並受到資料庫信任。The token is authenticated by an Azure AD, and is trusted by the database. 客戶 1 可以代表具有原生使用者的 Azure AD 或具有同盟使用者的 Azure Active Directory。Customer 1 can represent an Azure Active Directory with native users or an Azure AD with federated users. 客戶 2 代表包含已匯入使用者的可能解決方案;在此範例中,來自同盟 Azure Active Directory 且 ADFS 正與 Azure Active Directory 進行同步處理。Customer 2 represents a possible solution including imported users; in this example coming from a federated Azure Active Directory with ADFS being synchronized with Azure Active Directory. 請務必了解使用 Azure AD 驗證存取資料庫的必要條件是裝載訂用帳戶要與 Azure AD 相關聯。It's important to understand that access to a database using Azure AD authentication requires that the hosting subscription is associated to the Azure AD. 您必須使用相同的訂用帳戶來建立裝載 Azure SQL Database 或 SQL 資料倉儲的 SQL Server。The same subscription must be used to create the SQL Server hosting the Azure SQL Database or SQL Data Warehouse.

訂用帳戶關聯性

系統管理員結構Administrator structure

使用 Azure AD 驗證時,SQL Database 伺服器和「受控執行個體」會有兩個系統管理員帳戶:原始的 SQL Server 系統管理員和 Azure AD 系統管理員。When using Azure AD authentication, there are two Administrator accounts for the SQL Database server and Managed Instance; the original SQL Server administrator and the Azure AD administrator. 相同的概念適用於 SQL 資料倉儲。The same concepts apply to SQL Data Warehouse. 只有以 Azure AD 帳戶為基礎的系統管理員可以在使用者資料庫中建立第一個 Azure AD 自主資料庫使用者。Only the administrator based on an Azure AD account can create the first Azure AD contained database user in a user database. Azure AD 系統管理員登入可以是 Azure AD 使用者或 Azure AD 群組。The Azure AD administrator login can be an Azure AD user or an Azure AD group. 當系統管理員是群組帳戶時,它可以供任何群組成員使用,啟用 SQL Server 執行個體的多個 Azure AD 系統管理員。When the administrator is a group account, it can be used by any group member, enabling multiple Azure AD administrators for the SQL Server instance. 以系統管理員的身分使用群組帳戶,可讓您集中新增和移除 Azure AD 中的群組成員,而不需要變更 SQL Database 中的使用者或權限,藉以增強管理性。Using group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in SQL Database. 一律只能設定一個 Azure AD 系統管理員 (使用者或群組)。Only one Azure AD administrator (a user or group) can be configured at any time.

系統管理員結構

PermissionsPermissions

若要建立新的使用者,您必須具有資料庫中的 ALTER ANY USER 權限。To create new users, you must have the ALTER ANY USER permission in the database. 任何資料庫使用者皆可授與 ALTER ANY USER 權限。The ALTER ANY USER permission can be granted to any database user. ALTER ANY USER 權限的擁有者還包括伺服器系統管理員帳戶、具備資料庫之 CONTROL ON DATABASEALTER ON DATABASE 權限的資料庫使用者,以及 db_owner 資料庫角色的成員。The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.

若要在 Azure SQL Database、「受控執行個體」或「SQL 資料倉儲」中建立自主資料庫使用者,您必須使用 Azure AD 身分識別來連線到資料庫或執行個體。To create a contained database user in Azure SQL Database, Managed Instance, or SQL Data Warehouse, you must connect to the database or instance using an Azure AD identity. 若要建立第一個自主資料庫使用者,您必須使用 Azure AD 系統管理員 (資料庫的擁有者) 連接到資料庫。To create the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). 這在使用 SQL Database 或 SQL 資料倉儲設定和管理 Azure Active Directory 驗證中有示範。This is demonstrated in Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse. 只有針對 Azure SQL Database 或 SQL 資料倉儲伺服器建立 Azure AD 系統管理員後,才可能進行任何 Azure AD 驗證。Any Azure AD authentication is only possible if the Azure AD admin was created for Azure SQL Database or SQL Data Warehouse server. 如果已從伺服器移除 Azure Active Directory 系統管理員,則先前在 SQL Server 內建立的現有 Azure Active Directory 使用者便無法再使用其 Azure Active Directory 認證連線到資料庫。If the Azure Active Directory admin was removed from the server, existing Azure Active Directory users created previously inside SQL Server can no longer connect to the database using their Azure Active Directory credentials.

Azure AD 功能和限制Azure AD features and limitations

  • 下列 Azure AD 的成員可在 Azure SQL 伺服器或 SQL 資料倉儲中佈建:The following members of Azure AD can be provisioned in Azure SQL server or SQL Data Warehouse:

  • 在具有 db_owner 伺服器角色的群組中,Azure AD 使用者無法針對 Azure SQL Database 和 Azure SQL 資料倉儲使用 CREATE DATABASE SCOPED CREDENTIAL 語法。Azure AD users that are part of a group that has db_owner server role cannot use the CREATE DATABASE SCOPED CREDENTIAL syntax against Azure SQL Database and Azure SQL Data Warehouse. 您會看到下列錯誤︰You will see the following error:

    SQL Error [2760] [S0001]: The specified schema name 'user@mydomain.com' either does not exist or you do not have permission to use it.

    直接將 db_owner 角色授與給個別 Azure AD 使用者,以減輕 CREATE DATABASE SCOPED CREDENTIAL 問題。Grant the db_owner role directly to the individual Azure AD user to mitigate the CREATE DATABASE SCOPED CREDENTIAL issue.

  • 下列系統函式在 Azure AD 主體下執行時會傳回 NULL:These system functions return NULL values when executed under Azure AD principals:

    • SUSER_ID()
    • SUSER_NAME(<admin ID>)
    • SUSER_SNAME(<admin SID>)
    • SUSER_ID(<admin name>)
    • SUSER_SID(<admin name>)

受控執行個體Managed Instances

  • Azure AD 伺服器主體 (登入) 和使用者都以受控執行個體預覽功能的形式受到支援。Azure AD server principals (logins) and users are supported as a preview feature for Managed Instances.
  • 設定對應至 Azure AD 群組的 Azure AD 伺服器主體 (登入),因為受控執行個體不支援資料庫擁有者。Setting Azure AD server principals (logins) mapped to an Azure AD group as database owner is not supported in Managed Instances.
    • 其擴充功能是當群組新增為 dbcreator 伺服器角色的一部分時,此群組中的使用者可以連線到受控執行個體並建立新的資料庫,但將無法存取資料庫。An extension of this is that when a group is added as part of the dbcreator server role, users from this group can connect to the Managed Instance and create new databases, but will not be able to access the database. 這是因為新的資料庫擁有者是 SA,而不是 Azure AD 使用者。This is because the new database owner is SA, and not the Azure AD user. 如果將個別使用者新增至 dbcreator 伺服器角色,則不會顯示這個問題。This issue does not manifest if the individual user is added to the dbcreator server role.
  • Azure AD 伺服器主體 (登入) 支援 SQL 代理程式管理和作業執行功能。SQL Agent management and jobs execution is supported for Azure AD server principals (logins).
  • Azure AD 伺服器主體 (登入) 可以執行資料庫備份和還原作業。Database backup and restore operations can be executed by Azure AD server principals (logins).
  • 支援稽核與 Azure AD 伺服器主體 (登入) 和驗證事件相關的所有陳述式。Auditing of all statements related to Azure AD server principals (logins) and authentication events is supported.
  • 屬於 sysadmin 伺服器角色成員的 Azure AD 伺服器主體 (登入) 支援專用管理員連線。Dedicated administrator connection for Azure AD server principals (logins) which are members of sysadmin server role is supported.
    • 透過 SQLCMD 公用程式和 SQL Server Management Studio 支援。Supported through SQLCMD Utility and SQL Server Management Studio.
  • 來自 Azure AD 伺服器主體 (登入) 的登入事件支援登入觸發程序。Logon triggers are supported for logon events coming from Azure AD server principals (logins).
  • Service Broker 和 DB 電子郵件可使用 Azure AD 伺服器主體 (登入) 來設定。Service Broker and DB mail can be setup using an Azure AD server principal (login).

使用 Azure AD 身分識別連接Connecting using Azure AD identities

Azure Active Directory 驗證支援下列方法,使用 Azure AD 身分識別連接至資料庫:Azure Active Directory authentication supports the following methods of connecting to a database using Azure AD identities:

  • 使用整合式 Windows 驗證Using integrated Windows authentication
  • 使用 Azure AD 主體名稱和密碼Using an Azure AD principal name and a password
  • 使用應用程式權杖驗證Using Application token authentication

Azure AD 伺服器主體 (登入) (公開預覽) 支援下列驗證方法:The following authentication methods are supported for Azure AD server principals (logins) (public preview):

  • Azure Active Directory 密碼Azure Active Directory Password
  • Azure Active Directory 整合式Azure Active Directory Integrated
  • 包含 MFA 的 Active Directory 通用驗證Azure Active Directory Universal with MFA
  • Azure Active Directory 互動式Azure Active Directory Interactive

其他考量Additional considerations

  • 若要增強管理性,建議您以系統管理員身分佈建專用的 Azure AD 群組。To enhance manageability, we recommend you provision a dedicated Azure AD group as an administrator.
  • 一個 Azure SQL Database 伺服器或 Azure SQL 資料倉儲一律只能設定一個 Azure AD 系統管理員 (使用者或群組)。Only one Azure AD administrator (a user or group) can be configured for an Azure SQL Database server or Azure SQL Data Warehouse at any time.
    • Azure AD 伺服器主體 (登入) 新增到受控執行個體 (公開預覽) 之後,就能建立可以加入 sysadmin 角色的多個 Azure AD 伺服器主體 (登入)。The addition of Azure AD server principals (logins) for Managed Instances (public preview) allows the possibility of creating multiple Azure AD server principals (logins) that can be added to the sysadmin role.
  • 只有 SQL Server 的 Azure AD 系統管理員可以一開始就使用 Azure Active Directory 帳戶來連線到 Azure SQL Database 伺服器、「受控執行個體」或「Azure SQL 資料倉儲」。Only an Azure AD administrator for SQL Server can initially connect to the Azure SQL Database server, Managed Instance, or Azure SQL Data Warehouse using an Azure Active Directory account. Active Directory 系統管理員可以設定後續的 Azure AD 資料庫使用者。The Active Directory administrator can configure subsequent Azure AD database users.
  • 建議將連接逾時設定為 30 秒。We recommend setting the connection timeout to 30 seconds.
  • SQL Server 2016 Management Studio 和 SQL Server Data Tools for Visual Studio 2015 (版本 14.0.60311.1 (2016 年 4 月) 或更新版本) 支援 Azure Active Directory 驗證。SQL Server 2016 Management Studio and SQL Server Data Tools for Visual Studio 2015 (version 14.0.60311.1April 2016 or later) support Azure Active Directory authentication. ( .NET Framework Data Provider for SqlServer 支援 Azure AD 驗證,最低版本 .NET Framework 4.6)。(Azure AD authentication is supported by the .NET Framework Data Provider for SqlServer; at least version .NET Framework 4.6). 因此,這些工具和資料層應用程式 (DAC 和 .BACPAC) 的最新版本可以使用 Azure AD 驗證。Therefore the newest versions of these tools and data-tier applications (DAC and .BACPAC) can use Azure AD authentication.
  • 從 15.0.1 版起,sqlcmd 公用程式bcp 公用程式都支援搭配 MFA 的 Active Directory 互動式驗證。Beginning with version 15.0.1, sqlcmd utility and bcp utility support Active Directory Interactive authentication with MFA.
  • SQL Server Data Tools for Visual Studio 2015 至少需要 2016 年 4 月版本的 Data Tools (版本 14.0.60311.1)。SQL Server Data Tools for Visual Studio 2015 requires at least the April 2016 version of the Data Tools (version 14.0.60311.1). Azure AD 使用者目前不會顯示在 SSDT 物件總管中。Currently Azure AD users are not shown in SSDT Object Explorer. 解決方法是在 sys.database_principals 中檢視使用者。As a workaround, view the users in sys.database_principals.  
  • Microsoft JDBC Driver 6.0 for SQL Server 支援 Azure AD 驗證。Microsoft JDBC Driver 6.0 for SQL Server supports Azure AD authentication. 此外,請參閱 設定連接屬性Also, see Setting the Connection Properties.
  • PolyBase 無法使用 Azure AD 驗證進行驗證。PolyBase cannot authenticate by using Azure AD authentication.  
  • Azure 入口網站的 [匯入資料庫] 和 [匯出資料庫] 刀鋒視窗支援 SQL Database 的 AD 驗證。Azure AD authentication is supported for SQL Database by the Azure portal Import Database and Export Database blades. PowerShell 命令也支援使用 Azure AD 驗證的匯入和匯出。Import and export using Azure AD authentication is also supported from the PowerShell command.
  • SQL Database、「受控執行個體」及「SQL 資料倉儲」可藉由使用 CLI 來支援 Azure AD 驗證。Azure AD authentication is supported for SQL Database, Managed Instance, and SQL Data Warehouse by use CLI. 如需詳細資訊,請參閱使用 SQL Database 或 SQL 資料倉儲設定和管理 Azure Active Directory 驗證SQL Server - az sql serverFor more information, see Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse and SQL Server - az sql server.

後續步驟Next steps