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.
- Azure Database integrated authentication with SSIS - February 27, 2019
Can we accomplish the connection using SSDT 2015? Our SSIS is 2016 version. How to make sure we get the right ADASQL.DLL that works for SSDT 2015?
Hi Sireesha, you would need to install SSMS 17.7 or higher. It will install the correct ADALSQL.DLL version. We didn’t find the library outside the SSMS. Actually you can use OLEDB drivers: https://blogs.msdn.microsoft.com/sqlnativeclient/2019/02/22/ole-db-driver-18-2-for-sql-server-released/
Regards,
Thanks. But still my ssis package is 2016 version and I open it from Visual Studio 2015. Do I need to select anything specific from the drivers list in the connection manager? Do I need to locate the ADASQL.DLL file upon installation of SSMS 17.7?
Anyways, When I tried to install the DLL from the microsoft download page mentioned in the article, I couldn’t because I got the prompt that I have a higher version already installed.
Let me try both SSMS 17.7 and the OLEDB driver and get back. I hope all these multiple version installation don’t ruin my setup (because I need to work with Visual Studio 2015 and SSMS 2016 for my work items). 🙂
I’m not able to make this work. Can you please elaborate on the Provider that I need to select from ADO.Net Connection? I’ve selected “.NET Providers\SQL Client Data Providers”. I’m using Visual Studio 2015. I have installed SSMS 1.8 on my machine and this didn’t help. I don’t understand how to reference the required ADASQL.DLL in the connection. There is no option to provide the full connection string either. I’ve also installed the OLEDB driver you mentioned above. And again I’m not sure how that’s supposed to help. What’s the SSIS IDE (SSDT) version you are using in your post?