搭配 ODBC 驅動程式使用 Azure Active DirectoryUsing Azure Active Directory with the ODBC Driver

下載下載 ODBC 驅動程式DownloadDownload ODBC Driver

目的Purpose

Microsoft ODBC Driver for SQL Server 13.1 版或更新版本可讓 ODBC 應用程式使用 Azure Active Directory 中的同盟身分識別,連接到 Azure SQL Database 的實例。The Microsoft ODBC Driver for SQL Server version 13.1 or above allows ODBC applications to connect to an instance of Azure SQL Database using a federated identity in Azure Active Directory. 身分識別可以使用使用者名稱/密碼、Azure Active Directory 存取權杖、Azure Active Directory 受控識別 (17.3 +) ,或 Windows-Integrated Linux/macOS (上) 17.6 + 進行驗證。The identity can use a username/password, an Azure Active Directory access token, an Azure Active Directory managed identity (17.3+), or Windows-Integrated Authentication (17.6+ on Linux/macOS). 針對 ODBC Driver 13.1 版,Azure Active Directory 存取權杖驗證僅限 WindowsFor the ODBC Driver version 13.1, the Azure Active Directory access token authentication is Windows only. ODBC Driver 17 版和更新版本支援跨所有平台 (Windows、Linux 及 macOS) 進行此驗證。The ODBC Driver version 17 and above support this authentication across all platforms (Windows, Linux, and macOS). 具有登入識別碼的新 Azure Active Directory 互動式驗證是在適用於 Windows 的 ODBC Driver 17.1 版中引進的。A new Azure Active Directory interactive authentication with Login ID is introduced in ODBC Driver version 17.1 for Windows. 在 ODBC 驅動程式 17.3.1.1 版中,已針對系統指派與使用者指派的身分識別,新增新的 Azure Active Directory 受控識別驗證方法。A new Azure Active Directory managed identity authentication method was added in ODBC Driver version 17.3.1.1 for both system-assigned and user-assigned identities. 所有這些選項都是使用新的 DSN 和連接字串關鍵字和連接屬性來完成。All of these options are accomplished by using new DSN and connection string keywords, and connection attributes.

注意

Linux 與 macOS 上早於 17.6 版的 ODBC 驅動程式只支援直接針對 Azure Active Directory 進行 Azure Active Directory 驗證。The ODBC Driver on Linux and macOS before version 17.6 only supports Azure Active Directory authentication directly against Azure Active Directory. 如果在 Linux 或 macOS 用戶端使用 Azure Active Directory 使用者名稱/密碼,而您的 Active Directory 組態需要用戶端驗證 Active Directory 同盟服務端點,則驗證可能會失敗。If you are using Azure Active Directory username/password authentication from a Linux or macOS client and your Active Directory configuration requires the client to authenticate against an Active Directory Federation Services endpoint, authentication may fail. 從 17.6 版的驅動程式起,已移除這個限制。As of driver version 17.6, this limitation has been removed.

新的和/或已修改的 DSN 與連接字串關鍵字New and/or Modified DSN and Connection String Keywords

使用 DSN 或連接字串連線以控制驗證模式時,可以使用 Authentication 關鍵字。The Authentication keyword can be used when connecting with a DSN or connection string to control the authentication mode. 連接字串中設定的值會覆寫 DSN 中設定的值 (如已提供)。The value set in the connection string overrides that in the DSN, if provided. Authentication 設定的「前置屬性值」 是從連接字串和 DSN 值計算而來的值。The pre-attribute value of the Authentication setting is the value computed from the connection string and DSN values.

