locked
Login from LAN Client fails RRS feed

  • Question

  • Server: Windows Server 2012, Windows Firewall: Off, SQL Server Database Engines: ON, SQL Browser Service: ON

    Client: Windows 7 Home Premium (Local Area Network Computer)

    Issue:

    From server, I can use Microsoft SQL Server Management Studio to connect to any SQL Server database engine (Primary or named) on the server.

    From Client, I can use a remote desktop connection and within the remote desktop connection I can start Microsoft SQL Server Management Studio on the server and connect to any SQL Server database engine (Primary or named).

    From Client, I can start Microsoft SQL Server Management Studio but attempts to connect to the Server’s instances of SQL Server result in the following:

    Primary SQL Server instance:

    Windows Authentication or SQL Server Authentication (Same Error)

    ===================================

    Cannot connect to XXXX-SERVER12.

    ===================================

    Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=22290; handshake=36;  (.Net SqlClient Data Provider)

    ------------------------------

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

    ------------------------------

    Server Name: XXXX-SERVER12

    Error Number: -2

    Severity: 11

    State: 0

    ------------------------------

    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

       at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

       at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()

       at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()

       at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable)

       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)

       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)

       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)

       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)

       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.Open()

       at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    ===================================

    The wait operation timed out

    XXXXXXXXXXXXXXXXXXXXXXXXXXX

    Named SQL Server instances:

    Windows Authentication or SQL Server Authentication (Same Error)

    ===================================

    Cannot connect to XXXX-SERVER12\XXXXSQLSERVER.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

    ------------------------------

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

    ------------------------------

    Error Number: -1

    Severity: 20

    State: 0

    ------------------------------

    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)

       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)

       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)

       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)

       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.Open()

       at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    Additional: All SQL Services (Except SQL Server Agent (primary and instances)) are running, all protocols are enabled and SQL Server Log shows no failed logon attempts.



    • Edited by grahamvb Monday, January 6, 2014 7:17 PM
    Monday, January 6, 2014 7:15 PM

Answers

All replies

  • try to put server name like this

    [ip server address]\[instance name]

    Tuesday, January 7, 2014 8:57 AM
  • Not sure whether you already visited the link  http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5796417-f269-45d9-bedc-2987b9b800f1/unable-to-connect-to-ssms-2012-in-win-2012-microsoft-sql-server-error-2?forum=sqldatabaseengine.

    *) Make sure firewall is open for sql server port

    *) Make sure you are able to ping the server from client

    *) Make sure you are able to telnet the server on sql server port (default is 1433) from client.

    *) Make sure TCPIP protocol is enabled in server atleast for IPV4. You can verify this in sql server configuration manager or execute sp_readerrorlog you can get the details in first few lines. If you are able to do this then you should be able to connect to the server from client.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Marked as answer by grahamvb Sunday, November 19, 2017 5:43 PM
    Tuesday, January 7, 2014 9:43 AM
  • Thank you for your reply. I carefully went through the steps, the only thing not properly configured was the port 49172 in the firewall and TCP listening at that port in SQL Server Configuration Manager.

    Unfortunately there still seems to be a similar issue

    ===================================

    Cannot connect to tcp:XXXX-SERVER12\XXXXSQLSERVER, 49172.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

    ------------------------------
    Error Number: 10060
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    ===================================

    A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

    Thursday, January 9, 2014 4:12 PM
  • Wojciech Wojtulewski

    No change. Same error.

    • Edited by grahamvb Thursday, January 9, 2014 5:01 PM
    Thursday, January 9, 2014 4:13 PM
  • Telnet to (server IP Address) XXX.XXX.X.XXX 1433 returns the message Connection to host lost.

    What does that mean?

    Thursday, January 9, 2014 5:01 PM
  • Telnet to (server IP Address) XXX.XXX.X.XXX 1433 returns the message Connection to host lost.

    What does that mean?

    Hello,

    What's the complete error message did your received when telnet the server machine?

    What's the authentication type did you select when connect to SQL Server instance? Please verify you configured the server authentication with mix mode if you use SQL authentication, and the account had login on the SQL Server instance.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Tuesday, January 14, 2014 8:36 AM
  • Telnet to (server IP Address) XXX.XXX.X.XXX 1433 returns the message Connection to host lost.

    What does that mean?


    This is expected if you're running a named instance since they are generally not hosted on port 1433 (this port is generally reserved for use by a default instance).

    I see you have checked the Windows Firewall settings, but do you have have network firewall killing the connection instead?  Check with your Sys Admin/Network team to be certain.

    John


    John Eisbrener - http://dbaeyes.com/

    Tuesday, January 21, 2014 9:10 PM
  • ARGH! Stupid Azure Admins can't follow Firewall settings protocol!

    Thank you, found that the port wasn't open in the firewall.

    Thanks,
    Noah

    Tuesday, December 6, 2016 9:40 PM