使用 Azure Active DirectoryUsing Azure Active Directory

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

下載OLE DB 驅動程式下載DownloadDownload OLE DB Driver

目的Purpose

從版本18.2.1 版開始,適用于 SQL Server 的 Microsoft OLE DB 驅動程式可讓 OLE DB 應用程式使用同盟身分識別連接到 Azure SQL Database 的實例。Starting with version 18.2.1, Microsoft OLE DB Driver for SQL Server allows OLE DB applications to connect to an instance of Azure SQL Database using a federated identity. 新的驗證方法包括:The new authentication methods include:

  • Azure Active Directory 登入識別碼和密碼Azure Active Directory login ID and password
  • Azure Active Directory 存取權杖Azure Active Directory access token
  • Azure Active Directory 整合式驗證Azure Active Directory integrated authentication
  • SQL 登入識別碼和密碼SQL login ID and password

18.3 版新增了下列驗證方法的支援:Version 18.3 adds support for the following authentication methods:

  • Azure Active Directory 互動式驗證Azure Active Directory interactive authentication
  • Azure Active Directory MSI 驗證Azure Active Directory MSI authentication

注意

支援在 DataTypeCompatibility (或其對應的屬性)設定為 80 的情況下使用下列驗證模式:Using the following authentication modes with DataTypeCompatibility (or its corresponding property) set to 80 is not supported:

  • 使用登入識別碼和密碼 Azure Active Directory 驗證Azure Active Directory authentication using login ID and password
  • 使用存取權杖 Azure Active Directory 驗證Azure Active Directory authentication using access token
  • Azure Active Directory 整合式驗證Azure Active Directory integrated authentication
  • Azure Active Directory 互動式驗證Azure Active Directory interactive authentication
  • Azure Active Directory MSI 驗證Azure Active Directory MSI authentication

連接字串關鍵字和屬性Connection string keywords and properties

已引進下列連接字串關鍵字來支援 Azure Active Directory 驗證:The following connection string keywords have been introduced to support Azure Active Directory authentication:

連接字串關鍵字Connection string keyword 連線屬性Connection property DescriptionDescription
存取權杖Access Token SSPROP_AUTH_ACCESS_TOKENSSPROP_AUTH_ACCESS_TOKEN 指定要驗證 Azure Active Directory 的存取權杖。Specifies an access token to authenticate to Azure Active Directory.
驗證Authentication SSPROP_AUTH_MODESSPROP_AUTH_MODE 指定要使用的驗證方法。Specifies authentication method to use.

如需新關鍵字/屬性的詳細資訊,請參閱下列頁面:For more information about the new keywords/properties, see the following pages:

加密和憑證驗證Encryption and certificate validation

本節討論加密和憑證驗證行為的變更。This section discusses the changes in encryption and certificate validation behavior. 這些變更只有在使用新的驗證或存取權杖連接字串關鍵字(或其對應的屬性)時有效。These changes are only effective when using the new Authentication or Access Token connection string keywords (or their corresponding properties).

加密Encryption

為了提升安全性,當使用新的連線屬性/關鍵字時,驅動程式會將預設的加密值設定為 yes 來覆寫。To improve security, when the new connection properties/keywords are used, the driver overrides the default encryption value by setting it to yes. 覆寫會發生在資料來源物件初始化時間。Overriding happens at data source object initialization time. 如果在以任何方式初始化之前設定加密,則會遵守且不會覆寫值。If encryption is set before initialization by any means, the value is respected and not overridden.

注意

在 ADO 應用程式中,以及在透過 IDataInitialize::GetDataSource 取得 IDBInitialize 介面的應用程式中,執行介面的核心元件會將加密明確設定為其 no 的預設值。In ADO applications and in applications that obtain the IDBInitialize interface through IDataInitialize::GetDataSource, the Core Component implementing the interface explicitly sets encryption to its default value of no. 因此,新的驗證屬性/關鍵字會遵守此設定,而且會覆寫加密值。As a result, the new authentication properties/keywords respect this setting and the encryption value isn't overridden. 因此,建議您明確地將這些應用程式設定 Use Encryption for Data=true 以覆寫預設值。Therefore, it is recommended that these applications explicitly set Use Encryption for Data=true to override the default value.

憑證驗證Certificate validation

為了提升安全性,新的連接屬性/關鍵字會採用與用戶端加密設定無關TrustServerCertificate 設定(及其對應的連接字串關鍵字/屬性)。To improve security, the new connection properties/keywords respect the TrustServerCertificate setting (and its corresponding connection string keywords/properties) independently of the client encryption setting. 因此,預設會驗證伺服器憑證。As a result, server certificate is validated by default.

注意

