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!

asked Mar 27 '11 at 06:52 PM in Default

Adma
11 ● 1 ● 1 ● 1

Is there any specific error that you are getting?

Mar 27 '11 at 07:12 PM DaniSQL

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

Mar 27 '11 at 07:32 PM Adma

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

Mar 28 '11 at 02:08 AM ThomasRushton ♦

(comments are locked)

3 answers:

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

answered Mar 30 '11 at 08:18 PM

Adma
11 ● 1 ● 1 ● 1

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!

Mar 30 '11 at 08:21 PM Adma

(comments are locked)

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.

answered Mar 28 '11 at 09:02 AM

Leo
1.6k ● 48 ● 54 ● 58

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.

Mar 28 '11 at 06:02 PM Adma

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.

Mar 30 '11 at 08:04 PM Adma

(comments are locked)

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

answered Mar 28 '11 at 02:09 AM