June 23, 2016 at 1:49 pm
I am NOT a SQL DBA, but am trying to resolve a possible permissions issue. We have a two node SQL 2012 failover cluster and are having the following issue. I need assistance in troubleshooting this for the SQL DBA, since he is convinced that it is a problem with the Windows credentials. I'm copying from the email thread, truncating and modifying info where necessary for security purposes. Please reply back with questions for clarification or with information to help point me towards a resolution. Thanks in advance for your patience..... Copied text begins below.....
Somewhere there is a difference between the permissions on the two nodes that make up (the cluster)
I have a SQL Server Agent job that executes every morning. It pulls data from a database on (the cluster), writes it to a local text file (E:\ApplicationData\datalocation1 and E:\ApplicationData\datalocation1\Archive ) , then calls the coreftp exe and FTP’s the data to an external stakeholder.
If (node2) is active, the job has to run using a SQL credential named JA. If I use the SQL Server Agent Service Account (kdhe\accountname1) , it fails when trying to write the drive. It also fails while trying to execute
If (node1) is active, the reverse is true. The job has to run using the SQL Server Agent Service Account (kdhe\accountname1). If I use the SQL credential named JA, it fails to write the drive. It also fails while trying to execute.
Sample of error message
Message
Executed as user: KDHE\accountname1. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 4:40:00 AM Error: 2016-04-06 04:40:08.91 Code: 0xC0029151 Source: FTP data to 'target location' Execute Process Task Description: In Executing "C:\Program Files\CoreFTP\coreftp.exe" "-s -O -site 'target location' -u E:\ApplicationData\datalocation1\*.txt -p /users/ftp_id" at "C:\Program Files\CoreFTP", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:40:00 AM Finished: 4:40:08 AM Elapsed: 8.469 seconds. The package execution failed. The step failed
June 23, 2016 at 2:56 pm
Additional Information:
The "SQL Server Agent (MSSQLSERVER)" service running on (node1) is running as "i12SqlAgent@fullyqualifieddomainname"
Node1 is the successful one with this account.
The "SQL Server Agent (MSSQLSERVER)" service running on (node2) is running as "Domain\i12SqlAgent"
Node2 in unsuccessful with this account.
I don't have access to history as to why the different names were used for the services in question. What is also strange is when I 'browse' to do an Active Directory lookup, the account name does not show up. However, when I go in to Active Directory and search, the i12SqlAgent account is present. Both computers are members of the appropriate domain.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply