1

I have been using SQL Server & Azure for almost two years with Zero problems. Now for some reason I am getting the following error when I try to connect to my Azure database from SSMS. I have tried everything I could find: extend connection time-outs, checked open firewall port 1433, used my phone Hotspot to make internet connection, turned off public and private firewalls (temporarily for testing)... with no difference. I have Azure BASIC service and the database only holds dummy information for testing.

Everything in the Azure Portal looks fine. I can't find any errors or outages. The only thing I thought was weird was that my Server is in "West Europe" (I'm in Florida). And my Storage Account is in "East US". Maybe that's not unusual though?

Microsoft SQL Server Management Studio
------------------------------
Error connecting to 'gobo.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

Tried testing with Azure Data Studio, where I could connect and I could run a query against a datatable successfully, however when I tried to expand the server list to show the database I got the following:

enter image description here

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) ---> System.ComponentModel.Win32Exception: The specified network name is no longer available
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
at Microsoft.SqlServer.Management.Smo.Database.get_IsSqlDw()
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.DatabaseTreeNode.EnsureContextInitialized() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\DatabaseTreeNode.cs:line 32
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoTreeNode.GetContext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\SmoTreeNode.cs:line 98
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.GetContextAs[T]() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\Nodes\TreeNode.cs:line 310
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.DatabaseTreeNode.PopulateChildren(Boolean refresh, String name, CancellationToken cancellationToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\SmoModel\DatabaseTreeNode.cs:line 46
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.Expand(String name, CancellationToken cancellationToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\Nodes\TreeNode.cs:line 248
at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.ObjectExplorerService.<>c__DisplayClass36_0.<QueueExpandNodeRequest>b__0(IBindingContext bindingContext, CancellationToken cancelToken) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\ObjectExplorer\ObjectExplorerService.cs:line 440
at Microsoft.SqlTools.ServiceLayer.LanguageServices.BindingQueue`1.<>c__DisplayClass34_2.<ProcessQueue>b__1() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\LanguageServices\BindingQueue.cs:line 352

I am at a total loss. Have spent 10+ hours on this with no progress. Any help would be appreciated. I am self taught and understand enough to use, but not experienced enough to do in depth debugging...

I installed sqlcmd and was able to connect to the server and query against the database, but I'm not sure what that tells me about the problem.

  • It looks like the network connection is getting dropped. Try running a network trace, such as with Wireshark, to monitor the TCP traffic. – Dan Guzman Feb 17 at 12:55
  • The error message indicates that the connection was forcibly closed by the host--this often indicates a firewall blocking the request, but in your case, sqlcmd works, disproving that. The stack trace from ADS indicates the problem is in loading Object Explorer--which is probably what is happening in SSMS, too. What version of SSMS & other SQL Tools do you have installed on your machine? My hunch is a shared component/dll may be to blame. – AMtwo Feb 17 at 13:04
  • Thank you for your replies! I'm not familiar with Wireshark, but I will look into it today. I am using SSMS v17.9.1 and downloaded/installed sqlcmd yesterday. Also (if that helps troubleshoot), I am working on a VS Community 15.8.1 C# project and I have similar problem connecting to my Azure database for there. – mjordan Feb 17 at 13:35
  • I ran Wireshark. I don't fully understand the network traffic, but there was nothing glaring that screamed error or such. – mjordan Feb 18 at 0:05
1

In case someone runs into a similar problem... After 1.5 hours on the phone with MSFT, and reinstalling both SSMS and VS with no resolution. I reformatted my machine, reinstalled, and now everything works the way it should. I don't know what got into my system but now it's gone. Not a great solution, but that's what worked.

UPDATE: When the problem reoccurred again, and after four more "reformats", the root of the problem was the Network Driver for my machine. Although Windows Update reported that I had the latest driver, there was a newer driver on the manufactures website that seems to have solved the problem.

Product Type: Killer(R) Wireless-AC 1550 Wireless Network Adapter (9260NGW) 160MHz Product Type: Killer E2500 Gigabit Ethernet Controller

Operating System: Windows 10 Pro 64-bit (10.0, Build 17763) (17763.rs5_release.180914-1434) System Manufacturer: Alienware BIOS: 1.6.5 (type: UEFI)

0

Had a similar issue with the creation of an index that was taking too long. Didn't work on SSMS, got it working on Azure Data Studio.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.