憑證驗證也可以透過 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI18.0\GeneralFlags\Flag2 登錄專案的 [Value] 欄位來控制。Certificate validation can also be controlled through the Value field of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI18.0\GeneralFlags\Flag2 registry entry. 有效值為 01Valid values are 0 or 1. OLE DB 驅動程式會在登錄和連接屬性/關鍵字設定之間選擇最安全的選項。The OLE DB driver chooses the most secure option between the registry and the connection property/keyword settings. 也就是說,只要至少其中一個登錄/連線設定啟用伺服器憑證驗證,驅動程式就會驗證伺服器憑證。That is, the driver will validate the server certificate as long as at least one of the registry/connection settings enables server certificate validation.

新增 GUIGUI additions

驅動程式圖形化使用者介面已經過增強,以允許 Azure Active Directory 驗證。The driver graphical user interface has been enhanced to allow Azure Active Directory authentication. 如需詳細資訊,請參閱:For more information, see:

範例連接字串Example connection strings

本節顯示新的和現有連接字串關鍵字的範例,以搭配 IDataInitialize::GetDataSourceDBPROP_INIT_PROVIDERSTRING 屬性使用。This section shows examples of new and existing connection string keywords to be used with IDataInitialize::GetDataSource and DBPROP_INIT_PROVIDERSTRING property.

SQL 驗證SQL authentication

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:
    • 新增:New:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = SqlPassword;使用者識別碼 = [username];Password = [password];針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=SqlPassword;User ID=[username];Password=[password];Use Encryption for Data=true

    • 已淘汰:Deprecated:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];使用者識別碼 = [username];Password = [password];針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];User ID=[username];Password=[password];Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:
    • 新增:New:

      Server=[server];Database=[database];Authentication=SqlPassword;UID=[username];PWD=[password];Encrypt=yesServer=[server];Database=[database];Authentication=SqlPassword;UID=[username];PWD=[password];Encrypt=yes

    • 已淘汰:Deprecated:

      Server=[server];Database=[database];UID=[username];PWD=[password];Encrypt=yesServer=[server];Database=[database];UID=[username];PWD=[password];Encrypt=yes

使用安全性支援提供者介面(SSPI)的整合式 Windows 驗證Integrated Windows authentication using Security Support Provider Interface (SSPI)

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:
    • 新增:New:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryIntegrated;針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true

    • 已淘汰:Deprecated:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];整合式安全性 = SSPI;針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Integrated Security=SSPI;Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:
    • 新增:New:

      Server=[server];Database=[database];Authentication=ActiveDirectoryIntegrated;Encrypt=yesServer=[server];Database=[database];Authentication=ActiveDirectoryIntegrated;Encrypt=yes

    • 已淘汰:Deprecated:

      Server=[server];Database=[database];Trusted_Connection=yes;Encrypt=yesServer=[server];Database=[database];Trusted_Connection=yes;Encrypt=yes

Azure Active Directory 使用者名稱和密碼驗證Azure Active Directory username and password authentication

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:

    Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryPassword;使用者識別碼 = [username];Password = [password];針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryPassword;User ID=[username];Password=[password];Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:

    Server=[server];Database=[database];Authentication=ActiveDirectoryPassword;UID=[username];PWD=[password];Encrypt=yesServer=[server];Database=[database];Authentication=ActiveDirectoryPassword;UID=[username];PWD=[password];Encrypt=yes

Azure Active Directory 整合式驗證Azure Active Directory integrated authentication

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:

    Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryIntegrated;針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:

    Server=[server];Database=[database];Authentication=ActiveDirectoryIntegrated;Encrypt=yesServer=[server];Database=[database];Authentication=ActiveDirectoryIntegrated;Encrypt=yes

使用存取權杖 Azure Active Directory 驗證Azure Active Directory authentication using an access token

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:

    Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];存取權杖 = [存取權杖] ;針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Access Token=[access token];Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:

    不支援透過 DBPROP_INIT_PROVIDERSTRING 提供存取權杖Providing access token through DBPROP_INIT_PROVIDERSTRING isn't supported

Azure Active Directory 互動式驗證Azure Active Directory interactive authentication

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:

    Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryInteractive;使用者識別碼 = [username];針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryInteractive;User ID=[username];Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:

    Server = [server];D 資料庫取得 = [Database];Authentication = ActiveDirectoryInteractive;UID = [username];Encrypt = 是Server=[server];Database=[database];Authentication=ActiveDirectoryInteractive;UID=[username];Encrypt=yes

