SQL Prompt 8 and SSMS 2017 issue

SalSal Posts: 3 Bronze 1
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?
Tagged:

Answers

  • johnnyi0317johnnyi0317 Posts: 5 Bronze 3
    edited August 14, 2017 9:09PM
    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 RJames R Posts: 104 Silver 4
    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
    Software Engineer
    Redgate Software
  • SalSal Posts: 3 Bronze 1
    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).
  • fmcmullanfmcmullan Posts: 3 Bronze 2
    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.
  • johnnyi0317johnnyi0317 Posts: 5 Bronze 3
    edited August 15, 2017 4:34PM
    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.
  • BrandonDavisBrandonDavis Posts: 1 New member
    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.
  • 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
  • SalSal Posts: 3 Bronze 1
    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.
  • 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.
  • fmcmullanfmcmullan Posts: 3 Bronze 2
    Thank you, Tianjiao_Li. That worked for me.
  • Indeed, thank you Tianjiao_Li and Sal. This fix worked here as well. Great job.
  • ashortashort Posts: 1 Bronze 1
    @Tianjiao_Li I just started a trial and ran into this issue, this fix works immediately, thank you!
  • mikecaremikecare Posts: 19 Silver 1
    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.
  • PDinCAPDinCA Posts: 642 Silver 1
    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...
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • James RJames R Posts: 104 Silver 4
    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
    Software Engineer
    Redgate Software
  • mikecaremikecare Posts: 19 Silver 1
    Just downloaded and trying it out. Seems to have fixed for me! Will continue to test it out today.

    MikeC
  • mikecaremikecare Posts: 19 Silver 1
    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 RJames R Posts: 104 Silver 4
    Strange. What do you mean when you are unable to get any suggestions to work? Are there any errors in the logs?

    James
    Software Engineer
    Redgate Software
  • mikecaremikecare Posts: 19 Silver 1
    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 RJames R Posts: 104 Silver 4
    That's very strange. Can you try downgrading to the latest release and see if that fixes it?
    Software Engineer
    Redgate Software
  • mikecaremikecare Posts: 19 Silver 1
    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.
  • 8.0.8.2203 fixed this issue for me. Thanks!
  • NitzNitz Posts: 1 New member
    Version 8.0.8.2203 fixed this issue for me as well.
  • mortezamorteza Posts: 1 New member
    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!

    q0xi7krus8xj.png


    many thanks to Tianjiao_Li , my problem was solved after 2 month !!
Sign In or Register to comment.