Share via

SSIS connect to azure sql database with mfa

rajanisqldev-42 221 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 26,586 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

    1 person found this answer helpful.

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 29,542 Reputation points Microsoft Employee Moderator
    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 221 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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.