Azure Active Directory MSI 驗證Azure Active Directory MSI authentication

  • 使用 IDataInitialize::GetDataSourceUsing IDataInitialize::GetDataSource:
    • 使用者指派的受控識別:User-assigned managed identity:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryMSI;使用者識別碼 = [物件識別碼];針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryMSI;User ID=[Object ID];Use Encryption for Data=true

    • 系統指派的受控識別:System-assigned managed identity:

      Provider = 內含 MSOLEDBSQL.H; 資料來源 = [伺服器]; 初始目錄 = [資料庫];Authentication = ActiveDirectoryMSI;針對資料使用加密 = trueProvider=MSOLEDBSQL;Data Source=[server];Initial Catalog=[database];Authentication=ActiveDirectoryMSI;Use Encryption for Data=true

  • 使用 DBPROP_INIT_PROVIDERSTRINGUsing DBPROP_INIT_PROVIDERSTRING:
    • 使用者指派的受控識別:User-assigned managed identity:

      Server = [server];D 資料庫取得 = [Database];Authentication = ActiveDirectoryMSI;UID = [物件識別碼];Encrypt = 是Server=[server];Database=[database];Authentication=ActiveDirectoryMSI;UID=[Object ID];Encrypt=yes

    • 系統指派的受控識別:System-assigned managed identity:

      Server = [server];D 資料庫取得 = [Database];Authentication = ActiveDirectoryMSI;Encrypt = 是Server=[server];Database=[database];Authentication=ActiveDirectoryMSI;Encrypt=yes

程式碼範例Code samples

下列範例顯示使用連接關鍵字連接到 Azure Active Directory 所需的程式碼。The following samples show the code required to connect to Azure Active Directory with connection keywords.

存取權杖Access Token

#include <string>
#include <iostream>
#include <msdasc.h>

int main()
{
    wchar_t azureServer[] = L"server";
    wchar_t azureDatabase[] = L"mydatabase";
    wchar_t accessToken[] = L"eyJ0eXAiOi...";
    IDBInitialize *pIDBInitialize = nullptr;
    IDataInitialize* pIDataInitialize = nullptr;
    HRESULT hr = S_OK;

    CoInitialize(nullptr);

    // Construct the connection string.
    std::wstring connString = L"Provider=MSOLEDBSQL;Data Source=" + std::wstring(azureServer) + L";Initial Catalog=" + 
                              std::wstring(azureDatabase) + L";Access Token=" + accessToken + L";Use Encryption for Data=true;";
    hr = CoCreateInstance(CLSID_MSDAINITIALIZE, nullptr, CLSCTX_INPROC_SERVER, 
                          IID_IDataInitialize, reinterpret_cast<LPVOID*>(&pIDataInitialize));
    if (FAILED(hr))
    {
        std::cout << "Failed to create an IDataInitialize instance." << std::endl;
        goto Cleanup;
    }
    hr = pIDataInitialize->GetDataSource(nullptr, CLSCTX_INPROC_SERVER, connString.c_str(), 
                                         IID_IDBInitialize, reinterpret_cast<IUnknown**>(&pIDBInitialize));
    if (FAILED(hr))
    {
        std::cout << "Failed to get data source object." << std::endl;
        goto Cleanup;
    }
    hr = pIDBInitialize->Initialize();
    if (FAILED(hr))
    {
        std::cout << "Failed to establish connection." << std::endl;
        goto Cleanup;
    }

Cleanup:
    if (pIDBInitialize)
    {
        pIDBInitialize->Uninitialize();
        pIDBInitialize->Release();
    }
    if (pIDataInitialize)
    {
        pIDataInitialize->Release();
    }

    CoUninitialize();
}

Active Directory 整合式Active Directory Integrated

#include <string>
#include <iostream>
#include <msdasc.h>

int main()
{
    wchar_t azureServer[] = L"server";
    wchar_t azureDatabase[] = L"mydatabase";
    IDBInitialize *pIDBInitialize = nullptr;
    IDataInitialize* pIDataInitialize = nullptr;
    HRESULT hr = S_OK;

    CoInitialize(nullptr);

    // Construct the connection string.
    std::wstring connString = L"Provider=MSOLEDBSQL;Data Source=" + std::wstring(azureServer) + L";Initial Catalog=" + 
                              std::wstring(azureDatabase) + L";Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true;";

    hr = CoCreateInstance(CLSID_MSDAINITIALIZE, nullptr, CLSCTX_INPROC_SERVER, 
                          IID_IDataInitialize, reinterpret_cast<LPVOID*>(&pIDataInitialize));
    if (FAILED(hr)) 
    {
        std::cout << "Failed to create an IDataInitialize instance." << std::endl;
        goto Cleanup;
    }
    hr = pIDataInitialize->GetDataSource(nullptr, CLSCTX_INPROC_SERVER, connString.c_str(), 
                                         IID_IDBInitialize, reinterpret_cast<IUnknown**>(&pIDBInitialize));
    if (FAILED(hr))
    {
        std::cout << "Failed to get data source object." << std::endl;
        goto Cleanup;
    }
    hr = pIDBInitialize->Initialize();
    if (FAILED(hr))
    {
        std::cout << "Failed to establish connection." << std::endl;
        goto Cleanup;
    }

Cleanup:
    if (pIDBInitialize)
    {
        pIDBInitialize->Uninitialize();
        pIDBInitialize->Release();
    }
    if (pIDataInitialize)
    {
        pIDataInitialize->Release();
    }

    CoUninitialize();
}

後續步驟Next steps