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:
USE [SSISDB]
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
auth_scheme
----------------------------------------
KERBEROS
NTLM
Node2:
select distinct(auth_scheme) from sys.dm_exec_connections
auth_scheme
----------------------------------------
KERBEROS
NTLM
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:
MSSQLSvc/Node2.Contoso.com:49471
MSSQLSvc/Node2.Contoso.com:SQL2
MSSQLSvc/Node1.Contoso.com:65335
MSSQLSvc/Node1.Contoso.com:SQL1
Figure 21: SPN’s registered for Contoso\Administrator
I registered the SPNs for the listener using:
setspn -A mssqlsvc/SSISDBListener.contoso.com:1433 contoso\administrator
setspn -A mssqlsvc/SSISDBListener.contoso.com contoso\administrator
To confirm if the SPN change is reflected or not:
Registered ServicePrincipalNames for CN=Administrator,CN=Users,DC=Contoso,DC=com:
mssqlsvc/SSISDBListener.contoso.com
mssqlsvc/SSISDBListener.contoso.com:1433
MSSQLSvc/Node2.Contoso.com:49471
MSSQLSvc/Node2.Contoso.com:SQL2
MSSQLSvc/Node1.Contoso.com:65335
MSSQLSvc/Node1.Contoso.com:SQL1
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.
Thanks,
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.