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.
2,449 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,406 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 24,911 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

    0 comments No comments