Suggestions not refreshing -- Certificate Chain error logged

seanhoward

I'm seeing the following error in my log and the object caches are not being built so I get no metadata based suggestions. This is happening on server connections that aren't encrypted. It's also all of my servers regardless of version.

RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Created new ServerCandidate for server: myserver.whatever.nunya 15 Aug 2017 18:34:27,350 [32] WARN RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Failed to load server myserver.whatever.nunya System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ---> System.ComponentModel.Win32Exception (0x80004005): The certificate chain was issued by an authority that is not trusted
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 RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.IsContainedOrRestrictedDatabase(ConnectionProperties cp)
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadDatabaseContainment()
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:0ec4c34c-cccb-41ca-9f8d-bf250b9e7703
Error Number:-2146893019,State:0,Class:20

James R

Hey @seanhoward thanks for the post and the log!

I could do with a couple more pieces of information to help solve the issue.

What version of Prompt are you running, what version of SSMS are you running and what version of SQL Server are you connecting to? Additionally, do you have any 'Additional Connection Parameters' set when connecting to your servers?

Thanks for your help!
James

Tianjiao_Li

Hi @seanhoward,

As we discussed in the email, if you have an error like:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ---> System.ComponentModel.Win32Exception (0x80004005): The certificate chain was issued by an authority that is not trusted

before you connect to the SQL Server, please go to the Connection Properties and enable the "Trust Server Certificate" option.



Thanks!

Michael Clark

Hi @seanhoward,

This issue has been fixed in our latest release.

You can download it here:
http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_8.0.9.2226.exe

Thanks again for reporting.

Best,
Michael