Can you do Azure AD Authentication with SSIS running in a Virtual Machine on the cloud? Short answer – No. In case if you want to know why it is so, read along to understand more.
Azure AD Authentication with SSIS
Azure AD issues tokens and centrally managed identities for users authenticating against it. Now, for the traditional SQL Server on-premises services like Integration Services (SSIS), it either supports AD or SQL Auth (Basic Authentication). It cannot use currently authentication against identity providers which issues tokens.
See the following options in the following screenshot:
Even though I was able to authenticate with a web app entry in AAD, I do not see a way to get around the Azure SQL DB connection. See below:
With Microsoft’s Azure Feature Pack, it gives you the following Connection Managers.
- Azure Storage Connection Manager
- Azure Subscription Connection Manager
- Azure Data Lake Store Connection Manager
- Azure Resource Manager Connection Manager
- Azure HDInsight Connection Manager
So, in a nutshell, you cannot have a source or destination with SQL Database using an AAD authentication method at the time of writing this post. As an alternative, the SSIS integration for Azure lets you create a Data Factory “Managed Service Runtime,” and it is adequately documented on the Microsoft website.
We hope this post was helpful. Feel free to send us suggestions in the comment section below.