12

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

Improve this question
13
  • Are you using windows authentication or are you using sql authentication with a username and password? Commented Mar 10, 2020 at 17:44
  • Which edition (Express, Web, Standard) did you install? And how are you trying to connect to it?? What server/instance name are you trying?¨ Commented Mar 10, 2020 at 17:49
  • @Karamafrooz I'm using sql auth with a username and password Commented Mar 10, 2020 at 17:50
  • 1
    THe most stupid error that normally many people overlook: FIREWALL. SQL Server is NOT opening the firewall in the setup, and he firewall normally blocks all incoming access. Commented Mar 10, 2020 at 19:05
  • 1
    That error is not a authentication error, it's a network error. Please follow the guide in the answer posted below. Commented Mar 15, 2020 at 9:55

3 Answers 3

Reset to default
13

  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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

7
+25

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.

Improve this answer

Comments

2

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.