SQL Search does not work in SSMS

vjacob1

SQL Search does not work or produce any results
When I try to check the log file or launch it
The log file is not updated nor it is opening to the right folder 
I am looking in the following location - \AppData\Local\Red Gate\Logs\SQLSearch 3
SQL Search 3 Version - 3.5.3.2624


Any ideas on how to troubleshoot this issue?

Thanks,
Vinod Jacob

Dan_J

Hi @vjacob1,

I'm sorry to hear about the issues you are experiencing with SQL Search! Thanks for reaching you to us regarding this.

Just to check, what version of SSMS are you currently running?

Regarding the log files, if you navigate to the Help menu (within SQL Search) ->Logging ->Locate log files  do you see any log files located there? If not, it could be that the logging level is set to 'No logging' (I think it is by default). If you change this to Verbose you should then see full log files created:

vjacob1

Hi Dan
The SSMS Version that I am using is shown below

SQL Server Management Studio 15.0.18369.0

SQL Server Management Objects (SMO) 16.100.46041.41

Microsoft Analysis Services Client Tools 15.0.19342.0

Microsoft Data Access Components (MDAC) 10.0.18362.1

Microsoft MSXML 3.0 6.0 

Microsoft .NET Framework 4.0.30319.42000

Operating System 10.0.18363

Logging is set to Verbose as that was the first thing that I did check.

Jessica R

Thank you @vjacob1, do you recall if you've set an rgtemp environment variable to change the location of Redgate temporary files? Does browsing to %rgtemp% bring up a valid path?

Can we also confirm:
- Is SQL Search grayed out?
- What SQL Server version are you attempting to search?
- If you open SQL Search before connecting to the SQL Server, does that have any effect?

vjacob1

That variable is not found at all on my system and I do not recall adding that variable.
No SQL Search is not greyed out.
SQL Server 2017
No it does not have any affect.

Dan_J

Hi @vjacob1

My sincere apology for the delay in responding to you on this!

After some research on this I have been made aware of a current bug within SQL Search that may relate to the issue you are seeing. Until a fix is released the following workaround may help:

1. Disconnect from the SQL Server in SSMS you want to search
2. Close and reopen SQL Search
3. Reconnect the SQL Server

