In many scenarios, we face the need to use integrated authentication in order to gain access to the required data sources to feed our analytical system. In view of Azure’s increasingly widespread use, as is the case with at least part of our infrastructure, some of these sources are hosted in Azure databases. In this case, we will discuss an actual error that we have come across when configuring and using integrated authentication in Azure databases with SSIS.
Microsoft refers to this documentation in order to try and understand the paradigm surrounding Azure integrated authentication, as well as how to configure and manage Azure Active Directory authentication with either a SQL Database, a managed instance or a SQL Data Warehouse: https://docs.microsoft.com/es-es/azure/sql-database/sql-database-aad-authentication-configure.
Without going into too much detail regarding the database configuration and other infrastructure related issues required to correctly configure the use of AAD in our company, we will focus in the use of AAD authentication to consume data via SSIS from any Azure database. Literally speaking, what needs to be configured on the client side in order to connect to a SQL Azure Database from SSIS OnPremise in order to consume data via an AAD user?
According to Microsoft’s own documentation, we will require the following:
- .NET Framework 4.6 or later https://msdn.microsoft.com/library/5a4x27ek.aspx.
- Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL) is available in multiple languages (both x86 and amd64) from the download center at Microsoft Active Directory Authentication Library for Microsoft SQL Server.
As you can see, the documentation also refers to the .NET Framework version required. However, it does not state the required version for the SQL Server ADALSQL.DLL authentication library. Furthermore, both by clicking on the link to the documentation and by doing a search on this library, you will be directed to the following link: https://www.microsoft.com/en-us/download/details.aspx?id=48742 where you can download version 13.0.200.152:
In this case, we have used the ADO NET Source component (SqlClient Data Provider) in order to do the integrated authentication with an AAD account.
Once we have downloaded the full library and updated our .Net Framework to the latest version, we created an SSIS package connected to the Azure database via ADO NET using the following type of connection string (example code):
Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;
Now, once we have run this package from OnPremise, we shall see the following error:
ADO NET Source has failed to acquire the connection {EB918ABB-4C3C-4F23-9C9A-2BE58CA444A6} with the following error message: “Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xCAA9001F; state 10 Integrated Windows authentication supported only in federation flow.”
After checking that we have followed all the steps described in the above-mentioned article in order to grant user access in the Azure database, and following several tests, we realized that the problem was due to the ADALSQL.DLL library version. And that’s because it won’t be enough to install the latest available version, but rather the subsequent version. But there is an “issue” here because this library version is not launched independently. So, in order to obtain it, you must install SSMS 17.x (the latest version possible) in order to get the updated library version. After opening a ticket with Microsoft, we were still unable to obtain the ADALSQL.DLL library file on its own. So we were recommended to install the latest available UAT and PRO environment version at the time (17.7) in order to solve the issue and ensure that we had the latest library version installed.
Summing up, in order to ensure that the SSIS integrated authentication works correctly with any Azure database, we recommend following the steps below:
- Follow the steps described in this article https://docs.microsoft.com/es-es/azure/sql-database/sql-database-aad-authentication-configure in order to grant the required database permissions to the AAD user to be used with SSIS, since this is a different process than granting AD user permissions on the OnPremise database. Ensure that you have at least the .NET Framework 4.6 version.
- Make sure that you have at least the .NET Framework 4.6 version.
- Install the latest SSMS version (17.7 or higher) in order to get the correct ADALSQL.DLL version.
- Use an ADO NET (SqlClient Data Provider and Odbc Data Provider) data source including a connection string with the following structure, as it does not admit the SSPI: Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;
- Use a proxy account to call up the SSIS package to ensure that it will be run using the credentials granted with the required permission in the Azure database.
Latest posts by Richard Luis Díaz (see all)
- Azure Database integrated authentication with SSIS - February 27, 2019