名稱Name Values 預設Default 描述Description
Authentication (未設定) 、 (空字串) 、、、 SqlPassword ActiveDirectoryPassword 、、 ActiveDirectoryIntegrated ActiveDirectoryInteractive ActiveDirectoryMsiActiveDirectoryServicePrincipal(not set), (empty string), SqlPassword, ActiveDirectoryPassword, ActiveDirectoryIntegrated, ActiveDirectoryInteractive, ActiveDirectoryMsi, ActiveDirectoryServicePrincipal (未設定)(not set) 控制驗證模式。Controls the authentication mode.
Value描述Description
(未設定)(not set)由其他關鍵字決定的驗證模式 (現有的舊版連線選項)。Authentication mode determined by other keywords (existing legacy connection options.)
(空字串)(empty string)(僅限連接字串)。覆寫和取消設定在 DSN 中設定的 Authentication 值。(Connection string only.) Override and unset an Authentication value set in the DSN.
SqlPassword利用使用者名稱與密碼直接向 SQL Server 執行個體進行驗證。Directly authenticate to a SQL Server instance using a username and password.
ActiveDirectoryPassword利用使用者名稱與密碼,以 Azure Active Directory 身分識別進行驗證。Authenticate with an Azure Active Directory identity using a username and password.
ActiveDirectoryIntegrated僅限 Windows 與 Linux/Mac 17.6+ 驅動程式。Windows, and Linux/Mac 17.6+, driver only. 使用整合式驗證,以 Azure Active Directory 身分識別進行驗證。Authenticate with an Azure Active Directory identity using integrated authentication.
ActiveDirectoryInteractive「僅限 Windows 驅動程式」 。Windows driver only. 使用互動式驗證,以 Azure Active Directory 身分識別進行驗證。Authenticate with an Azure Active Directory identity using interactive authentication.
ActiveDirectoryMsi使用受控識別驗證,以 Azure Active Directory 身分識別進行驗證。Authenticate with Azure Active Directory identity using managed identity authentication. 針對使用者指派的識別,UID 設成使用者身分識別的物件識別碼。For user-assigned identity, UID is set to the object ID of the user identity.
ActiveDirectoryServicePrincipal (17.7 +) 使用服務主體驗證以 Azure Active Directory 身分識別進行驗證。(17.7+) Authenticate with Azure Active Directory identity using service principal authentication. UID 會設為服務主體的用戶端識別碼。UID is set to the client ID of the service principal. PWD 設定為用戶端密碼。PWD is set to the client secret.
Encrypt (未設定)、YesNo(not set), Yes, No (請參閱描述)(see description) 控制連接的加密。Controls encryption for a connection. 如果設定的前置屬性值 Authenticationnone 在 DSN 或連接字串中,則預設值為 YesIf the pre-attribute value of the Authentication setting isn't none in the DSN or connection string, the default is Yes. 否則預設為 NoOtherwise, the default is No. 如果屬性 SQL_COPT_SS_AUTHENTICATION 會覆寫 Authentication 的前置屬性值,請在 DSN、連接字串或連線屬性中明確設定 Encryption 的值。If the attribute SQL_COPT_SS_AUTHENTICATION overrides the pre-attribute value of Authentication, explicitly set the value of Encryption in the DSN or connection string or connection attribute. 如果在 DSN 或連接字串中將值設定為 Yes,則 Encryption 的前置屬性值為 YesThe pre-attribute value of Encryption is Yes if the value is set to Yes in either the DSN or connection string.

新的和/或已修改的連線屬性New and/or Modified Connection Attributes

下列預先連線的連線屬性已引進或修改來支援 Azure Active Directory 驗證。The following pre-connect connection attributes have either been introduced or modified to support Azure Active Directory authentication. 當連線屬性具有對應的連接字串或 DSN 關鍵字且已設定時,連線屬性就會取得高優先順序。When a connection attribute has a corresponding connection string or DSN keyword and is set, the connection attribute takes precedence.

