question

Adma avatar image
Adma asked

Execute SPROC on SQL2008 R2 from SQL2005 via linked Server

I cannot execute a SPROC through a linked server from SQL2005 to SQL2008, i have check permissions, as well as account being used to connect, I can select on tables in the database, but cannot run stored procedures, I have checked RPC / RPC out and made sure EXEC permissions are available on the server. Any help on why this might be happening!
sql-server-2008stored-procedureslinked-server
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is there any specific error that you are getting?
0 Likes 0 ·
Msg 18456, Level 14, State 1, Line 1 Login failed for user 'user1'
0 Likes 0 ·
What's the query you're using / trying to use?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Can you connect to the desired SQL instance via SSMS using the "user1" credentials? Just wondering if security settings at the linked server are incorrect/have changed
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
As Fatherjack said you should try to log in as 'user1' but you should have a look in Link Server Setting under Security how you setup the 'Local Server Login to Remote Server Login Mapping' if you can login to SSMS.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If i try to do EXEC SQL1.reservedb.dbo.spAvailability @days=1 The query fails with the error message above if i do SELECT * FROM SQL1.reservedb.dbo.tblRes i get results returning fine. those two statements are launched from SQL3 which is SQL2005 box and the linked server is setup with the rights for the user SQL1 is a SQl2008R2 box I have looked at the mapping, and i have tried impersonate as well as mapping to the same name on SQL1. I can run SPROC's fine on SQL1 using the same login.
0 Likes 0 ·
More information to this issue, if i do it from another sql 2008 r2 box i still get the error message, but a confusing thing is that if i exclude the parameter for the sproc , i get an error saying that it expects the parameter to be supplied, so there is some connectivity going on.
0 Likes 0 ·
Adma avatar image
Adma answered
CANCEL THIS Question!!..vendor issue, not ours!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sorry...and thanks to everyone that was helping. the stored procedure was calling another stored procedure on another server but the linked server was incorrect for the stored procedure in question! ARGGGGH!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.