2

I am trying to execute my SSIS package from Azure enables SSIS project in VS 2019. SSIS package needs to connect with Azure SQL database using Active Directory Interactive authentication.

Test connection succeeded with the connection string provided and I am also able to parse the query in Execute SQL task.

But when the package is run, the task fails with an error

Failed to acquire connection

I am not able to connect via Script task also. It fails with an error

DTS.Runtime - Cannot find an authentication provider for 'ActiveDirectoryInteractive'

Has anyone succeeded connecting to Azure SQL from SSIS & SQL Server Agent job. Is SQL Server authentication the only way?

Improve this question
0

1 Answer 1

Reset to default
1

Failed to acquire connection | DTS.Runtime - Cannot find an authentication provider for 'ActiveDirectoryInteractive'

ActiveDirectoryInteractive (i.e, authentication=ActiveDirectoryInteractive) can be used to connect to an Azure SQL Database/ Synapse Analytics using an interactive authentication flow (multi-factor authentication) which uses System.Data.SqlClient

It will fail because MFA interactive is not supported by SqlClient provider.

Please note that the System.Data.SqlClient driver does not support the full range of Azure AD authentication methods. If none of the supported methods works in your environment, using SQL authentication may be the only option.

Ensure that you have at least the .NET Framework 4.7.2 version.

Or Try with ODBC driver connection.

Check this thread, c# - How to connect to a database using Active directory Login and MultiFactor Authentication (MFA) - Stack Overflow

For more details, ActiveDirectoryInteractive connects to SQL - Azure SQL Database | Microsoft Docs

Raise a support request if issue still remains.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.