How can we help you today? How can we help you today?

SQL SEARCH getting Error while trying to index

I have SQL SEARCH installed and it is getting the following error - how do I fix this issue but I can query the server fine

22 Sep 2020 11:25:51.571 [69] ERROR ServerObjectIndexer - Exception while indexing server: 
System.Data.SqlClient.SqlException (0x80131904): 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=13686; handshake=9430;  ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   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, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   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 RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection..ctor(String , CancellationToken )
   at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnectionSource.Connect(CancellationToken )
   at RedGate.SQLSearch.Indexing.ServerObjectIndexer.Index(CancellationToken )
ClientConnectionId:d8801283-18ed-4cdb-8259-492998996662
Error Number:-2,State:0,Class:11
22 Sep 2020 11:25:51.651 [32] ERROR DatabaseObjectIndexer - Exception while indexing database master: 
System.Data.SqlClient.SqlException (0x80131904): 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=13670; handshake=9409;  ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   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, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   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 RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection..ctor(String , CancellationToken )
   at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnectionSource.Connect(CancellationToken )
   at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken )
ClientConnectionId:fe519e9d-ed10-44ff-a472-a75da30a1bbe
Error Number:-2,State:0,Class:11
22 Sep 2020 11:25:51.657 [BatchingUsageApiClient] INFO  RedGate.SQLSearch.UsageReporting.ApplicationUsageHelper - Sending 1 event:
[
  {
    "eventType": "search",
    "headers": {
      "productId": "sql-search",
      "productVersion": "3.5.2.2607",
      "eventUuid": "e45e4e87-cdd1-4c36-9b9e-e10fdc147cf1",
      "usageUserId": "2b6308e8-7494-4eaf-ae95-46e1f3f18525",
      "sessionUuid": "8c0416cd-5e27-41d7-b10e-2cad48f9af06",
      "createdTimeUtc": "2020-09-22T15:25:51Z"
    },
    "payload": null
  }
]
vjacob1
0

Comments

11 comments

Sort by
  • Tianjiao_Li
    @vjacob1

    Sorry to hear you've run into this error.

    Could you confirm if you have "Force encryption" turned on for your server?


    Tianjiao_Li
    0
  • vjacob1
    Hi Tianjiao,

    No Force Encryption is turned off for the server
    vjacob1
    0
  • Tianjiao_Li
    Thanks for checking it!

    Could you try to increase the connection time-out value for connecting to your SQL Server in SSMS?



    Does that help?


    Tianjiao_Li
    0
  • vjacob1
    This only occurs on that specific network when I am connected and that is only when I am working in the office.
    Today I am working remote and connect to another computer for SSMS on that computer (which is a desktop) SQL Search works without any issues.
    it is only when I am in the office and run SSMS off of that connection - please keep this ticket open 
    I will try increasing the timeout the next time that I go in.
    vjacob1
    0
  • Tianjiao_Li
    No problem! Please keep us posted.
    Tianjiao_Li
    0
  • vjacob1
    What is the maximum value that you can put in that field?
    vjacob1
    0
  • vjacob1
    I have changed the connection time out and that still does not work and still gets the same error.
    It is still getting the indexing error - as mentioned previously this is only happening on this machine only 
    There is another computer when I use while working from home where SQL Search works without any issues 
    The only difference is that it is an older Windows Version
    I would like to fix it on this computer so I can use while working in the office

    vjacob1
    0
  • Tianjiao_Li
    @vjacob1

    What's the Windows Version for the machine in your office? https://documentation.red-gate.com/ss/requirements

    Tianjiao_Li
    0
  • vjacob1
    Version 1909 Build 18363.1082 - on this version the SQL Search is not working at all

    vjacob1
    0
  • vjacob1
    Version 1809 Build 17763.1457 - this the other computer where SQL Search works without any issue
    vjacob1
    0
  • Tianjiao_Li
    Could you try enabling TCP/IP for IPv4 and IPv6 on the SQL Server and check again?
    Tianjiao_Li
    0

Add comment

Please sign in to leave a comment.