0

I setup a SQL Server 2014 server (SQL1) and a web server (WEB1) using virtual machine template on Azure a year ago. I use PC1 in the office to write code for WEB1. Everything was fine.

Device list:

-------------------------------------------------------------
|WEB1    |10.0.0.5     |ASP.net web app still fine after sp2|   
|--------|-------------|------------------------------------|
|SQL1    |10.0.0.4     |SQL Server in question              |
|========|=============|====================================|
|PC1     |NAT          |I can not login from here           |
-------------------------------------------------------------

Yesterday I installed SP2 on this server. After reboot, I can no longer connect to the server from SQL1 locally and PC1 remotely. WEB1 in the same subnet using SQL Server runs fine.

After research, I noticed the server went into single-user mode, so I switch the server back to multi-user (by using sqlcmd) and then I can login using SSMS on sql1.

But I can still not login from my pc using SSMS or asp.net connection string.

I've checked:

  • Windows Firewall still allows public ip of my pc to connect to.
  • Azure Network security group still allows any - any - MS SQL.
  • SQL Server service is running.
  • TCP/IP is enabled in Configuration Manager.
  • I can ping SQL1 from WEB1 using its IP if ICMP Windows Firewall rule is enabled.

How to solve it?

Vinix
  • 121
  • 1
  • 8
  • 1
    Are you getting a specific error in SSMS when you try to connect to the server remotely? – Poosh Jun 02 '17 at 05:06
  • A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) – Vinix Jun 02 '17 at 07:17
  • Maybe some of this answers might help: [Named Pipes Provider, error 40 - Could not open a connection to SQL Server](https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-could-not-open-a-connec) – Poosh Jun 03 '17 at 02:31

1 Answers1

1

I solved this problem.

It's not related to SP2. It's because wrong NSG selected when applying recommendations of Advisor.

  1. Go to Virtual Network - Subnets, select the subnet the VM belongs to.
  2. Correct the NSG setting. Or set to none if you already have NSG for all VMs.
Vinix
  • 121
  • 1
  • 8