Introduction:
In this article, we will delve into the topic of setting up Azure Data Factory SSIS IR using the Express virtual network injection method. This method is an efficient way to set up an SSIS IR, especially when you're working with smaller workloads. We will cover the necessary steps, prerequisites, and best practices for using this method. So, whether you're just starting out with Azure Data Factory or you're an experienced user, this article will provide valuable insights and remarks s on how to set up your SSIS IR using the Express virtual network injection method.
Use Case:
This diagram shows the required connections for your Azure-SSIS IR:
The following outbound traffic should be allowed on the SSIS Subnet NSG for proper setup:
Remarks:
- In case the VNET has custom DNS servers, then you will need to configure the Custom DNS for SSIS IR as follows:
You need to configure it with a standard custom setup following these steps:
- Download a custom setup script (main.cmd) + its associated file (setupdnsserver.ps1).
- Replace “your-dns-server-ip” in main.cmd with the IP address of your own DNS server.
- Upload main.cmd + setupdnsserver.ps1 into your own Azure Storage blob container for standard custom setup and enter its SAS URI when provisioning Azure-SSIS IR, see the Customizing Azure-SSIS IR article.
If the DNS configuration is not properly set up, you may encounter the following error when provisioning or starting the SSIS IR instance:
Error code: AzureSqlConnectionFailure
Error message: Failed to connect to Azure SQL DB server due to sql error '10060', message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
- To enable the connectivity from the SSIS IR subnet to Azure storage account, you will need to enable service endpoint for Microsoft.Storage on the SSIS subnet ( Delegated to Microsoft.Batch)
- The storage account container can be accessed via public or private connection by enable service endpoint on the SSIS subnet.
In case of private access, the delegated subnet (SSIS IR Subnet) can connect to an Azure storage account privately by using a Virtual Network (VNet) service endpoint. This allows the subnet to access the storage account over the Azure backbone network, rather than the public internet, providing increased security and reduced network latency. You will need to use the SSIS Subnet (delegated + service endpoint enabled), and then update the firewall settings on the storage account to allow traffic from the VNet. Once this is done, traffic from the delegated subnet to the storage account will be routed privately over the Azure backbone network.
More info: Configure Azure Storage firewalls and virtual networks | Microsoft Learn
If there is any misconfiguration, you may receive the following error:
[{"Code":"CustomSetupScriptBlobContainerInaccessible","NodeNumber":1,"Message":"Cannot access your Azure blob container for custom setup."}]
- You can use the SQL AAD authentication in the provisioning.
You will need to make Azure AD user account (AAD identity) to be made administrator of the server (Server Admin), user can be part of AAD group, you will need to create a contained user in Azure SQL Database representing the Azure AD group. More information can be found at: Enable Azure Active Directory authentication for Azure SSIS integration runtime - Azure Data Factory...
You may need to uncheck AAD only on SSISDB catalog SQL Server throughout the provisioning process.
Notes:
- 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.
- If you have already created your Azure-SSIS IR using SQL authentication, you cannot reconfigure it to use Azure AD authentication via PowerShell at this time, but you can do so via Azure portal/ADF app.
In case of any misconfiguration, you might encounter error like:
Provision catalog failed with sql error code [18456] and error message [Login failed for user '<token-identified principal>'. Please add your ADF MSI into an AAD group with access permissions to your catalog database server.]
18456 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.
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: XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
Additional References:
Further information can be found in the references:
Customize the setup for an Azure-SSIS Integration Runtime - Azure Data Factory | Microsoft Learn
Troubleshoot SSIS Integration Runtime management - Azure Data Factory | Microsoft Learn
AAD Auth Error - Login failed for user '<token-identified principal>' - Microsoft Community Hub
Azure SSIS-IR will not start after setting SQL Managed Instance to AAD Only - Microsoft Q&A
AAD Auth for SSIS IR · Issue #81162 · MicrosoftDocs/azure-docs · GitHub
We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.
Ahmed S. Mazrouh
Ram Varma
