Execute SPROC on SQL2008 R2 from SQL2005 via linked Server

Question

question

Adma asked Mar 27, '11

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-2008 stored-procedures linked-server

3

10 |1200

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

DaniSQL commented · Mar 27, 2011 at 07:12 PM

Is there any specific error that you are getting?

0 ·

Adma commented · Mar 27, 2011 at 07:32 PM

Msg 18456, Level 14, State 1, Line 1 Login failed for user 'user1'

0 ·

ThomasRushton ♦♦ commented · Mar 28, 2011 at 02:08 AM

What's the query you're using / trying to use?

0 ·

Answer 1 · 2011-03-28T02:09:37Z

Fatherjack answered Mar 28, '11

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

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

Answer 2 · 2011-03-28T09:02:24Z

Leo answered Mar 28, '11

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

10 |1200

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

Adma commented · Mar 28, 2011 at 06:02 PM

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 ·

Adma commented · Mar 30, 2011 at 08:04 PM

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 ·

Answer 3 · 2011-03-30T20:18:30Z

Adma answered Mar 30, '11

CANCEL THIS Question!!..vendor issue, not ours!

1

10 |1200

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

Adma commented · Mar 30, 2011 at 08:21 PM

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 ·

question