Can't Connect to SQL Server 2019 on Home Network

Question

I setup and installed SQL Server 2019 on one of my computers on my home network. I'm trying to connect to it through SQL Server Management Studio setup on another computer on my home network.

I followed all of the steps listed here: SQL Server instructions

TITLE: Connect to Server
------------------------------

Cannot connect to DESKTOP-6CKIO3T.

------------------------------
ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 5)

------------------------------

Access is denied

This is what I have done so far to try to fix this issue:

• Enabled remote connections on SQL Server
• Enabled and activated all ip addresses through the SQL Server Configuration Manager
• Fully disabled the Windows firewall on the SQL Server computer and the ssms computer
• Turned on file sharing, network discovery, and media streaming on both computers
• Tried to connect using computer name, local ip, external ip, and other variations
• Changed the timeout values to 0 to eliminate that as a possible issue
• Made sure that sql server and sql browser services are running

Am I missing anything or doing anything wrong?

UPDATE: Using the browsing option under the server name, I'm able to detect the sql server running on the remote computer. I'm still not able to connect to it

Answer 1

1. Open SSMS Go to Properties click on the connection and allow remote connections to this server as shown in image 1 Image1

2. open SQL Server Configuration manager Click on SQL Server Network Configuration and click on Protocols for MSSQLSERVER as shown in image 2 Image

3. Click on TCP/IP and Enable then Restart SQL server then open Properties of TCP/IP and click on IP Addresses and scroll down and find "IPALL" then Give TCP Port 1433 click ok as shown in image 3 image

4. Alter that open windows firewall and click on inbound Rules then create a new Rule select PORT and Click next another window will show you select TCP and Specifies the PORT 1433 click on ok next, next and Give the name of Rule then try to connect from same network using SQL Server Authentication FireWallImage1

Answer 2

There are possible multiple reasons why you cannot connect. MS provides extensive checklist to resolve it.

Solving Connectivity errors to SQL Server

What does this guide do?

Majority of connectivity issues to SQL server, can be solved by going through a simple checklist and a sequence of easy steps. This guided walk through aims at providing the same for various connection errors that connecting to SQL Server.

Who is it for?

Anyone who is working with SQL Server and having connection issues.

How does it work?

Before you delve into troubleshooting specific connectivity errors, we recommend, though not mandatory, you gather the prerequisite items and run down a quick checklist. Though they may appear to take an extra few minutes of your time they can help get to the resolution sooner.

Once you exclude all options it would mean you hit something really unusual.

Answer 3

Just in case any others have this same issue, I had it too, and I had to specify the port when trying to connect from a different computer using SSMS. After validating firewall rules and TCP listeners are enabled/configured on the SQL Server, then in SSMS, connect using something like:

hostname\sqlinstanceName,port

Example: sqlsrvr.homelab.local\sqlexpress,1433

Even though I was using the default port (1433), SSMS would not connect unless I specified it.

My Home Lab is using Hyper-V on a dedicated box, no AD servers, just workgroup machines, and am using SQL auth (due to no AD in the lab). SQL 2019 express installed on a windows server 2019 VM accessing it remotely from windows 10's over the lab network.

I am not sure if the lack of AD changes things, but was strange that SSMS would not connect unless I added the port. With the port specified, it connects right away.