SQL Prompt 8 and SSMS 2017 issue

Sal

Recently upgraded to SSMS 2017. When I connect to a server using my Windows authentication SQL Prompt works like a charm. When I connect to a server using the SQL Server Authentication SQL Prompt stops working for that connection. Unfortunately I need to connect to servers differently based on access permissions. This worked fine in SSMS 2012 before the upgrade.

Any thoughts?

johnnyi0317

I'm having this issue as well. When I Crtl-Alt-D, I don't see the database objects (tables) being read in. Code complete works with SELECT, etc, but not with my database's objects. No doubt it is something on my part - being stupid for instance, but I'm not figuring it out.

James R

Hi guys, thanks for the information.

I can't seem to reproduce this easily on my end, could you let me know what versions of SQL Prompt you are using and what versions of SQL Server you are trying to connect to? Older versions of SQL Prompt 8 don't have SQL Server 2017 support.

Thanks again!
James

Sal

Hi James

I've been working with Tianjiao LI (Product Support Engineer) on this today and have supplied screen shots, etc. I'm working with Sql Prompt 8.0.8.2086 and SSMS 2017 v17.2.

It's just an odd thing that it works fine with SSMS as long as I use Windows authentication. As soon as I connect via SQL Server Authentication I run into these problems.

I've also got SSMS 2012 installed on my system and Sql Prompt 8 works as expected using either authentication.

Our databases are SQL Server 2014 (SP2).

fmcmullan

I'm having the same problem. Using SSMS 2017 v17.2, with SQL Prompt 8.0.8.2086 connected to SQL Server 2014. When I try to refresh suggestions (CTRL-Shift-D) I see a very quick small box (for less than half a second) from SQL Prompt with a message that says "Connecting" to the SQL server and "Loading", then nothing. Suggestions for my database objects do not display.

SSMS 2016, same SQL Prompt version connected to the same SQL Server engine, suggestions work with no problem.

I've uninstalled and reinstalled SQL Prompt with no success.

johnnyi0317

I'm using SSMS v17.2, connected to SQL Server 2016 SP1 13.0.4001.0 and SQL Prompt 8.0.8.2086. Same symptoms as the others.

BrandonDavis

Check your database permissions. Running a SQL Trace shows that it connects to [master] then executes a USE to switch contexts to the database you're requesting schema information on. It may be that you don't have access to [master] and your connection is being rejected.

pjtaylor49

So on this subject, it seemed to work fine in 17.1 without access to the master db. Connections to Azure SQL Db worked fine. So if this is a deliberate change it is a showstopper for me as typically we don't get access to master in Azure SQL Db

Sal

Hi Brandon

I'm not sure of the others but in my situation that wouldn't be the case. My issue is specific to SSMS 2017 and SQL Server Authentication only. On SSMS 2012 this works without issue with the same credentials.

johnnyi0317

Brandon,

I don't think that is the issue here as well. I have all rights granted to the user I'm logging in as.

Tianjiao_Li

Hi everyone,

I'm sharing @Sal 's finding for this issue. Can you please check your log file to see if you have similar error like this:

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)

In this case, please make sure to enable the "Trust Server Certificate" under Connection Properties when you connect to SQL server?

I hope this helps!

fmcmullan

Thank you, Tianjiao_Li. That worked for me.

johnnyi0317

Indeed, thank you Tianjiao_Li and Sal. This fix worked here as well. Great job.

ashort

@Tianjiao_Li I just started a trial and ran into this issue, this fix works immediately, thank you!

mikecare

I do not have these log errors. I have a database that is just not refreshing suggestions upon choosing to refresh. Using SSMS 2016 and SSMS 17.2 to try and connect to a SQL 2014 SP2 db and not having success seeing 2 new tables added. Validated "Trust Server Certificate" is checked on these connections.

PDinCA

No SQL Error Log messages like that posted above, and nothing in any Event Viewer category of Windows Logs.

Prompt 8.0.8.2086
VS 2017 v17.2

I just "Trusted" the cert. Refreshed suggestions. Hover over a known table and it now works with a SQL Login.

Why was it broken, and will it revert to prior behavior? Having scores of server connections to have to now go and change, this isn't a very good option... Fix coming? Please...

James R

Hey all,

I've been trying out a fix for this issue, can you please download this build and see if it's resolved?

Sorry for the inconvenience!

James

mikecare

Just downloaded and trying it out. Seems to have fixed for me! Will continue to test it out today.

MikeC

mikecare

So it worked for me on my Desktop, but on my laptop I am no longer able to get any suggestions to work. What should I try next?

James R

Strange. What do you mean when you are unable to get any suggestions to work? Are there any errors in the logs?

James

mikecare

I get suggestions from master db and from my snippets only. No matter what other db I connect to. I don't see any errors and have turned on verbose logging. I've attached my log

James R

That's very strange. Can you try downgrading to the latest release and see if that fixes it?

mikecare

Ok, I downgraded, but that did not fix it. Seemed to keep what change I had made under options. I checked "Restore defaults" and the refresh and suggestions came back. I'm going to upgrade back to the 8.0.8.2203 version and see if I'm still good.

jondcoleman

8.0.8.2203 fixed this issue for me. Thanks!

Nitz

Version 8.0.8.2203 fixed this issue for me as well.

morteza

Tianjiao_Li said:

Hi everyone,

I'm sharing @Sal 's finding for this issue. Can you please check your log file to see if you have similar error like this:

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)

In this case, please make sure to enable the "Trust Server Certificate" under Connection Properties when you connect to SQL server?

I hope this helps!

many thanks to Tianjiao_Li , my problem was solved after 2 month !!