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!

more ▼

asked Mar 27, 2011 at 06:52 PM in Default

avatar image

11 1 1 1

Is there any specific error that you are getting?

Mar 27, 2011 at 07:12 PM DaniSQL

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

Mar 27, 2011 at 07:32 PM Adma

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

Mar 28, 2011 at 02:08 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Mar 28, 2011 at 02:09 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 28, 2011 at 09:02 AM

avatar image

1.6k 55 59 62

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, 2011 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, 2011 at 08:04 PM Adma
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 30, 2011 at 08:18 PM

avatar image

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, 2011 at 08:21 PM Adma
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 27, 2011 at 06:52 PM

Seen: 1977 times

Last Updated: Mar 27, 2011 at 06:52 PM

Copyright 2018 Redgate Software. Privacy Policy