Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pete_N
Posting Yak Master
181 Posts |
 Posted - 2014-08-12 : 06:02:36
|
| Server A SQL2008R2Server B SQL2014SQL Authentication User MyUser ( example ) Server A & B are linked. Server B holds a SP that executes a SSIS packageIf I log into SERVERA as MyUser using SQL Manager, and execute a query 'exec ServerB.Dtaabase.SP' everything runs fine and the SSIS package is executedIf I try and call the SP from a vb application with a connection string using MyUser and password, it gets to the SP on ServerB but doesn't execute the SSIS package. I know it is calling the SP on ServerB, but cannot get the error reason back for why it wont execute the SSIS. I have tied several things including writing the error to a table on ServerB, but gate an access error on the table. I have given MyUser full permissions on ServerB and still get an error. I have tried in the SP to execute as a domain admin and still get the same error. I think the problem is to do with the linked servers, but don't know how to proceed. The SQL server logs on both servers do not show anything . I have spent an entire day on this posting on the SSIS forum, but no joy. The error i get is 'The Server principal "xx" is not able to access the database "SSISErrorsDB" under the current security context. |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-12 : 06:45:33
|
| Are both SQL instances on the same servers or different, could be a firewall issue and port blocking, try disabling the firewall on B temporarily to cross that off. Also, have you tried setting MyUser in the SSIS package catalogue thru MStudio connecting to the SSIS package for permissions. Have you included the full linked server path in your query including instance name.Firewall article discusses ports.http://msdn.microsoft.com/en-gb/library/cc646023.aspxWe are the creators of our own reality! |
 |
|
|
|
|
|
Linked Server Issue