SSIS package execution failure when SSISDB is configured with AlwaysOn

Hello all,

Recently I worked on an interesting issue with SSIS package execution issue. In this blog, I am sharing the steps taken to isolate the cause and fix it.

Issue Description:

SSIS Package execution fails when it is executed from Integration Services catalog from the secondary replica node using the listener name. No issue reported when the package is executed from the primary replica.

To demonstrate the issue, I am using SQL 2012 instances on Windows 2012 R2 Server.

SQL standalone instance on Node 1: Node1\SQL1
SQL standalone Instance on Node 2: Node2\SQL2
AlwaysOn Availability Group: SSISDBAG
AlwaysOn Listener: SSISDBListener
SQL Service Account: Contoso\Administrator

The package is executing file system task, and it is moving test.txt file from \\Node1\data to \\Node1\Data\Moved folder.

The existing AlwaysOn setup, Node1\SQL1,is the primary replica, and Node2\SQL2 is the secondary replica.

Figure 1: AlwaysOn configuration

The package is deployed to path: /SSISDB/SSISPackage/SSISPackage.

Now, let’s execute the package on Node1. The package execution is successful.

Figure 2. Successful package execution when connected using Node1\SQL1

Let’s connect using the listener name (SSISDBListener). The package executes fine.

Figure 3: Successful Package Execution using Listener Name

This is where things get a bit tricky. Let’s failover the AlwaysOn group to the secondary replica hosted on Node2\SQL2.

Figure 4: AlwaysOn group failover

Node2\SQL2 is the new primary replica.

Figure 5: Node2\SQL 2 is the new Primary Replica

Now, let’s log on to Node2 machine and execute the package. You will get error message:

TITLE: Microsoft SQL Server Management Studio
Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft SQL Server, Error: 15581)

Figure 6. SSISDB database decryption error

To fix the error, execute the following queries:

OPEN master Key decryption by password = 'Password' -- Password used when creating SSISDB
ALTER Master Key ADD encryption by Service Master Key

Figure 7: Decrypting SSIDDB database

After decrypting the SSISDB database, execute the package. As per the screenshot, no issues here as well. Even with the listener name, the package execution should be successful.

Figure 8: Successful Execution of Package on new replica locally

Now to reproduce the issue log on to Node 1 machine and connect to Node2\SQL2 instance and execute the package.

Figure 9: Package Execution failure when executed from Node1 using listener

Figure 10: Package Execution failure when executed from Node1

Package execution fails with error:

File System Task: Error: An error occurred with the following error message: "Access to the path '\\Node1\Data\Test.txt' is denied.".

As per the error, it looks like an permission issue, which seems interesting.

Using the same account (Contoso\Administrator) on Node2, package execution was successful.

To confirm the permission issue, let’s take Process Monitor on Node 2 and filter the data for ACCESS DENIED.

Figure 11: Access Denied Messages in process Monitor

Below screenshot confirms that Contoso\Administrator is the account which is delegated to access the file test.txt.

Figure 12: Account which is having Access Denied

Let us enable Audit security logging using local security policy:

Figure 13: Object level Auditing

Figure 14: Object Level Auditing

Reproduce the package execution issue once again, and review the security logs in Windows Event Viewer.

Figure 15: Security Audit Output

Figure 16: Security Audit shows Anonymous Logon

ANONYMOUS LOGON confirms that SQL Server is not able to delegate the account “CONTOSO\Administrator” to access the file stored at \\Node1\Data\test.txt.

There is no issue reported when the package is executed from Node2 but issue is seen only from Node1, which indicates that Single HOP access to the File is working and Double HOP is failing.

To check if the SQL Server is authenticating over Kerberos:

Node 1:
select distinct(auth_scheme) from sys.dm_exec_connections

select distinct(auth_scheme) from sys.dm_exec_connections

SQL Server is authenticating over Kerberos, which indicates SQL Service Principle Names(SPNs) are registered.

From Active Directory, let’s check the delegation setting on “Contoso\Administrator”. It’s set to “Do not trust this user for Delegation”.

Figure 17: Delegation setting of Contoso\Administrator

Change the above setting to “Trust this user for delegation to any service (Kerberos Only) ”.

The next step is to purge the existing Kerberos tickets using Klist purge.

Figure 18: Klist Purge

Reopen the management studio on Node 1 and package execution is successful this time.

Figure 19: Successful Execution of package from Node1

Let’s check if we are able to execute the package using listener name as well or not. What’s interesting is that, package execution still fails with the same error.

Figure 20: Package failure through Listener

To list the SPNs registered with Contoso\Administrator account:

C:\Users\Administrator.CONTOSO>setspn -L contoso\administrator
Registered ServicePrincipalNames for CN=Administrator,CN=Users,DC=Contoso,DC=com:

Figure 21: SPN’s registered for Contoso\Administrator

I registered the SPNs for the listener using:

setspn -A mssqlsvc/ contoso\administrator
setspn -A mssqlsvc/ contoso\administrator

To confirm if the SPN change is reflected or not:

Registered ServicePrincipalNames for CN=Administrator,CN=Users,DC=Contoso,DC=com:

After implementing the above SPNs, perform Klist purge and reopen the Management Studio. And this time, the package execution is successful with listener connection as well.

Figure 22: Successful execution through Listener

I hope the guidelines address the four listed scenarios help you.

Please share your feedback, questions and/or suggestions.


Don Castelino | SQL Server Support Team | CSS – Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.