SSIS packages executed through SQL Agent Job scheduler fails while connecting to Azure File Storage Path

  • Article

Recently we ran into an interesting scenario where we had a SSIS package that contained a script task and inside the script task, we were making connections to Azure File Storage and pulling the data from the file.

While running this SSIS package from Visual Studio – SQL Server Data Tools (SSDT) & after deploying the SSIS package under SSISDB catalog and running the package from SSISDB catalog the package executes as expected accessing the files and pulling the data without any issues. But when we configure this SSIS package to run under a SQL Agent Job, it doesn’t pull the expected data accessing the Azure File Storage.

By Azure File Storage, we are referring to File services available under your Azure Storage which you would connect using the https://xxxx.file.core.windows.net/ ,  here you can upload any files and access it from your machines by mapping it to a local drive (or) access the path directly by providing the storage credentials.

 

 

 

 

 

 

 

 

Fig 1: Azure File Storage – File Services

 

Fig 2: Network Mapped Drive – From My PC

 

Fig 3: Network Mapped Drive – Accessing the share

 

We noticed that the issue happens only when we are running the SSIS packages over a SQL Agent Job. The same package runs fine from command prompt using DTEXEC.exe utility (or) from SSISDB (or) from SSDT works fine without any issues.

In order to narrow-down the issue further, we had collected Process monitor traces for both the working and failing scenarios and compared the logs.

 

In the success scenario: [Running the package from the Visual Studio – SSDT]

Fig 4: Success scenario

We are able to see that the access result to Azure File Storage wiz. \\XXXX.file.core.windows.net\testssis\ is returned as SUCCESS.

 

In the Failure Scenario: [Running the package from SQL Agent Job]

 

Fig 5: Failure scenario

We are able to see that the access result to Azure File Storage wiz. \\XXXX.file.core.windows.net\testssis\ is returned as LOGON FAILURE.

 

After spending a significant time, we understood that we need to take care of the following 2 things to resolve this issue.

  1. We need to configure and store the Azure File Storage access credentials in the Windows Credential Manager in advance.

You can do this using the Windows Control Panel à Credential Manager à Windows Credential and Add a Windows credential. (or) Use the below command on an elevated command prompt window to store the windows credential.

Syntax:                 cmdkey /add:<yourstorageaccountname>.file.core.windows.net /user:AZURE\<yourstorageaccountname> /pass:<YourStorageAccountKeyWhichEndsIn>

Example:             cmdkey /add:XXXXX.file.core.windows.net /user:AZURE\XXXX /pass:mwpqXXXXXXXXXwaFXXXX7wnXXXXXXXXXXX==

Fig 6: Credential Manager

This way you will be able to persist the connections across reboots and make it visible for the SQL Server Agent Job to use this.

and

  1. Instead of using the local path [Mapped network Drive Name] wiz. Z: for accessing the Azure File Storage, we need to stick onto the usage of UNC [Universal Naming Convention] path (like \\xxxx.file.core.windows.net\testssis) rather. [Recommended] in our package.

Once we added this credential under Credential Manager and modified our SSIS package to use UNC path, we were able to successfully establish the connection to the Azure File storage while running the SSIS packages through SQL Agent Job schedulers successfully.

 

Author:     Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Sarath Babu Chidipothu  – Support Escalation Engineer, SQL Server BI Developer team, Microsoft