SQL Server connection from SSIS Package

Harsha 1 Reputation point
2022-08-24T04:31:19.673+00:00

We are trying to connect to a SQL DB from an SSIS package. We were asked to use Service account (AAD Account) for the connection. We are prohibited from logging into a machine/VM using service accounts (login restricted).

The only other option we have is using a SQL ID. I do not want to use SQL ID as it can be a channel for breach.

Is there any way we can establish the connection? Is there a way I can use Service account credentials without logging onto the machine/VM?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,948 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 20,076 Reputation points
    2022-08-24T05:41:59.573+00:00

    Hi @Harsha ，
    As far as I know, AAD account is only used to connect to Azure SQL Database.

    If you want to connect to SQL DB on pre in SSIS package, you could only use SQL Server authentication or Windows authentication like shown below.

    Could you please have a check of the issue so that we could do more analysis.
    234296-image.png

  2. Yitzhak Khabinsky 20,546 Reputation points
    2022-08-24T14:55:27.19+00:00

    Hi @Harsha ,

    It looks like that SSIS package is using Native OLEDB Provider 11.0
    It is deprecated.

    I would suggest switching to the Microsoft OLE DB Driver for SQL Server.
    Check it out here: oledb-driver-for-sql-server

    Microsoft OLE DB Driver can use Azure Active Directory authentication: Azure Active Directory authentication
    Please see below.

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