If this does not work for you, please can you attempt a re-installation of SQL Search? We suggest using Revo uninstaller (https://www.revouninstaller.com/revo-uninstaller-free-download/) to remove any residual files before then reinstalling the latest version of SQL Search.

Again, if this does not resolve the issue, it may be worth trying to install a slightly older version of SQL Search. Version 3.5.0 can be downloaded here: https://download.red-gate.com/checkforupdates/SQLSearch/SQLSearch_3.5.0.2590.exe

Please let me know the outcome of these suggestions.

vjacob1

@Dan_J
Using an older version of SQL Search - allowed me to open the log file but getting the following errors when trying to search for objects.

I increased the timeout for the SQL Connection to the max but it is still timing out when trying to index.
See Errors below

Error Number:-2,State:0,Class:11

18 Mar 2021 11:00:37.822 [9] 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=14283; handshake=9878;  ---> 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:4eee3a17-980e-4914-89c8-c07babd546ab

Error Number:-2,State:0,Class:11

18 Mar 2021 11:00:37.843 [24] ERROR DatabaseObjectIndexer - Exception while indexing database SHN: 

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=14305; handshake=9905;  ---> 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:f6559440-11f0-415a-b9f4-8d2d89ef1456

Error Number:-2,State:0,Class:11

Dan_J

Hi @vjacob1

Thank you for your update on this, my apology for the delay in responding to you!

It is interesting that rolling back to a slightly older version of SQL Search allows for some, but not all, functions to work.

I don't appear to have asked you this before now, but when did you start seeing this issue? Was it following any particular action/update?

Also, what connection method do you use to connect to your database(s)?

vjacob1

I have been having this issue for a long time now, I previously thought it was the way we were connected to the SQL Server through SSMS - previously we were using the Run AS command to run as a different user but now things have changed and they gave us a direct connection so we can run as a normal user and now the SQL Search runs fine but now it does not return any results or come back with any errors. I am using a TCP/IP Connection to the SQL Server using the IP Address - I have tried to changed the timeout for the connection to the max value but that does not seem to change anything.

vjacob1

It appears to timeout while indexing the databases this the error that I am seeing

08 Apr 2021 05:46:09.631 [97] ERROR DatabaseObjectIndexer - Exception while indexing database SHN: 

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=9735; handshake=4826;  ---> 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:ccb84e40-92ae-47d3-a2f5-a364112f596c

Error Number:-2,State:0,Class:11

vjacob1

Good Morning - is there any solution for me to use SQL Search in my current setup
I have a feeling that the reason for my issues is because the SQL Server that I am trying to search is connected using a VPN.
Perhaps this could be the reason this is not working?
I have another PC that is on the network where it is a two way trust and SQL Search works without any issue.
Any ideas of what to try?

Thanks,
Vinod Jacob

Dan_J

Hi @vjacob1

My apology again for the delay in responding to you on this!

Thank you for highlighting that this could be related to you using a VPN.

Firstly, can you please try launching SQL Search before connecting to the server to see if that helps at all?

If not, it would be greatly appreciated if you can you send me all of the log files from the following directory:
C:\Users\your username\AppData\Local\Red Gate\Logs

vjacob1

Hi Dan,
https://1drv.ms/u/s!AjMRocjlC4Py4RJEoUPlH0ZSbWf5?e=2jLZxQ
Here is the link to the Log Files.

vjacob1

Any updates?

LlewellynAtBluestone

Yes... very frustrating.. having the grayed out issue too

Dan_J

Hi @vjacob1

My sincere apology for the delay in getting back to you on this.

I am currently struggling to reproduce the issue you are seeing here, and I wondered if it might be possible for you to try a remote connecting (RDP) to a machine that is on the other side of your VPN that has SSMS and SQL Search installed? If so, it would be good to see if you experience the same issue then?

Also, I have been made aware that one of our other products (SQL Source Control) can cause performance issues with SSMS when a VPN connection is involved. If you do have SQL Source Control installed, would it be possible to try temporarily removing it to see if you continue to see the same issue in SQL Search?

Also, one final thing to check, do the search results ever show? It would be interesting to know if they do after leaving SQL Search to run for a few minutes.

vjacob1

Hi Dan,
It was already mentioned in this post but I do have another computer that I previously RDPing to use SSMS as part of our work setup 
SQL Search works without no issues as this computer has a direct network connection to the SQL Server
I uninstalled SQL Source Control and SQL Search just crashes when it was trying to pull up a list of the databases and SSMS terminates and restarts but this was the behavior all time when using SQL Search with this VPN Connection

Dan_J

Hi @vjacob1

Thank you for update on this, our apology for the delay in responding to you!

I wondered if you could please try the following: in SSMS, disconnect from your server, reopen the SQL Search tab, then reconnect to your server (via VPN), does SQL Search work then?

vjacob1

@Dan_J Sorry that did not work...I tried a couple of times but SQL Search does not respond at all and just sits and sometimes causes SSMS to crash

Dan_J

Hi @vjacob1

Thank you for your update on this, I'm sorry that my suggestion did not help on this occasion.

Just to let you know that I will be attempting to reproduce this issue again tomorrow by creating a VPN scenario with SQL Search. I will get back to you as to the outcome of that as soon as possible.

LlewellynAtBluestone

I use to get crashing problems, I uninstalled redgate tool belt, installed the latest SSMS and reinstalled the toolbelt and was okay after that.  With regards to the grayed out SQL Search, I removed the server name from the Object Explorer window in SSMS and reconnected to it.  SQL Search worked again after that.

Dan_J

Hi @vjacob1

I'm afraid again we are not seeing the same issue you are reporting with SQL Search.

It could be that we are not quite replicating your scenario, we did the following:

- Connected to VPN (to the Redgate office network)
- RDP'd onto a PC sat in the office
- Launched SSMS (15.0.18)
- Test 1 - SQL Search v3.5.5 - we performed a search, after indexing for around 30 seconds search results are produced.
- Test 2 - SQL Search v.3.5.3 - Same search as above, same result (search results are produced)

Is there anything we should have done differently to the above steps?

One question that came out of our discussion this morning was around the size of the database you are searching on. Is it a particularly large database?

vjacob1

@Dan_J - the size of the database is about 1 GB or so last I checked
SQL Search is now crashing my SSMS when I try to go into the tool
Attached is a link to the log file - https://1drv.ms/u/s!AjMRocjlC4Py43bG4ZhD8FSItpha?e=OWnT63 

Dan_J

Hi @vjacob1

Thank you for providing the latest log file, I am reviewing this now.

I just wanted to check, are the replication steps outlined below a correct representation of the scenario in which you are seeing this issue with SQL Search?:

- Connected to VPN (to the Redgate office network)
- RDP'd onto a PC sat in the office
- Launched SSMS (15.0.18)
- Test 1 - SQL Search v3.5.5 - we performed a search, after indexing for around 30 seconds search results are produced.
- Test 2 - SQL Search v.3.5.3 - Same search as above, same result (search results are produced)