SSIS Active Directory Integrated

3
votes

I am running some SSIS packages locally that need to insert data into an Azure v12 SQL Database. For authentication we would like to use "Active Directory Integrated". This works if I connect via SQL Management Studio so it is setup right. However my SSIS package is giving me an error of

enter image description here

My connection string looks like thisenter image description here

So note the authentication param which I think is causing the issue.

Any ideas onusing this type of security with SSIS? The SSIS package is being run from a SQL 2016 server.

2
sql-serverazuressisactive-directory
We have the same issue. Were you able to find a workaround? - John Bowyer
We actually reverted to an on premise solution as this security just wasn't mature enough. The only thing that's seems to know of the is SSMS. One option is to use a SQL account for SSIS. The problem was nothing like excel or power BI could connect without setting up things like DSN in ODBC. The issues outweighed the benefits at the moment but I'm sure that will change. - DasDave

2 Answers

0
votes

SSIS OLE DB connections does not support Azure integrated security yet, and it's very undocumented. You have to workaround by creating an SQL login with password Or use ADO.NET connection

0
votes

If you have an OLEDB connection using SQLNativeClient, you can make a change to allow the use of Active Directory. First download Microsoft® OLE DB Driver 18 for SQL Server® https://www.microsoft.com/en-us/download/details.aspx?id=56730

Then view the SSIS package in code and change the connection string to 'Provider=MSOLEDBSQL;Authentication=ActiveDirectoryIntegrated;Encrypt=yes'

The UI will not display the AD options, but it will work for an Execute SQL Task, not yet managed to get it to work for and OLE DB source.

I also managed to get this working for an OLE DB data source by setting the DelayValidation True on the Data Flow.