Sql-server – SSMS can’t connect to Azure database

azure-sql-databasesql serverssms

I got a new Windows 10 machine and installed the latest version of SQLEXPRESS and then the latest version of SSMS (17.9.1).

When trying to connect to my Azure database, I'm getting the following error.

enter image description here

Error connecting to 'mydb.database.windows.net'.

—————————— ADDITIONAL INFORMATION:

Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)

An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)

A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 – An existing connection was
forcibly closed by the remote host.) (Microsoft SQL Server, Error:
10054)

An existing connection was forcibly closed by the remote host

What I've tried so far:

  1. Uninstall/Reinstall – nope. I did this twice
  2. Turn off firewall – didn't work. Besides, I can connect to the database fine through Visual Studio 2017, so it isn't a firewall issue. I also validated with netstat that port was open after I telnet-ed to the server.
  3. Different version – I tried downgrading to SSMS version 17.9, but I get the same error. I'm in the process of bringing up 18.0 (preview 5) just to see if something will work for me. I may update here whether that works for me or not.
  4. Different database – I have two Azure servers, I can't access either. I can connect to my local SQLEXPRESS instance without any issues.

My Google-foo has failed me on this so far. What am I missing? My current theory is that the SQLEXPRESS installation is colliding with SSMS install, but I don't know how to know that for sure – it was just something I read that used to be a problem.

Update

18 Preview 5 didn't help me. Same error 🙁

Others that appear to have this same issue without a resolution:

Update 2

Uninstalling SQLEXPRESS and using IISCrypto to add specific TLS entries into the registry didn't help either.

Update 3 (After the answer below)

I installed SSDT and everything broke again :(. However, I did find that I can connect a single query in SSMS with no problem, but I cannot connect to a Server in the Object Explorer. Go figure.

I'll update here if there is something else I discover.

Best Answer

Update: I found that connecting to AWS failed too which led me to yet another Google search which led me here: https://www.sqlservercentral.com/Forums/1891503/Unable-to-connect-to-SQL-2008-Servers-from-SSMS-2017

Following the instructions from the last post in the referenced site, I downloaded IISCrypto and enabled all of the Ciphers. Bingo.

https://www.nartac.com/Products/IISCrypto/Download

I'll leave my previous answer below in case it brings comfort to anyone who went through sleepless nights and a couple of keyboards.

Old Update - After installing SSDT 15.8.2 the solution below stopped working for me :(

After days of:

  • MS Azure SQL Support - not helpful (they just referred me to this post. The one I made.)
  • Uninstalling SQL Server Express/Developer and three different versions of SSMS - all with the same results
  • Creating a VM on my computer with Win 10 Pro - SSMS worked on it no problem
  • Wiping the computer and starting from absolute nothing (using the MSI recovery) - still same issue
  • Inspecting TLS packets to ensure version 1.2 - it was 1.2
  • Trying to connect with SqlPackage.exe - never worked for me, same error as SSMS
  • Trying to connect with SQLCMD - it worked!
  • Trying to connect with SQL Native Client - also worked!

... I did finally get it to work. Good news is that it works. Bad news is that I don't know exactly why.

I currently have a fresh Win 10 Pro install with SSMS 17.9.1. I do not have any SQL Server instance installed. My default Windows Firewall is enabled and Norton AntiVirus is managing it (default with the computer). All Windows Updates have been applied as of this writing. I also downloaded and installed the SQL Server 2012 Native Client https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

When it started working, I had just used SQLCMD and connected to the Azure SQL Server master database and was querying the sys.event_log table. Immediately before that, I had used SQLCMD to connect to a different database on that same server. When I didn't see anything in the sys.event_log table I went back to SSMS and it worked for all of my servers (we have three).

TL;DR Connect with SQLCMD for a while and see if that helps??

Related Question