I cannot connect remotely to my SSMS database

Question

Hello. I am trying to remotely connect to my ssms database. I looked at tutorials and they told me i should enable and make some things. I did everything. Enabled TCP/IP in Configuration, Opened a port, and still nothing. i was searching for 2 hours and i cant find a solution. The database is on my host machine and i was trying to connect via ssms that is on VM. i dont think that is an issue because i asked my friend to connect with his PC and he also couldn't

EDIT: i enabled remote connections in server properties

EDIT #2: Error Message: Cannot connect to ***************\SQLEXPRESS 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

EDIT #3: I didnt port forward anything because i cant port forward on my network for some reason. I dont know if that inpacts anything i just read one aricle online and said that port forwarding is (optional)

Answer 1

Hi @David Molnar What's the error message?

If encountered error like this: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Then troubleshoot issue follow by this doc: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Also, see this doc: Troubleshoot connectivity issues in SQL Server.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It sounds like you are using Hyper-V, have a database on the host, and are connecting from a copy of SSMS running on an OS in a VM. Correct?
If so, you will have a basic routing issue between the VM OS and the host OS, rather than anything to do with SQL Server.
What IP range is being assigned within the VM? Does the host OS have a route table entry that points back to that IP for the VM? (Otherwise, it will be sending return packets to the Internet and not to the VM).
Can you ping the host from the VM and get a reply? Can you ping the VM from the host and get a reply?

Answer 3

Hi @David Molnar
Please check this blog which discussed about most of the connectivity errors and possible solution. Thank you!

Answer 4

OK, this is how I do that:

1. In Hyper-V Manager, open Virtual Switch Manager.
2. If you don't already have an Internal only network, create one. (I'll call it HostInternalNetwork). For connection type, use Internal network. Enable the option for the VLAN ID.
3. That will make a network adapter appear on your host machine. Open a command window, and execute ipconfig to see the IP addresses. Find the IPv4 address for the HostInternalNetwork (or whatever you called it). On my machine, that's 192.168.241.1
4. Right-click the Guest VM that's running SQL Server in Hyper-V Manager, and choose Settings. Make sure the Network Adapter is connected to HostInternalNetwork (or whatever you called it)
5. Start the guest VM and use the Connect option to log on.
6. In the guest VM, open the properties of the network adapter, and choose to modify the IPv4 configuration. Set it to a fixed address of 192.168.241.2 (it needs to be a different address from the address in step 3 but on the same network). My subnet mask would be 255.255.255.0.
7. Open the Windows Firewall in the guest VM. Add an Inbound Rule by clicking New Rule.
8. Choose a Pre-defined rule type: File and Printer Sharing. On the Predefined rules page, pick File and Printer Sharing (Echo Request ICMPv4-In), then on the Action page, click Allow the connection and Finish.
9. At this point, from the guest OS you should be able to pint 192.168.241.1 and from the host OS you should be able to ping 192.168.241.2.
10. Add another Inbound Rule by clicking New Rule. Choose a Port rule. On the Protocol and Ports page, choose TCP and Specific local ports: 1433. Then Next, and then on the Action page, click Allow the connection and Next. Default your way through the next screens, then call the rule Inbound SQL Server, and Finish.
11. In the guest VM, make sure that SQL Server Network Configuration > Protocols for MSSQLSERVER has TCP enabled. After you enable it, you will need to restart it.
12. You should then be able to connect from SSMS on the host using the IP address 192.168.241.2 as the server address. Note you will need to use a SQL login as you don't have Windows authentication set up in the guest VM. (presumably)
13. The final thing that might make it easier for you, is to add an entry to your OS hosts file with the name of the guest VM and pointing to 192.168.241.2. Then you could connect to it by name. I'm hoping that gets you going.

Answer 5

For anyone reading this i managed to fix it. i found on some random lession from microsoft that i needed to connect like this:
Server Name: tcp:<your public ip adress>,<port that you put for tcp/ip connection> and now it works.

Big thank you to everyone especially Greg Low for replying and trying to help me with my problem.