none
Could not open a connection to [Linked] SQL Server

    Question

  • When using the SQL Server Native Client (SQL Native Client) provider to run queries between two linked computers that are running SQL Server 2005 I did receive the following error message:
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    Following the steps described in FIX: Error message when you run queries between two SQL Server 2005 linked servers: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" (925001) I configured the connection between the two SQL Server linked servers to use the Named Pipes protocol instead of the TCP/IP protocol.
    Unfortunately after
    1. enabling the Named Pipes protocol on both linked servers
    2. configuring them for remote access
    3. recreating a new linked server configuration that uses a different name and that uses the Named Pipes protocol

    I still receive the following error:
    OLE DB provider "SQLNCLI" for linked server "pamm22" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI" for linked server "pamm22" 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 "pamm22" returned message "Invalid connection string attribute".

    When authenticating as sa using SQL Server Authentication I'm able to connect, but the result of the next query learns that the Named Pipes protocol is being used:
    select net_transport, auth_scheme from Server2.master.sys.dm_exec_connections where session_id=@@spid
    
    net_transport auth_scheme
    TCP SQL

    How should I force usage of the Named Pipes protocol, besides what I already did:
    EXEC sp_addlinkedserver @server='Server2', @srvproduct='', @provider='SQLNCLI', @datasrc='np:Server2', @provstr='Integrated Security=SSPI'
    


    • Edited by korbeeck Wednesday, November 16, 2011 3:44 PM
    Tuesday, November 15, 2011 4:28 PM

Answers

  • Thanks Amber, for another detailed reply.

    First of all, after troubleshooting with linked server connections over Named Pipes but concluding from your post that SQL Server 2005 Service Pack 2 would suffice, I disabled the Named Pipes client protocols on all of our servers; next week during our monthly maintenance windows this will become effective.

    All our servers have at least Service Pack 2 installed, the development editions even Service Packs 3/4. So you suggest that ‘double hop’ authentication via a middle server should work using SP2?

    I did follow the suggested steps in SQL Linked Server Query failed with “Login failed for user …” (link):

    1. Executed and verified linked server creation
    2. Configured and verified self-mapping login
    3. Verified user domain account permission using Sqlcmd -E -S pamm22
    4. Verified linked server query with ‘single hop’ authentication


    Furthermore the user’s domain account has not been set to Account is sensitive and cannot be delegated, and the SQL Server service account has been trusted for delegation. Using setspn -L pamm22 learns that the following SPNs are not registered:

    MSSQLSvc/PAMM22
    MSSQLSvc/pamm22.pamm.local

    Only its HOST/… equivalent. Should I consult our system manager to make a SPN accordingly to get it working, whereby the service account should be supplied as accountname?

    setspn -A MSSQLSvc/pamm22.pamm.local:1433 accountname
    Monday, November 28, 2011 1:26 PM

All replies

  • What do you get back if you do:

     

    SELECT *
    FROM OPENQUERY(Server2, 'select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid')
    

    What I think you are doing is getting your local @@SPID and pulling the information from the Server2 DMV for the wrong session_id as a result.  Using OPENQUERY pushes the entire execution to the remote instance.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, November 15, 2011 5:34 PM
    Moderator
  • Thanks Jonathan, for your quick response.

    I tried the query you suggested; the result is promising:
    net_transport auth_scheme
    Named pipe SQL
    Session SQL
    Executed as sa.

    However when using Windows Authentication the received errors are still the following:
    OLE DB provider "SQLNCLI" for linked server "pamm22" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI" for linked server "pamm22" 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 "pamm22" returned message "Invalid connection string attribute".

    While the account used has sufficient rights to access the server via SSMS and receiving its WMI statuses; any possible solutions are appreciated.

    • Edited by korbeeck Thursday, November 17, 2011 10:22 AM
    Wednesday, November 16, 2011 4:30 PM
  • Hi Korbeeck,

    According to the error message you provided, have you installed SQL Server 2005 SP2 or later SP?

    If not, please download and install the Hotfix for SQL Server 2005 from here: FIX: Error message when you run queries between two SQL Server 2005 linked servers: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

    For SQL Server 2005 SP1, please download and install the Hotfix from here: FIX: Error message when you use the SQL Server Native Client provider to run queries between two linked servers in SQL Server 2005 Service Pack 1: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

    Meanwhile please pay attention to the WORKAROUND section in above KBs.

    Otherwise, you may refer to the following links to troubleshoot the issue:
    http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
    http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

     


    Regards, Amber zhang
    Tuesday, November 22, 2011 7:53 AM
    Moderator
  • Thanks Amber, for another detailed reply.

    First of all, after troubleshooting with linked server connections over Named Pipes but concluding from your post that SQL Server 2005 Service Pack 2 would suffice, I disabled the Named Pipes client protocols on all of our servers; next week during our monthly maintenance windows this will become effective.

    All our servers have at least Service Pack 2 installed, the development editions even Service Packs 3/4. So you suggest that ‘double hop’ authentication via a middle server should work using SP2?

    I did follow the suggested steps in SQL Linked Server Query failed with “Login failed for user …” (link):

    1. Executed and verified linked server creation
    2. Configured and verified self-mapping login
    3. Verified user domain account permission using Sqlcmd -E -S pamm22
    4. Verified linked server query with ‘single hop’ authentication


    Furthermore the user’s domain account has not been set to Account is sensitive and cannot be delegated, and the SQL Server service account has been trusted for delegation. Using setspn -L pamm22 learns that the following SPNs are not registered:

    MSSQLSvc/PAMM22
    MSSQLSvc/pamm22.pamm.local

    Only its HOST/… equivalent. Should I consult our system manager to make a SPN accordingly to get it working, whereby the service account should be supplied as accountname?

    setspn -A MSSQLSvc/pamm22.pamm.local:1433 accountname
    Monday, November 28, 2011 1:26 PM