15

This isn't a duplicate; my scenario is different from others I've found:

MSSQLSERVER service does *not* start, port 1433 in use https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/10/05/tcp-port-is-already-in-use/ https://support.plesk.com/hc/en-us/articles/213409409

Differences:

  • Neither cls & netstat -oanb -p tcp nor TCPView reveal anything listening on port 1433
  • According to the EventLog message, this is occurring on IPV6
  • The problem doesn't manifest for two named instances running on the same machine—only the default instance

To my knowledge, I'm not using IPV6 in any way, shape or form. I certainly haven't configured it.

The service will start when I make any of these configurations:

  • Disable TCP/IP in SQL Configuration Manager
  • Enable TCP/IP but turn off Listen All in TCP/IP Properties
  • When Listen All is off, disable all IPs on the IP Addresses tab

If I do either of these, the service fails to start:

  • Turn on Listen All
  • Enable any IP address

This has got me pulling my hair out.

What's gone wrong here?

0

3 Answers 3

8

Because netstat doesn't indicate that anything is listening on port 1433, that leaves the possibility that a client is already using port 1433 when SQL tries to start. From the SQL Server Premier Field Engineer blog post about "TCP Port is already In Use":

The second situation involves a client running on the server using the same dynamic port as SQL Server. Every TCP session requires two endpoints – a client endpoint and a server endpoint. Each endpoint is an IP Address/Port combination - also known as a TCP socket. Normally, the client socket code will ask the OS to provide a port from a range of dynamic ports. The server socket is the IP/TCP port of the service the client is connecting to (SQL Server for example).

The default dynamic port range might include port 1433. You can check it with netsh int ipv4 show dynamicportrange tcp.

...if you happen to have a client (think Windows OS system services) that starts BEFORE SQL Server does, it could use TCP/1433 for its client socket. ... So, how do you fix and prevent a situation like this? There's another feature within the OS that allows you to tell the OS the TCP ports in the Dynamic Port Range that should be EXCLUDED. All that is needed is to run the following with an Administrative Command Prompt:

netsh int ipv4 add excludedportrange tcp startport=1433 numberofports=1 store=persistent

After that setting is made, the OS will not give any clients that port. Services can listen on it still, but you prevent clients from getting it when asking the OS for a dynamic port.

IPv6 version

The original poster indicated that even though the logged error mentioned IPv6, his problem turned out to be with IPv4. But if it had been an IPv6 port conflict, the same process can be followed for IPv6.

netsh int ipv6 show dynamicportrange tcp

netsh int ipv6 add excludedportrange tcp startport=1433 numberofports=1 store=persistent

7
  • 1
    You may want to edit the post as all of those commands are for IPv4 but the OP is asking about IPv6. Commented Feb 21, 2019 at 11:21
  • @SeanGallardy Good point. I've edited my answer.
    – Doug Deden
    Commented Feb 21, 2019 at 15:35
  • i'm having the exact same issue, excluded ports include 1433, no luck :( changing port to something up, worked... Commented Mar 18, 2020 at 15:41
  • When trying to exclude the port I get a different error: The process cannot access the file because it is being used by another process.
    – AlexPi
    Commented Jan 19, 2023 at 21:29
  • @AlexPi Are you saying that you get the "The process cannot access the file..." error when you run the netstat command with the excludedportrange option?
    – Doug Deden
    Commented Jan 20, 2023 at 0:10
9

It might be over a year, but I do hope this will help others as well.

I could not confirm this, but it looks like the same SQL server instance like to assign the same port value to all the ports.

You'll need to open SQL Server Configuration Manager (If in case you cannot find it, https://blog.sqlauthority.com/2019/03/01/sql-server-sql-server-configuration-manager-missing-from-start-menu/)

enter image description here

Only assign one IP to use port 1433 and configure others for dynamic value, by setting TCP Dynamic Ports to 0 and blank off the TCP Port value. I chose 127.0.0.1 (loopback IP @ local) to bind with. Restart your SQL Server service.

2
  • Nice find on that missing SSCM. That's been plaguing me since forever. +1
    – InteXX
    Commented Oct 26, 2020 at 20:07
  • such a life saver
    – m'hd semps
    Commented Jan 29, 2023 at 18:55
4

I had this same problem recently while upgrading to a newer sql server I used TCPview and netstat but did not see the TCP port being used. I ended up trying everything here with no success. What worked for me was stopping and starting the Host Network.

net stop hns then restarting with net start hns then restarting the sql server from Configuration manager.

1
  • 1
    This worked for me...but I fear it may be a bandaid on a cancer. It still begs the question, "what was hanging on to that port, to cause this conflict". Like others here, I did not find that 1433 in use, in NetStat (or Windows Resource Monitor's "network" tab and its "listening ports"). So while this restart of HNS (which I did from "services", rather than the cli) did allow me to now start sql server (which I also did from "services", rather than the SQL config mgr), we have to wonder if some circumstance resolved by that HNS restart might recur. Anyway, thanks Peter. Commented Jan 29 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.