屬性Attribute 類型Type Values 預設Default 描述Description
SQL_COPT_SS_AUTHENTICATION SQL_IS_INTEGER SQL_AU_NONE, SQL_AU_PASSWORD, SQL_AU_AD_INTEGRATED, SQL_AU_AD_PASSWORD, SQL_AU_AD_INTERACTIVE, SQL_AU_AD_MSI, SQL_AU_AD_SPA, SQL_AU_RESETSQL_AU_NONE, SQL_AU_PASSWORD, SQL_AU_AD_INTEGRATED, SQL_AU_AD_PASSWORD, SQL_AU_AD_INTERACTIVE, SQL_AU_AD_MSI, SQL_AU_AD_SPA, SQL_AU_RESET (未設定)(not set) 請參閱上方的 Authentication 關鍵字描述。See description of Authentication keyword above. SQL_AU_NONE 提供來明確覆寫 Authentication DSN 和/或連接字串中的設定值,並在 SQL_AU_RESET 取消設定屬性時,允許 dsn 或連接字串值的優先順序。SQL_AU_NONE is provided to explicitly override a set Authentication value in the DSN and/or connection string, while SQL_AU_RESET unsets the attribute if it was set, allowing the DSN or connection string value to take precedence.
SQL_COPT_SS_ACCESS_TOKEN SQL_IS_POINTER 指向 ACCESSTOKEN 的指標或 NULLPointer to ACCESSTOKEN or NULL NULLNULL 如果不是 Null,請指定要使用的 AzureAD 存取權杖。If non-null, specifies the AzureAD Access Token to use. 指定存取權杖以及 UIDPWDTrusted_ConnectionAuthentication 連接字串關鍵字或其對等屬性是錯誤的。It's an error to specify an access token and also UID, PWD, Trusted_Connection, or Authentication connection string keywords or their equivalent attributes.
注意: ODBC 驅動程式版本13.1 只支援在 Windows 上進行這種設定。NOTE: ODBC Driver version 13.1 only supports this setting on Windows.
SQL_COPT_SS_ENCRYPT SQL_IS_INTEGER SQL_EN_OFF, SQL_EN_ONSQL_EN_OFF, SQL_EN_ON (請參閱描述)(see description) 控制連接的加密。Controls encryption for a connection. SQL_EN_OFFSQL_EN_ON 會分別停用和啟用加密。SQL_EN_OFF and SQL_EN_ON disable and enable encryption, respectively. 如果設定的前置屬性值不是 Authentication noneSQL_COPT_SS_ACCESS_TOKEN 已設定,而且未 Encrypt 在 DSN 或連接字串中指定,則預設值為 SQL_EN_ONIf the pre-attribute value of the Authentication setting isn't none or SQL_COPT_SS_ACCESS_TOKEN is set, and Encrypt wasn't specified in either the DSN or connection string, the default is SQL_EN_ON. 否則預設為 SQL_EN_OFFOtherwise, the default is SQL_EN_OFF. 如果連接屬性 SQL_COPT_SS_AUTHENTICATION 設定為 [否] none ,則 SQL_COPT_SS_ENCRYPT Encrypt 在 DSN 或連接字串中未指定時,明確地將設定為所需的值。If the connection attribute SQL_COPT_SS_AUTHENTICATION is set to not none, explicitly set SQL_COPT_SS_ENCRYPT to the desired value if Encrypt wasn't specified in the DSN or connection string. 這個屬性的有效值會控制是否將針對連線使用加密 (部分機器翻譯)。The effective value of this attribute controls whether encryption will be used for the connection.
SQL_COPT_SS_OLDPWD - - - Azure Active Directory 不支援,因為對 Azure AD 主體進行的密碼變更無法透過 ODBC 連線來完成。Not supported with Azure Active Directory, since password changes to Azure AD principals cannot be accomplished through an ODBC connection.

SQL Server 驗證的密碼逾期已在 SQL Server 2005 中推出。Password expiration for SQL Server Authentication was introduced in SQL Server 2005. 已新增 SQL_COPT_SS_OLDPWD 屬性,讓用戶端能夠同時提供舊的和新的密碼進行連線。The SQL_COPT_SS_OLDPWD attribute was added to allow the client to provide both the old and the new password for the connection. 當設定此屬性時,提供者將不會使用第一個連接的連接集區或未來的連接,因為連接字串將會包含現在已變更的「舊密碼」。When this property is set, the provider won't use the connection pool for the first connection or for future connections, since the connection string will contain the "old password", which has now changed.
SQL_COPT_SS_INTEGRATED_SECURITY SQL_IS_INTEGER SQL_IS_OFF,SQL_IS_ONSQL_IS_OFF,SQL_IS_ON SQL_IS_OFF 「已淘汰」 ;請改用設定為 SQL_AU_AD_INTEGRATEDSQL_COPT_SS_AUTHENTICATIONDeprecated; use SQL_COPT_SS_AUTHENTICATION set to SQL_AU_AD_INTEGRATED instead.

針對伺服器登入的存取驗證強制使用 Windows 驗證 (Linux 和 macOS 上的 Kerberos)。Forces use of Windows Authentication (Kerberos on Linux and macOS) for access validation on server login. 使用 Windows 驗證時,驅動程式會忽略在 SQLConnectSQLDriverConnectSQLBrowseConnect 處理期間所提供的使用者識別碼和密碼值。When Windows Authentication is used, the driver ignores user identifier and password values provided as part of SQLConnect, SQLDriverConnect, or SQLBrowseConnect processing.

Azure Active Directory 的 UI 新增項目 (僅限 Windows 驅動程式)UI Additions for Azure Active Directory (Windows driver only)

驅動程式的 DSN 設定和連線 Ui 已使用 Azure AD 的驗證所需的更多選項來增強。The DSN setup and connection UIs of the driver have been enhanced with the more options necessary for using authentication with Azure AD.

在 UI 中建立和編輯 DSNCreating and editing DSNs in the UI

使用驅動程式的安裝程式 UI 來建立或編輯現有的 DSN 時,可以使用新的 Azure AD 驗證選項:It's possible to use the new Azure AD authentication options when creating or editing an existing DSN using the driver's setup UI:

