Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Posted Thursday, May 8, 2014 9:49 AM


Group: General Forum Members
Last Login: Tuesday, February 17, 2015 12:59 PM
Points: 167, Visits: 435
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.

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.
Post #1568994
Posted Friday, May 9, 2014 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:20 PM
Points: 5, Visits: 26
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.

Post #1569452
Posted Wednesday, June 18, 2014 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 25, 2015 2:19 PM
Points: 4, Visits: 31
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......


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

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.

Post #1582967
Posted Thursday, June 19, 2014 3:50 PM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 212, Visits: 1,002
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.
Post #1584072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse