question

AzTechie-1779 avatar image
0 Votes"
AzTechie-1779 asked PRADEEPCHEEKATLA-MSFT commented

Azure SSIS IR using Windows and AAD authentication on same Execute SSIS Package activity ?

With the same Execute SSIS Package activity of Azure SSIS IR , can we connect to on-prem SQL using Windows Authentication and connect to Azure SQL database using Azure AD Authentication (AAD Authetication)

azure-data-factoryazure-sql-database
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AzTechie-1779,

Welcome to the Microsoft Q&A platform.

Unfortunately, it is not possible to use Windows Authentication for on-premise SQL and Azure AD Authentication for Azure SQL within same Execute SSIS package activity. You will have to use separate activities for each.

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

0 Votes 0 ·
AzTechie-1779 avatar image AzTechie-1779 PRADEEPCHEEKATLA-MSFT ·

Hi @PRADEEPCHEEKATLA-MSFT

98322-image.png

can we not use Windows authentication for the SSIS Package and under Connection Manager of the "Execute SSIS Package" can we not specify Managed Identity authentication using OLEDB driver ?



Is there any link or article , which says its not possible . Why is it not possible . Any logical reason ?


0 Votes 0 ·
image.png (18.9 KiB)
image.png (52.3 KiB)

Hello @AzTechie-1779,

Apologize for the delay in response and sorry for not being clear in my initial response.

When you design your package, you decide which data stores to connect Win Auth/AAD Auth via their connection string in connection managers.

For Win Auth, you also need to configure this feature on SSIS IR, see https://docs.microsoft.com/azure/data-factory/ssis-azure-connect-with-windows-auth.

For AAD Auth, you also need to configure ADF managed identity accordingly, see https://docs.microsoft.com/azure/data-factory/enable-aad-authentication-azure-ssis-ir#run-ssis-packages-with-managed-identity-authentication.

Once you properly configure ADF/SSIS IR, you can run your package as Execute SSIS Package activity in ADF pipeline to perform ETL using both authentication methods, e.g. copying & transforming data from on-prem SQL Server connected via Win Auth to SQL DB/MI connected via AAD Auth.

Hope this helps. Do let us know if you any further queries.

0 Votes 0 ·
Show more comments

0 Answers