May 8, 2014 at 9:49 am
i researched my issue and found the below link. I don't want to use a sql job. I'd rather now hard code the sql login the in the proc either. Does anyone know if this issue has been addressed by microsoft or ideas on what options i have here.
Issue:
The problem is that integration services as of the 2012 release does not support credentials delegation which means even though the SSIs package is running under the right windows account it does not pass that account when it tries to access the database or file system as they say at the end of this topic.
http://waheedrous.wordpress.com/2013/12/23/ssis-2012-nt-authorityanonymous-logon/
May 9, 2014 at 2:31 pm
I was never able to resolve this myself and I currently use jobs. It's not a big deal in this case. I have the app sit in a loop checking to see if the job has completed before moving on.
Mike
June 18, 2014 at 8:42 am
In my experience, this almost always has to do with linked servers. You need to pay attention to the server that is "executing" the job. More often than not, a query using a connection to a linked server is executing a JOIN that refers back to the server the job is running on.
Bonus: When you run this in dev mode in BI, it runs fine, it only fails as a job. Here's why......
Example:
The SQL Job is executing on Server_A
The Execute SQL command is running a query that is using a connection on a linked server (Server_B) (look at the connection in the task, see if it's a different server), finally, the QUERY in that Execute SQL command is SELECTING (or updating or whatever) from a table *local to the connection* ie
SELECT *
FROM TABLE t <----- Is considered a local table because the connection is ON this server according to the Connection Manager
INNER JOIN SERVER_A.database.dbo.TABLE b <----- Is a LINKED server between A and B
ON t.COL = b.COL
The *problem* is that Server A is running the Job, so what is occuring is a full circle loop. A executes, LINKS to B to execute the code, which tries to LINK to A again. It's never obvious, but this "double hop" happens all the time and is sneaky. You can go through all sorts of Kerbos configuration stuff, or just change the server the Query is executing on, and reverse the join. That will fix it.
June 19, 2014 at 3:50 pm
Sounds like a kerberos issue with double-hop and integrated security. Has the SQL instance been renamed or possibly removed from a Cluster? Review your SPN's on the domain and verify they look correct.
May 18, 2017 at 1:46 pm
Definitely an issue with Kerberos.
I had the same problem and after doing some troubleshooting, found out that the package would run successfully if ran from an SSMS installed on the server, but would fail to run if ran from an SSMS client outside the server. This is a double hop issue.
Going to try out Microsoft's Kerberos config manager and see if that fixes the issue.
https://www.microsoft.com/en-us/download/details.aspx?id=39046
March 15, 2018 at 3:43 pm
Did y
Max_Null - Thursday, May 18, 2017 1:46 PMDefinitely an issue with Kerberos.
I had the same problem and after doing some troubleshooting, found out that the package would run successfully if ran from an SSMS installed on the server, but would fail to run if ran from an SSMS client outside the server. This is a double hop issue.Going to try out Microsoft's Kerberos config manager and see if that fixes the issue.
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Did you fix the issue? Can you share how you solved? We DID set up the Kerberos and allowed delegation of the server machine and also the SQL service account. But still get the same error? Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply