Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Setting up linked servers in Sql Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 6:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64
Ok, I give up. I have read about a thousand articles online, searched Google until my fingers bleed, and I cannot get a linked server to work.

It seems so simple. I have two Sql Server 2005 machines, running on 2K3 64bit, on a domain, and they are both running under the same domain account. I can't think of a more "standard" setup.

I go into Management Studio for server A, click "New Linked Server", put in the name of server B, and no matter what settings I try I cannot make it work! Argh! This shouldn't be hard. I remember doing this with 2000 and it was always easy.

I'm not using password logins, only domain accounts.

Can somebody provide a step-by-step to make this work? I've tried everything the Google has to offer, so there must be some secret magic that nobody has ever written about.
Post #574262
Posted Tuesday, September 23, 2008 7:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Are you setting it to "SQL Server" or leaving it on "Other Data Source"?

Are you setting the security credentials? If so, what settings are you using?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #574302
Posted Tuesday, September 23, 2008 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64
The latest thing I have tried, which is at least giving me different error messages, is this:

EXEC sp_addlinkedserver
@server='statler',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='np:statler',
@provstr='Integrated Security=SSPI'

-- Then I try this:
select net_transport, auth_scheme from statler.master.sys.dm_exec_connections where session_id=@@spid

/*

Getting closer, but still fails:

OLE DB provider "SQLNCLI" for linked server "statler" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "statler" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI" for linked server "statler" returned message "Invalid connection string attribute".

*/

This might have something to do with enabling names pipes, but I can connect via sqlcmd from server A to server B like this:

WALDORF :) Sqlcmd.exe /E /Snp:statler

If I don't used named pipes, and just do:

New Linked Server
Server Type: SqlServer
Security: be made using the current login's security context

I get this:

Login failed for user NT AUTHORITY\ANONYMOUS LOGIN
Post #574310
Posted Tuesday, September 23, 2008 7:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:44 AM
Points: 770, Visits: 1,597
Hi Ericzbeard
Try this

** Open SQLMS
** Connect to ServerA on Object Explorer
** Expand Server, Click on Server Object and choose Linked Servers
** Right Click on Linked Server and Choose New Linked Server
** On Linked Server -- Type ServerB
** On ServerType Choose SQL Server
** On RightHand Pane Choose Security
** You can see For Login Not Defined in list above,Connection will ( in this Choose the Last option, Be made using this security Contexxt)
** Provide username and Password ( Use sa for testing since you are getting errors) then you can change it.

Click ok, this will work

Let me know if you got any errors

Cheers

:)
Post #574340
Posted Tuesday, September 23, 2008 7:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64
Sql logins are disabled. I would rather not have to enable the sa account if I can avoid it.
Post #574346
Posted Tuesday, September 23, 2008 7:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:44 AM
Points: 770, Visits: 1,597
you can use any login that have administrator previliges, linked servers can only be created by logins that have admin previliges

Cheers
:)
Post #574355
Posted Tuesday, September 23, 2008 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64
I'm logged in as the domain administrator.
Post #574506
Posted Wednesday, September 24, 2008 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 5:50 AM
Points: 5, Visits: 7
I'm having exactly the same problem - I'll be watching this thread and will let you know if I work it out !

Steve
Post #575056
Posted Wednesday, September 24, 2008 5:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:44 AM
Points: 770, Visits: 1,597
HI Eric and Steve
Try to connect to your SQL server from any other machines, If you cant login then then there are some issues with the logins.

Check SQL Confugration for remote connections, wether it is enabled or disabled??

Let me know what problems you are facing after this checks :)

Cheers

:)
Post #575071
Posted Wednesday, September 24, 2008 5:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 5:50 AM
Points: 5, Visits: 7
Ok I have enabled the sa account on my target server (the one I want to link too), switched the server to mixed security and reset the sa password so to be sure I know what it is. I have specified that connections be made using the security context of sa with the correct password. This now works but isn't a solution as I don't want to use sa or have my target server running mixed security.

If I change the id/password it is using to a domain user, ACUTEC\Steve - which is my logon that I use to connected to the server in Management Studio - I get an error 'Login failed for user 'ACUTEC\Steve', error number is 18456. Is it taking this as a SQL logon rather than integrated security perhaps?

If I tell it to use the logins current security context - I am logged on as ACUTEC\Steve and doing this in Management Studio connected to both servers using Windows Authentication - I get thet same error but stating the account is NY AUTHORITY\ANONYMOUS LOGON


Post #575078
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse