My Visual Studio is unable to connect to SQL Server

Question

My Visual Studio is unable to connect to SQL Server

Error_401 86

Hi,

I'm not a developer so I have very less knowledge on coding.
I have configured SQL Cluster with the following COnfiguration -

Cluster 2 - 10.0.0.7
Cluster 11 - 10.0.0.8

SQL Role Server -10.0.0.10

I also have two iSCSI configured in the failover manager -

For now the SQL Role is assigned to Cluster11 from the screenshot, so I can ping 10.0.010 from Cluster11 and not from Cluster2.

I also have a webserver - 10.0.0.13 where I have installed Visual Studio 2019 but I cannot connect to 10.0.0.10, the ping also fails -

I'm a complete newbie in this, please guide me on how to fix this and guide me accordingly.

Please let me know if you need more information or if there is something I'm missing or did not configured correctly.

Thanks

Olaf Helper 47,436 Reputation points

2021-05-07T06:34:53.62+00:00

Can you ping the cluster name SQLFOC or FQDN SQLFOC.demo.com?
Error_401 86 Reputation points

2021-05-07T06:53:42.413+00:00

No, the ping is not working
Error_401 86 Reputation points

2021-05-07T06:54:40.47+00:00

Here is the Port Query from webserver - 10.0.0.13 to 10.0.0.10

Accepted answer

1 additional answer

Your answer

Olaf Helper 47,436 Reputation points

2021-05-07T06:34:53.62+00:00

Can you ping the cluster name SQLFOC or FQDN SQLFOC.demo.com?
Error_401 86 Reputation points

2021-05-07T06:53:42.413+00:00

No, the ping is not working
Error_401 86 Reputation points

2021-05-07T06:54:40.47+00:00

Here is the Port Query from webserver - 10.0.0.13 to 10.0.0.10

Answer 1

Cris Zhan-MSFT 6,661

Hi,

>I have already disabled firewalls on all the devices and since all these machines are VMs on Azure

Some people encountered the same problem when using FCI on Azure VMs. You may need to configure Azure Load Balancer for failover cluster instance.

More details please refer to the following post:
https://community.spiceworks.com/topic/1970401-accessing-sql-cluster-ip-azure-vnet
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d0b8d906-b5f3-4903-a711-437fa696cc3c/errors-with-ping-and-sql-connection-azurevm-two-nodes-cluster?forum=WAVirtualMachinesforWindows
https://www.sqlservercentral.com/forums/topic/sql-2019-fci-in-azure-unable-to-connect-on-secondary-node

Error_401 86 Reputation points

2021-05-12T13:43:10.487+00:00

Hi, thank you for the reply - I get it that load balancer is something that has to be properly configured.

Now, I have completely moved the same architecture to Hyper-V, only the IPs have changed

Storage Server - 192.168.0.106
Cluster1 - 192.168.0.100
Cluster2 - 192.168.0.102
SQL Cluster - 192.168.0.101
Web Server - 192.168.0.108

The problem now I'm facing is that I can ping the SQL Cluster from everywhere but the SSMS fails to connect to the main SQL Server

WebServer is unable to connect to Cluster1
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T01:56:07.013+00:00

Hi,

It seems that the failover instance of SQL Server you installed is installed as a default instance.

Now, can you connect to the SQL Server failover cluster instance(FCI) through the virtual network name or IP address of the SQL Server FCI? This is also the recommended way to connect to the SQL Server failover cluster instance.

>The problem now I'm facing is that I can ping the SQL Cluster from everywhere but the SSMS fails to connect to the main SQL Server

You cannot remotely connect to SQL Server FCI by specifying the server name or IP address of the active node running SQL Server FCI.

In my test, the local connection can directly specify the server name of the active node to connect to the FCI without problems. But for remote connections, you must use the cluster virtual network name and IP address. This is by design.

If the answer is helpful, please click "Accept Answer" and upvote it.
Error_401 86 Reputation points

2021-05-13T06:38:36.473+00:00

Ok, then how do I connect by Visual Studio to SQL Server Cluster

What connection string should I add

I have an IIS Server hosting a test website on WebServer - 192.168.0.108
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T07:04:02.18+00:00

Hi，

Can you not connect to the SQL Server cluster by specifying the IP address or virtual network name of the SQL Server FCI?

The connection string depends on the driver you are using.
https://www.connectionstrings.com/sql-server/
Error_401 86 Reputation points

2021-05-13T07:23:02.043+00:00

No, the WeBServer is not communicating with the SQL Server Cluster

WebServer is able to ping SQLFOSERVER.demo.com - 192.168.0.101

Firewalls are all closed on all the servers
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T07:30:08.877+00:00

What the server name you specified in the "Connect to Server" dialog of SSMS. SQLFOSERVER.demo.com?

You should specify the SQLFOSERVER or 192.168.0.101
Error_401 86 Reputation points

2021-05-13T07:32:33.983+00:00
Yes, I tried with both -

192.168.0.101

SQLFOSERVER

but it did not work
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T07:36:29.767+00:00

hi,

Ok, have you enabled the TCP/IP protocol for the SQL Server cluster instance?

On active node, open the SQL Server Configuration Manager > SQL Server Network Configuration.
Error_401 86 Reputation points

2021-05-13T07:47:27.993+00:00

I already checked this earlier and it was enabled by default -
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T07:58:12.377+00:00

Hi,
OK, I understand. Actually this SQL FCI is installed as a named instance. Instead of the same configuration in Azure VMs when you posted this question.

the SQLFOSERVER is the SQL Server cluster instance name, not the virtual network name of the SQL Server FCI. Please check it in failover cluster manager.

For a named instance, please ensure the SQL Server Browsing is running on active node if you didn't specify a static IP address for this SQL FCI.

Now, if you ensure the 192.168.0.101 is the IP address of the SQL Server cluster, please try 192.168.0.101\SQLFOSERVER

If you check and find the virtual network name of the SQL Server FCI, you can try the virtual network name\SQLFOSERVER
Error_401 86 Reputation points

2021-05-13T08:53:13.62+00:00

This is the SQL Role running on Failover Cluster Manager -

What should I try then
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T08:57:55.937+00:00

192.168.0.101\SQLFOSERVER

SQLFOSERVER\SQLFOSERVER
Error_401 86 Reputation points

2021-05-13T09:20:56.803+00:00

For both, I'm getting this same error -
Cris Zhan-MSFT 6,661 Reputation points

2021-05-13T09:36:04.44+00:00

Actually I think the problem now is the login problem.

It is correct to use 192.168.0.101\SQLFOSERVER or SQLFOSERVER\SQLFOSERVER. This SQL Server cluster instance can already be found, but the connection fails due to login problems.

Check the error log for more detailed login failure messages.

What is the Windows account added when creating this fci instance? The account demo\Administrator seems not be added in SQL Server logins.

If the ''sa'' account is enabled, please log in with the ''sa''account.
Error_401 86 Reputation points

2021-05-13T17:00:57.217+00:00

Hi
Thank you a ton for all your help.
You were correct there was an authentication problem, though I have configured everything correctly the WebServer is unable to authenticate using the DEMO\Administrator account.

I enabled the sa account and now it is connecting for now.

Let me check if the Visual Studio connects to the database or not.

Will keep posting here for any issues.

Thanks again

Answer 2

Alex Bykovskyi 2,241

Hey,

Check firewall on your cluster nodes. For the testing purposes disable it on Cluster11 node and check if you can ping. In addition, the following guide might help:
https://www.starwindsoftware.com/resource-library/starwind-virtual-san-installing-and-configuring-sql-server-2019-tp-failover-cluster-instance-on-windows-server-2016/

Cheers,

Alex Bykovskyi

StarWind Software

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Error_401 86 Reputation points

2021-05-08T04:54:07.64+00:00

Thanks, Alex

I have already disabled firewalls on all the devices and since all these machines are VMs on Azure, I have created NSG and opened port 1433 on all VMs.

Share via

My Visual Studio is unable to connect to SQL Server

1 additional answer

Your answer