Setting up linked servers in Sql Server 2005

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

  • 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

  • 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

  • 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

    🙂

  • Sql logins are disabled. I would rather not have to enable the sa account if I can avoid it.

  • you can use any login that have administrator previliges, linked servers can only be created by logins that have admin previliges

    Cheers

    🙂

  • I'm logged in as the domain administrator.

  • I'm having exactly the same problem - I'll be watching this thread and will let you know if I work it out !

    Steve

  • 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

    🙂

  • 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

  • Thanks Crazy - I have checked and 'Allow remote connections to this server' is ticked with a 600 second timeout.

    I am running Management Studio on a PC workstation and can connect to both servers using any of the accounts I have been trying.

  • Ok, I have come to the conclusion that it is not possible to link two Sql Server 2005 servers using windows authentication. I had to resort to creating a Sql Login on the linked server.

    I have spent days on this, trying everything found by this google search:

    http://www.google.com/search?hl=en&q=login+failed+for+user+NT+AUTHORITY%5CANONYMOUS+LOGIN+linked+server

    and many many more searches.

    Oh well, at least it works now. I just feel like I'm not doing it "right".

  • hi Steve

    Has the NT user got admin rights, since Admins can only use Linked servers

    🙂

  • The SQL Logon for 'ACUTEC\Steve' has 'sysadmin' role on the target server and is also a member of Domain Admins in Active Directory.

  • I've had the same problem and it came down to how Windows authenticates accounts and the Kerberos protocol - maybe it applies to your situation too.

    My brain would explode if I tried to explain in my own words, but here's a link to start you off:

    http://www.maladjusted.org/sql-server/remote-reporting-and-linked-servers/Linked%20Servers%20and%20Windows%20Authentication.doc

    There's quite a bit of info out there via Google too. Good luck!

    Cath

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply