SQL SEARCH getting Error while trying to index

vjacob1

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

  }

]

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?

vjacob1

Hi Tianjiao,

No Force Encryption is turned off for the server

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?

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.

Tianjiao_Li

No problem! Please keep us posted.

vjacob1

What is the maximum value that you can put in that field?

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

Tianjiao_Li

@vjacob1

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

vjacob1

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

vjacob1

Version 1809 Build 17763.1457 - this the other computer where SQL Search works without any issue

Tianjiao_Li

Could you try enabling TCP/IP for IPv4 and IPv6 on the SQL Server and check again?