Authentication=ActiveDirectoryIntegrated,適用於對 Azure SQL Database 進行 Azure Active Directory 整合式驗證Authentication=ActiveDirectoryIntegrated for Azure Active Directory Integrated authentication to Azure SQL Database

DSN 建立及編輯畫面,其中已選取 Azure Active Directory 整合式驗證。

Authentication=ActiveDirectoryPassword,適用於對 Azure SQL Database 進行 Azure Active Directory 使用者名稱/密碼驗證Authentication=ActiveDirectoryPassword for Azure Active Directory username/password authentication to Azure SQL Database

DSN 建立及編輯畫面,其中已選取 Azure Active Directory 密碼驗證。

Authentication=ActiveDirectoryInteractive,適用於對 Azure SQL Database 進行 Azure Active Directory 互動式驗證Authentication=ActiveDirectoryInteractive for Azure Active Directory interactive authentication to Azure SQL Database

DSN 建立及編輯畫面,其中已選取 Azure Active Directory 互動式驗證。

Authentication=SqlPassword 表示對 SQL Server (Azure 或其他) 進行使用者名稱/密碼驗證Authentication=SqlPassword for username/password authentication to SQL Server (Azure or otherwise)

DSN 建立及編輯畫面,其中已選取 SQL Server 驗證。

Trusted_Connection=Yes 適用于 Windows 舊版 SSPI 整合式驗證Trusted_Connection=Yes for Windows legacy SSPI-integrated authentication

DSN 建立及編輯畫面,其中已選取整合式 Windows 驗證。

Authentication=ActiveDirectoryMsi,適用於 Azure Active Directory 受控識別驗證Authentication=ActiveDirectoryMsi for Azure Active Directory Managed Identity authentication

DSN 建立及編輯畫面,其中已選取受控服務識別驗證。

Authentication=ActiveDirectoryServicePrincipal 針對 Azure Active Directory 服務主體驗證Authentication=ActiveDirectoryServicePrincipal for Azure Active Directory service principal authentication

已選取 Azure Active Directory 服務主體驗證的 DSN 建立和編輯畫面。

這七個選項對應至 Trusted_Connection=Yes (現有的舊版 Windows SSPI 整合式驗證) 和 Authentication= ActiveDirectoryIntegrated 、、 SqlPassword ActiveDirectoryPasswordActiveDirectoryInteractiveActiveDirectoryMsiActiveDirectoryServicePrincipalThe seven options correspond to Trusted_Connection=Yes (existing legacy Windows SSPI-only integrated authentication) and Authentication= ActiveDirectoryIntegrated, SqlPassword, ActiveDirectoryPassword, ActiveDirectoryInteractive, ActiveDirectoryMsi, and ActiveDirectoryServicePrincipal respectively.

SQLDriverConnect 提示 (僅限 Windows 驅動程式)SQLDriverConnect Prompt (Windows driver only)

當 SQLDriverConnect 要求完成連線所需之資訊時顯示的提示對話方塊,其中包含四個適用於 Azure AD 驗證的新選項:The prompt dialog displayed by SQLDriverConnect when it requests information required to complete the connection contains four new options for Azure AD authentication:

由 SQLDriverConnect 所顯示的 [SQL Server 登入] 對話方塊。

這些選項對應至上述 DSN 設定 UI 中相同的六個可用選項。These options correspond to the same six available in the DSN setup UI above.

