Azure SSIS-IR will not start after setting SQL Managed Instance to AAD Only

Question

Hi,

The SSIS-IR runs set to AAD ok and will start up. But on switching the SQL Managed Instance (SQLMI) to AAD Only the SSIS-IR will not start and reports:

*Error 1: Last operation 'Start' get the status 'Failed'. Error code: AzureSqlConnectionFailure Error message: Failed to connect to Azure SQL DB server due to sql error '18456', message: Login failed for user '***'. Reason: Azure Active Directory only authentication is enabled. Please contact your system administrator. Please add your ADF MSI into an AAD group with access permissions to your catalog database server. Activity ID: 422*

The ADF MSI has been added as Login to the SQLMI and a User created in the SSISDB with db_owner membership. This is as per Microsoft's instructions and scripts and these were created OK in SSMS.

Scripts:
USE master
--DROP LOGIN [Identity]
CREATE LOGIN [Identity] FROM EXTERNAL PROVIDER
ALTER SERVER ROLE [dbcreator] ADD MEMBER [Identity]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [Identity]

USE SSISDB
--DROP USER [Identity]
CREATE USER [Identity] FOR LOGIN [Identity] WITH DEFAULT_SCHEMA = dbo
ALTER ROLE db_owner ADD MEMBER [Identity]

In summary, if using mixed authentication in Azure SQLMI, the SSIS-IR starts ok but does not if the SQLMI switched in the Azure Portal to 'AAD Only'. There seems to nothing wrong with the login of the ADF MSI (using same name as the ADF itself).

Has anyone come across this issue?

Thanks

Answer 1

Possibly just found the answer to this, it is not the ADF managed identity account that fails to login, it is 2 x standard SQL accounts set up on installation, one of which is AzureIntegrationServiceWorker.

*https://learn.microsoft.com/en-us/azure/data-factory/enable-aad-authentication-azure-ssis-ir

*In this scenario, Azure AD authentication with the specified system/user-assigned managed identity for your ADF is only used in the provisioning and subsequent starting operations of your Azure-SSIS IR that will in turn provision and or connect to SSISDB. For SSIS package executions, your Azure-SSIS IR will still connect to SSISDB to fetch packages using SQL authentication with fully managed accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) that are created during SSISDB provisioning.**

It seems SQL Authorisation is still needed for now. Switching the SQL MI to 'AAD only' - disables the '2 SQL Authorisation out of the box accounts' that are required to run packages set up in the SSISDB through Project deployment.

So it looks like you can 'Enable Azure Active Directory authentication for Azure-SSIS integration runtime' but **not switch the **SQL MI itself to AAD only**** because the 2 x fully managed SQL Authorisation accounts are required for SSIS-IR Start up.

Thanks