SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Querying a remote server with replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bsteelio
Starting Member

3 Posts

Posted - 05/09/2008 :  15:06:03  Show Profile  Reply with Quote
Hi All,
I am calling a stored proc through a job that calls a second stored proc on a different server. Replication is setup between the two servers, both systems are set in Mixed auth mode and both server services start using the same domain account. The problem is the job fails calling the second stored proc with this message:

Login failed for user 'Administrator'. [SQLSTATE 28000] (Error 18456). The step failed.

I get the same message regardless of the user set to run the job or how I configure the Remote Server options. (Not using Linked Servers due to Replication setup prior to my up and commance. If that matters.)

Any ideas?

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/09/2008 :  15:54:13  Show Profile  Reply with Quote
Might work with Linked server
Go to Top of Page

bsteelio
Starting Member

3 Posts

Posted - 05/09/2008 :  16:24:27  Show Profile  Reply with Quote
Thanks for the reply!

However, since the Remote Server connection was established with replication, I get an "Already Exists" when trying to add it as a Link Server and a "Can not be deleted" when trying to delete it as a Remote Server.

It appears to be permissions related but changing the Remote Login Name\Local Login Name does not appear to change a thing except to break the replication.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 05/09/2008 :  16:29:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can still use a linked server, just use an alias name instead.

You might also try an openquery since you can specify the credentials in the query. You should try SQL authentication just in case.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

bsteelio
Starting Member

3 Posts

Posted - 05/09/2008 :  17:54:50  Show Profile  Reply with Quote
I never thought about an alias but that did it! Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000