'SQL Server not listening on IP address even if TCP/IP is enabled
I am trying to set up MS SQL Server Express 2019 to listen on TCP/IP and connect to it through IP address instead of shared memory. I am currently connecting through Windows Authentication and the SQL Server Management Studio (SMSS).
In the Configuration Manager, I have enabled TCP/IP and the desired IP address (192.168.0.198, port 1433). I have checked that "Listen All" is enabled, as is "Allow remote connections to this server" in SMSS. I also restarted the service after enabling TCP/IP.
Image: Setting up the IP address
However, when I try to connect, it gives an error.
Image: Error when connecting to server
Is it required to set up an account, does Windows Authentication not work with IP address? I also could not ping or otherwise locate the server listening. In the Windows Resource Monitor, there is no service listening on 1433. What could be the reason for this?
Thank you.
Solution 1:[1]
On TCP/IP remove the 0(zero) in TCP Dynamic Ports.
Solution 2:[2]
if "Listen on all ips" is set to true/yes then it uses the port in the last entry on TCP/IP networking settings. IF you have 5 ips (localhost, public ip, etc) then you have to scroll down (might not be in view). There is a settings for "all ips" with a port number like 53477. You can either pick "NO" to listen to all ips option, change the port, or use the 53whatever port # listed to connect. check firewall settings too.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | vinny james |
| Solution 2 | Waitman |