SSIS connect to azure sql database with mfa

rajanisqldev-42 206 Reputation points
2022-09-29T16:59:55.293+00:00

Hi all,

I have azure sql db. I am able to connect in SSMS using Azure Active Directory - Universal with MFA

I need to connect in SSDT but I can't see this authentication method.

I have tried both ADO.Net source and OLEDB Source but I can't see this. I am using VS 2019.

How can I connect using this authentication method

Thanks in advance

Azure SQL Database
0 comments
Accepted answer
  1. Yitzhak Khabinsky 24,946 Reputation points
    2022-10-03T14:10:38.457+00:00

    Hi @rajanisqldev-42 ,

    You need to install and use in SSIS the following driver: Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
    All other OLEDB providers and drivers are deprecated.
    You can read about here: oledb-driver-for-sql-server

    Check it out what connectivity it supports.

    247068-microsoft-ole-db-driver-for-sql-server.png

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Reister Hansjoerg 6 Reputation points
    2023-11-28T15:39:38.85+00:00

    Doesn't work in VS 2022. Get error message "Invalid value for key 'authentication'." when trying to create an ADO.NET source and try to select a table.

    1 person found this answer helpful.
    0 comments
  2. GeethaThatipatri-MSFT 27,567 Reputation points Microsoft Employee
    2022-09-30T17:58:17.78+00:00

    Hi, @rajanisqldev-42 Thanks for posting your question in the Microsoft Q&A forum.

    Definitely supports MFA (it’s oddly named “Active Directory Interactive Authentication”): but you should be able to connect with Active Directory Interactive Authentication.

    246575-image.png

    Regards
    Geetha

  3. rajanisqldev-42 206 Reputation points
    2022-10-04T09:28:52.27+00:00

    Hi @GeethaThatipatri-MSFT

    Sorry for another post,

    When I ran the SSIS package, it is prompting to enter password. How does it work if I push to production and schedule the job?

    Thanks