x

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

Adma gravatar image

Adma
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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

(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

Leo gravatar image

Leo
1.6k 54 56 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, 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

Adma gravatar image

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, 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1833
x407
x106

asked: Mar 27, 2011 at 06:52 PM

Seen: 1677 times

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