範例連接字串Example connection strings

  1. SQL Server 驗證:舊版語法。SQL Server Authentication - legacy syntax. 伺服器憑證不會經過驗證,而且只有在伺服器強制執行加密時,才會使用加密。Server certificate isn't validated, and encryption is used only if the server enforces it. 使用者名稱/密碼會在連接字串中傳遞。The username/password is passed in the connection string. server=Server;database=Database;UID=UserName;PWD=Password;
  2. SQL 驗證:新語法。SQL Authentication - new syntax. 用戶端會要求加密 (預設值 Encrypttrue) ,而且除非 TrustServerCertificate 設定為) (,否則會驗證伺服器憑證。 trueThe client requests encryption (the default value of Encrypt is true) and the server certificate gets validated, whatever the encryption setting (unless TrustServerCertificate is set to true). 使用者名稱/密碼會在連接字串中傳遞。The username/password is passed in the connection string. server=Server;database=Database;UID=UserName;PWD=Password;Authentication=SqlPassword;
  3. (向 SQL Server 或 SQL IaaS) 使用 SSPI 的整合式 Windows 驗證 (Linux 和 macOS 上的 Kerberos):目前的語法。Integrated Windows Authentication (Kerberos on Linux and macOS) using SSPI (to SQL Server or SQL IaaS) - current syntax. 除非使用加密,否則不會驗證伺服器憑證。Server certificate isn't validated, unless encryption is used. server=Server;database=Database;Trusted_Connection=yes;
  4. (「僅限 Windows 驅動程式」 )。使用 SSPI 的整合式 Windows 驗證 (如果目標資料庫位於 SQL Server 或 SQL IaaS):新語法。(Windows driver only.) Integrated Windows Authentication using SSPI (if the target database is in SQL Server or SQL IaaS) - new syntax. 用戶端會要求加密 (預設值 Encrypttrue) ,而且除非 TrustServerCertificate 設定為) (,否則會驗證伺服器憑證。 trueThe client requests encryption (the default value of Encrypt is true) and the server certificate gets validated, whatever the encryption setting (unless TrustServerCertificate is set to true). server=Server;database=Database;Authentication=ActiveDirectoryIntegrated;
  5. Azure Active Directory 使用者名稱/密碼驗證 (如果目標資料庫位於 Azure SQL Database)。Azure Active Directory Username/Password Authentication (if the target database is in Azure SQL Database). 除非設定為) ,否則伺服器憑證會經過驗證,不論加密設定 (TrustServerCertificate trueServer certificate gets validated, whatever the encryption setting (unless TrustServerCertificate is set to true). 使用者名稱/密碼會在連接字串中傳遞。The username/password is passed in the connection string. server=Server;database=Database;UID=UserName;PWD=Password;Authentication=ActiveDirectoryPassword;
  6. (「僅限 Windows 與 Linux/macOS 17.6+ 驅動程式」。)使用 ADAL 或 Kerberos 的整合式 Windows 驗證,其需要針對 Azure AD 發行的存取權杖兌換 Windows 帳戶認證 (假設目標資料庫位於 Azure SQL Database)。(Windows, and Linux/macOS 17.6+, driver only.) Integrated Windows Authentication using ADAL or Kerberos, which involves redeeming Windows account credentials for an Azure AD-issued access token, assuming the target database is in Azure SQL Database. 除非設定為) ,否則伺服器憑證會經過驗證,不論加密設定 (TrustServerCertificate trueServer certificate gets validated, whatever the encryption setting (unless TrustServerCertificate is set to true). 在 Linux/macOS 上,您必須提供適當的 Kerberos 票證。On Linux/macOS, a suitable Kerberos ticket needs to be available. 如需詳細資訊,請參閱下一節的同盟帳戶和 使用整合式驗證For more information, see the section below on Federated Accounts and Using Integrated Authentication. server=Server;database=Database;Authentication=ActiveDirectoryIntegrated;
  7. (僅限 Windows 驅動程式。 ) Azure AD 互動式驗證會使用 Azure Active Directory Multi-Factor Authentication 技術來設定連接。(Windows driver only.) Azure AD Interactive Authentication uses Azure Active Directory Multi-Factor Authentication technology to set up connection. 在此模式中,藉由提供登入識別碼來觸發 Azure 驗證對話方塊,並允許使用者輸入密碼以完成連線。In this mode, by providing the login ID, an Azure Authentication dialog is triggered and allows the user to input the password to complete the connection. 使用者名稱會在連接字串中傳遞。The username is passed in the connection string. server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive; 使用 Active Directory 互動式驗證時的 Windows Azure 驗證 UI。server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive; Windows Azure Authentication UI when using Active Directory Interactive authentication.
  8. Azure Active Directory 受控識別驗證會使用系統指派或使用者指派的身分識別進行驗證以設定連線。Azure Active Directory Managed Identity Authentication uses system-assigned or user-assigned identity for authentication to set up connection. 針對使用者指派的識別,UID 設成使用者身分識別的物件識別碼。For user-assigned identity, UID is set to the object ID of the user identity.
    針對系統指派的身分識別,For system-assigned identity,
    server=Server;database=Database;Authentication=ActiveDirectoryMsi;
    對於物件識別碼等於 myObjectId 之使用者指派的身分識別,For user-assigned identity with object ID equals to myObjectId,
    server=Server;database=Database;UID=myObjectId;Authentication=ActiveDirectoryMsi;
  9. Azure Active Directory 服務主體驗證 server=Server;databse=Database;UID=clientId;PWD=clientSecret;Authentication=ActiveDirectoryServicePrincipal;Azure Active Directory Service Principal Authentication server=Server;databse=Database;UID=clientId;PWD=clientSecret;Authentication=ActiveDirectoryServicePrincipal;

注意

  • 搭配 17.4.2 版 以前 的 Windows ODBC 驅動程式使用新的 Active Directory 選項時,確認已安裝 SQL Server 的 Active Directory 驗證程式庫When using the Active Directory options with the Windows ODBC driver prior to version 17.4.2, ensure that the Active Directory Authentication Library for SQL Server has been installed. 使用 Linux 和 macOS 驅動程式時,確認已安裝 libcurlWhen using the Linux and macOS drivers, ensure that libcurl has been installed. 對於驅動程式 17.2 版和更新版本,這不是明確的相依性,因其不是其他驗證方法或 ODBC 作業的必要項。For driver version 17.2 and later, this is not an explicit dependency since it is not required for the other authentication methods or ODBC operations.
  • 當 Azure Active Directory 設定包含條件式存取原則,且用戶端為 Windows 10 或 Server 2016 或更新版本時,透過整合式或使用者名稱/密碼進行的驗證可能會失敗。When Azure Active Directory configuration includes Conditional Access policies, and the client is Windows 10 or Server 2016 or later, authentication via Integrated or username/password may fail. 條件式存取原則要求使用 Windows 帳戶管理員 (WAM),其由適用於 Windows 的驅動程式 17.6 版或更新版本所支援。Conditional Access policies require the use of Windows Account Manager (WAM), which is supported in driver version 17.6 or later for Windows. 若要使用 WAM,請分別在 HKLM\Software\ODBC\ODBCINST.INI\ODBC Driver 17 for SQL ServerHKCU\Software\ODBC\ODBC.INI\<your-user-DSN-name>HKLM\Software\ODBC\ODBC.INI\<your-system-DSN-name> 中針對全域、使用者 DSN 或系統 DSN 範圍的設定建立名為 ADALuseWAM 的新字串或 DWORD 值,並將其設定為 1 的值。To use WAM, create a new string or DWORD value named ADALuseWAM in HKLM\Software\ODBC\ODBCINST.INI\ODBC Driver 17 for SQL Server, HKCU\Software\ODBC\ODBC.INI\<your-user-DSN-name>, or HKLM\Software\ODBC\ODBC.INI\<your-system-DSN-name> for global, user DSN, or system DSN-scoped configuration respectively, and set it to a value of 1. 請注意,使用 WAM 進行驗證並不支援使用 runas 以不同的使用者身分執行應用程式。Note that authentication with WAM does not support running the application as a different user with runas. Linux 或 macOS 並不支援需要條件式存取原則的案例。Scenarios which require Condtitional Access policies are not supported for Linux or macOS.
  • 若要使用 SQL Server 帳戶使用者名稱與密碼進行連線,您現在可以使用新的 SqlPassword 選項,這是特別適用於 Azure SQL 的建議選項,因為此選項會啟用更安全的連線預設值。To connect using a SQL Server account username and password, you may now use the new SqlPassword option, which is recommended especially for Azure SQL since this option enables more secure connection defaults.
  • 若要使用 Azure Active Directory 帳戶使用者名稱與密碼進行連線,請分別利用使用者名稱與密碼來指定連接字串中的 Authentication=ActiveDirectoryPasswordUIDPWD 關鍵字。To connect using an Azure Active Directory account username and password, specify Authentication=ActiveDirectoryPassword in the connection string and the UID and PWD keywords with the username and password, respectively.
  • 若要使用 Windows 整合式或 Active Directory 整合式 (僅限 Windows 與 Linux/macOS 17.6+ 驅動程式) 驗證進行連線,請在連接字串中指定 Authentication=ActiveDirectoryIntegratedTo connect using Windows Integrated or Active Directory Integrated (Windows, and Linux/macOS 17.6+, driver only) authentication, specify Authentication=ActiveDirectoryIntegrated in the connection string. 驅動程式將自動選擇正確的驗證模式。The driver will choose the correct authentication mode automatically. 不得指定 UIDPWDUID and PWD must not be specified.
  • 若要使用 Active Directory 互動式 (僅限 Windows 驅動程式) 驗證進行連線,就必須指定 UIDTo connect using Active Directory Interactive (Windows driver only) authentication, UID must be specified.

使用存取權杖進行驗證Authenticating with an Access Token

SQL_COPT_SS_ACCESS_TOKEN 預先連線屬性允許使用從 Azure AD 取得的存取權杖進行驗證 (而不是使用者名稱與密碼),同時也會略過驅動程式對存取權杖的協商和取得。The SQL_COPT_SS_ACCESS_TOKEN pre-connection attribute allows the use of an access token obtained from Azure AD for authentication instead of username and password, and also bypasses the negotiation and obtaining of an access token by the driver. 若要使用存取權杖,請將 SQL_COPT_SS_ACCESS_TOKEN 連線屬性設定為指向 ACCESSTOKEN 結構的指標:To use an access token, set the SQL_COPT_SS_ACCESS_TOKEN connection attribute to a pointer to an ACCESSTOKEN structure:

typedef struct AccessToken
{
    DWORD dataSize;
    BYTE data[];
} ACCESSTOKEN;

ACCESSTOKEN 是一個可變長度結構,由 4 個位元組的「長度」 且後面接著形成存取權杖之不透明資料的「長度」 位元組所組成。The ACCESSTOKEN is a variable-length structure consisting of a 4-byte length followed by length bytes of opaque data that form the access token. 由於 SQL Server 處理存取權杖的方式,因此必須展開透過 OAuth 2.0 JSON 回應取得的權杖,讓每個位元組後面都是零填補位元組,類似于僅包含 ASCII 字元的 UCS-2 字串。Because of how SQL Server handles access tokens, one obtained via an OAuth 2.0 JSON response must be expanded so that each byte is followed by a zero padding byte, similar to a UCS-2 string containing only ASCII characters. 不過,權杖是不透明的值,而指定的長度(以位元組為單位)不得包含任何 null 結束字元。However, the token is an opaque value and the length specified, in bytes, must NOT include any null terminator. 由於其長度和格式條件約束很大,因此只能透過連接屬性以程式設計方式使用此驗證方法 SQL_COPT_SS_ACCESS_TOKENBecause of their considerable length and format constraints, this method of authentication is only available programmatically via the SQL_COPT_SS_ACCESS_TOKEN connection attribute. 沒有對應的 DSN 或連接字串關鍵字。There's no corresponding DSN or connection string keyword. 連接字串不能包含 UIDPWDAuthenticationTrusted_Connection 關鍵字。The connection string must not contain UID, PWD, Authentication, or Trusted_Connection keywords.

注意

ODBC Driver 13.1 版僅在 Windows 上支援此驗證。The ODBC Driver version 13.1 only supports this authentication on Windows.

Azure Active Directory 驗證範例程式碼Azure Active Directory Authentication Sample Code

下列範例示範如何使用 Azure Active Directory 搭配連線關鍵字來連線到 SQL Server 所需的程式碼。The following sample shows the code required to connect to SQL Server using Azure Active Directory with connection keywords. 不需要變更應用程式程式碼本身。There's no need to change the application code itself. 如果使用連接字串或 DSN (如果使用的話),則是使用 Azure AD 進行驗證所需的唯一修改:The connection string, or DSN if one is used, is the only modification needed to use Azure AD for authentication:

    ...
    SQLCHAR connString[] = "Driver={ODBC Driver 17 for SQL Server};Server={server};UID=myuser;PWD=myPass;Authentication=ActiveDirectoryPassword"
    ...
    SQLDriverConnect(hDbc, NULL, connString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    ...

下列範例示範如何使用 Azure Active Directory 搭配存取權杖驗證來連線到 SQL Server 所需的程式碼。The following sample shows the code required to connect to SQL Server using Azure Active Directory with access token authentication. 在此情況下,必須修改應用程式程式碼來處理存取權杖,並設定相關聯的連接屬性。In this case, it's necessary to modify application code to process the access token and set the associated connection attribute.

    SQLCHAR connString[] = "Driver={ODBC Driver 17 for SQL Server};Server={server}"
    SQLCHAR accessToken[] = "eyJ0eXAiOi..."; // In the format extracted from an OAuth JSON response
    ...
    DWORD dataSize = 2 * strlen(accessToken);
    ACCESSTOKEN *pAccToken = malloc(sizeof(ACCESSTOKEN) + dataSize);
    pAccToken->dataSize = dataSize;
    // Expand access token with padding bytes
    for(int i = 0, j = 0; i < dataSize; i += 2, j++) {
        pAccToken->data[i] = accessToken[j];
        pAccToken->data[i+1] = 0;
    }
    ...
    SQLSetConnectAttr(hDbc, SQL_COPT_SS_ACCESS_TOKEN, (SQLPOINTER)pAccToken, SQL_IS_POINTER);
    SQLDriverConnect(hDbc, NULL, connString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    ...
    free(pAccToken);

下列範例連接字串用於 Azure Active Directory 互動式驗證。The following sample connection string is for use with Azure Active Directory Interactive Authentication. 它不包含 PWD 欄位,因為密碼會輸入到 Azure 驗證畫面上。It doesn't contain PWD field as the password would be entered on the Azure Authentication screen.

SQLCHAR connString[] = "Driver={ODBC Driver 17 for SQL Server};Server={server};UID=myuser;Authentication=ActiveDirectoryInteractive"

下列範例連接字串用於 Azure Active Directory 受控識別驗證。The following sample connection string is for use with Azure Active Directory Managed Identity Authentication. 使用使用者指派的身分識別時,UID 會設為使用者身分識別的物件識別碼。UID is set to the object ID of the user identity when using a user-assigned identity.

// For system-assigned identity,
SQLCHAR connString[] = "Driver={ODBC Driver 17 for SQL Server};Server={server};Authentication=ActiveDirectoryMsi"
...
// For user-assigned identity with object ID equals to myObjectId
SQLCHAR connString[] = "Driver={ODBC Driver 17 for SQL Server};Server={server};UID=myObjectId;Authentication=ActiveDirectoryMsi"

在 Linux/macOS 上使用 ADFS 同盟帳戶的考量Considerations for using ADFS Federated Accounts on Linux/macOS

從 17.6 版開始,適用於 Linux 與 macOS 的驅動程式支援搭配使用者名稱/密碼 (ActiveDirectoryPassword) 或 Kerberos (ActiveDirectoryIntegrated) 使用 Azure Active Directory ADFS 同盟帳戶進行驗證。Starting with version 17.6, the drivers for Linux and macOS support authentication using Azure Active Directory ADFS-federated accounts using either username/password (ActiveDirectoryPassword) or Kerberos (ActiveDirectoryIntegrated). 使用整合模式時,會有一些取決於平台的限制。There are some limitations dependent on the platform when using Integrated mode.

使用 UPN 尾碼不同于 Kerberos 領域的使用者進行驗證時(也就是使用替代的 UPN 尾碼),您必須使用企業主體選項 (搭配使用此選項 -E kinit ,並在 user@federated-domain 取得 Kerberos 票證時以) 形式提供主體名稱。When authenticating with a user whose UPN suffix is different from the Kerberos realm, that is, an alternate UPN suffix is in use, it's necessary to use the Enterprise Principal option (use the -E option with kinit, and supply the principal name in the form user@federated-domain) when obtaining Kerberos tickets. 如此一來,驅動程式可以正確地判斷同盟網域和 Kerberos 領域。This way, the driver can correctly determine both the federated domain and the Kerberos realm.

您可以透過檢查 klist 命令的輸出,來確認有適當的 Kerberos 票證可供使用。You can verify that a suitable Kerberos ticket is available by inspecting the output of the klist command. 如果同盟網域與 Kerberos 領域及 UPN 尾碼相同,主體名稱的格式將會是 user@realmIf the federated domain is the same as the Kerberos realm and UPN suffix, the principal name will be of the form user@realm. 如果不同,主體名稱的格式應為 user@federated-domain@realmIf it's different, the principal name should be of the form user@federated-domain@realm.

LinuxLinux

在 SuSE 11 中,1.6. x 的預設 Kerberos 程式庫版本不支援使用替代 UPN 尾碼所需的企業主體選項。On SuSE 11, the default Kerberos library version of 1.6.x doesn't support the Enterprise Principal option necessary to use alternate UPN suffixes. 若要搭配 Azure AD 整合式驗證使用替代 UPN 尾碼,請將 Kerberos 程式庫升級到 1.7 或更新版本。To use alternate UPN suffixes with Azure AD Integrated authentication, upgrade the Kerberos library to 1.7 or newer.

在 Alpine Linux 上,預設 libcurl 不支援 Azure AD 整合式驗證所需的 SPNEGO/Kerberos 驗證。On Alpine Linux, the default libcurl doesn't support the SPNEGO/Kerberos authentication required for Azure AD Integrated authentication.

macOSmacOS

系統 Kerberos 程式庫 kinit 支援具有選項的企業主體 --enterprise ,但也會隱含地執行名稱標準化,以防止使用替代的 UPN 尾碼。The system Kerberos library kinit supports Enterprise Principal with the --enterprise option, but also implicitly does name canonicalization, which prevents the use of alternate UPN suffixes. 若要搭配 Azure AD 整合式驗證使用替代 UPN 尾碼,請透過 brew install krb5 安裝較新的 Kerberos 程式庫,並以上面所述的方式搭配 -E 選項使用其 kinitTo use alternate UPN suffixes with Azure AD Integrated authentication, install a newer Kerberos library via brew install krb5 and use its kinit with the -E option as described above.

另請參閱See Also

適用於使用 Azure AD 驗證之 Azure SQL Database 的權杖型驗證支援Token-based authentication support for Azure SQL Database using Azure AD auth
使用整合式驗證Using Integrated Authentication