none
Cannot Connect to the High Availability SQL Server using the flag Application Intent = ReadOnly throwing error No such host is known

    Question

  • Hi,

    I am trying to connect to the SQL Server High Availability using ADODB in vc++. The connection string is as follow :

    Provider=SQLNCLI11;Data Source=tcp:%s,%s;Persist Security Info=True;Initial Catalog=%s;Application Intent=READONLY

    From few remote machines I can connect to the SQL server as expected so the configuration on the SQL Server is correct. Whereas from other machines if I am not using Application Intent flag I am able to connect. Whereas with the flag I am not able to connect.

    Machine is in same domain as the SQL server machine. And I am able to ping the machine.

    So what configuration or setting is not done that could throw the error.

    The error from the different tries are : 

    1. Visual Studio -> Server Explorer -> Data connection with connection string:

    Data Source=x.x.x.x,port;Initial Catalog=dbname;User ID=username;ApplicationIntent=ReadOnly:

     TCP Provider, error - 0: No such host is found 


    2. The error from SQL Server 2016 Management studio is: 

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

    Cannot connect to x.x.x.x,port.

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

    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 - No such host is known.) (.Net SqlClient Data Provider)

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

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


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

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
       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.UI.ConnectionDlg.Connector.ConnectorThread()

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

    No such host is known

    3. Using SQLCMD:

    "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE" -S x.x.x.x,xxxx -U username -P **** -K READONLY -d dbname
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: No such host is known.
    .
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

    Tuesday, March 21, 2017 5:06 AM

All replies

  • Hello Monika,

    Is there a Firewall running on the Client machine which blocks the used IP port "5123"?

    See also the interactive guide: Solving Connectivity errors to SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 21, 2017 6:05 AM
  • Firewall is off on both Sql Server and the remote machine. I am able to connect from some other remote machines. So want to know what are the possible configuration change required in order to able to use the High Availability.
    Tuesday, March 21, 2017 8:22 AM
  • Please post your read only routing configuration as there is an issue with that part of it.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Saturday, March 25, 2017 2:57 PM
    Answerer