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?

Improve this question
0

4 Answers 4

Reset to default
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

Improve this answer
0
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.

Improve this answer
0
5

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.

Improve this answer
1
  • I had the same issue. Previous solution did not work for me. But this one worked. Tnx.
    – Mansoor Omrani
    Commented Sep 12 at 11:15
1

If port 1433 is not occupied in TCPview and netstat, you can use net stop winnat command.

Improve this answer

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.