Trouble using Azure AD with ODBC driver for auth

Question

Hi all,

I'm connecting to an SQL database through an ODBC driver (https://learn.microsoft.com/en-us/sql/odbc/microsoft/microsoft-odbc-desktop-database-drivers?view=sql-server-ver16

Microsoft ODBC Desktop Database Drivers - Open Database Connectivity (ODBC)Microsoft ODBC Desktop Database Driverslearn.microsoft.com).

The odbc driver is authenticated via Azure AD and an Azure AD enterprise application.

The trouble we're having is that we can connect fine via browser login to AD (including MFA), but when using Azure AD authentication option in ODBC, the driver is typically only allowed access a short period and then the credentials (login/password) are denied.

Looking at the logs it seems to be because of required Multifactor Auth. which doesn't seem to work with the AzureAD auth option in ODBC.

Any thoughts, feedback or experiences with solutions to this issue?

Thanks!

Answer 1

Hi Anders •,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand, you are facing issue in connecting to an SQL database through an ODBC driver.

I hope you are following the documentation here: Using Azure Active Directory with the ODBC Driver and Configure and manage Azure AD authentication with Azure SQL.

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.

Please check the Operating System and Driver version in use and if it complies to the prerequisites.

The ODBC Driver on Linux and macOS before version 17.6 only supports Azure Active Directory authentication directly against Azure 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. As of driver version 17.6, this limitation has been removed.

• 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. When using the Linux and macOS drivers, ensure that libcurl has been installed. 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.
• 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. Conditional Access policies require the use of Web Account Manager (WAM), which is supported in driver version 17.6 or later for Windows. 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. Note that authentication with WAM does not support running the application as a different user with runas. Scenarios which require Condtitional Access policies are not supported for Linux or macOS.
• 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.
• 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.
• To 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. For driver versions 17.7 or earlier, UID and PWD must not be specified. Beginning with driver version 17.8, UID and PWD are ignored.
• To connect using Active Directory Interactive (Windows driver only) authentication, UID must be specified. For driver versions 17.7 and earlier, PWD must not be specified. Beginning with driver version 17.8, PWD is ignored.
• Starting with version 18.1, Trusted_Connection=Yes no longer uses Azure Active Directory federated authentication by default and uses SSPI-integrated instead. To use Azure Active Directory for this option, TrustedConnection_UseAAD=Yes should be configured.
• ODBC driver versions 17.7 and lower have a known issue with connection timeout when AAD authentication and Force Encryption are enabled on a SQL instance. SQL Server errorlog may contain error messages such as: "Error: 33155, Severity: 20, State: 1. A disconnect event was raised when server is waiting for Federated Authentication token. This could be due to client close or server timeout expired.". If you are using SQL high availability solutions such as Availability Groups or Failover Cluster Instances, the internal cluster communication for SQL may be affected by this behavior which can affect resource availability. In the cluster log, you may see error messages such as: [hadrag] Connect to SQL Server ...ODBC Error: [HY000] [Microsoft][ODBC Driver 17 for SQL Server]An unknown error has occurred. Detailed error information is not available. (0). ODBC driver versions 17.10 and higher fix this issue and with SQL Server 2022 GDR KB5021522 /CU1 KB5022375, the latest driver which contains this fix is installed with SQL installation. You can verify which version of ODBC driver you have installed by referring to the ODBC Data Source Administrator.

Hope this helps. Please let us know your inputs on the same and if still you face issue, please reply with the configuration details so that we can further assist you.

If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thank you.