-2

I want to connect to SQL Server Express on localhost using the default static port, 1433, instead of dynamic ports. I want to use both IPv4 and IPv6 to connect, so I want to be able to connect both with 127.0.0.1 and [::1].

I've managed to configure SQL Server Express to listen at port 1433 on 0.0.0.0 via SQL Server Configuration Manager but I am not still not able to make SQL Server listen at port 1433 on local interface [::1] and 127.0.0.1.

Any ideas?

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
terms of service 212
  • 1
  • 2
  • 1
    SQL Server Express is a named instance which means it doesn't listen *just* on 1433. Don't change any configuration settings. Nothing is broken. You risk breaking things if you modify them – Panagiotis Kanavos Sep 17 '20 at 16:09
  • 1
    What is the *actual* problem? Are you trying to connect from a client application? Using what language, what connection string? All clients can handle named instances. Perhaps your firewall needs configuring? You can find the specifics in [Configure Windows Firewall](https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15#ports-used-by-) in the docs – Panagiotis Kanavos Sep 17 '20 at 16:10
  • Or perhaps the connection string is wrong? Since SQL Server Express is a named instance, you need to include the name in the connection string, eg `.\SQLEXPRESS` or `127.0.0.1\SQLEXPRESS`. – Panagiotis Kanavos Sep 17 '20 at 16:14
  • I was trying to assign a port to the SQL Server Express on 127.0.0.1 and [::1] but both didn't work. Port assignment worked only on 0.0.0.0 – terms of service 212 Sep 17 '20 at 17:54
  • Yes I've tried. It's not about the Windows Firewall cos running netstat -aon shows the port 1433 attached to 0.0.0.0 and not 127.0.0.1 or [::1] Thanks for your time – terms of service 212 Sep 17 '20 at 17:59
  • Thanks for your time Panagiotis Kanavos. Good bye – terms of service 212 Sep 17 '20 at 18:01
  • 1
    I strongly suggest you read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). Bad questions don't just waste the answerers time. They can harm others that may have the same problem and can end up harming their machines by applying the wrong solutions – Panagiotis Kanavos Sep 17 '20 at 18:04
  • I think I am quite clear about my requirements. It solved my problem. – terms of service 212 Sep 17 '20 at 18:12
  • 1
    The original question wasn't clear. The title at least asked something completely different from what you described in the comments. I edited the question to match the comments and removed the close vote. You'll have to convince the other guy that downvoted to remove the downvote – Panagiotis Kanavos Sep 18 '20 at 05:10
  • 1
    You should understand that people taking hours to ask for clarification are trying to help not just you but other people that may have the same problem. They aren't paid to do this. SO is a Q&A site, not a discussion forum. A question should be useful not just to the one asking but to anyone else having the same problem. This means it needs to be clear. – Panagiotis Kanavos Sep 18 '20 at 05:13

1 Answers1

2

0.0.0.0 called "IPALL" in the SQL Server Configuration Manager causes the SQL Instance to listen on that port on all current and future IP addresses, including the IP4 and IP6 loopback addresses.

If you want SQL Server to listen only on the loopback addresses, disable "Listen All" for TCP/IP in the Configuration manager and enable and configure the two loopback ip addresses seperately.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Oh my! I missed out this configuration setting! This works! – terms of service 212 Sep 17 '20 at 17:57
  • 1
    @termsofservice212 no you didn't. It's not needed at all. In fact, if you do that, you won't be able to use any other instance on your machine. If you install SQL Server Developer edition, you won't be able to connect to it as a default instance, you'll have to create another named instance – Panagiotis Kanavos Sep 17 '20 at 18:02
  • Uh. I don't need any another instance on my machine. I know what I need. Thanks. – terms of service 212 Sep 17 '20 at 18:03
  • @PanagiotisKanavos You can have additional instances on the machine. Each just needs to listen on a different port, or not have TCP/IP enabled. Developer and Express don't even have TCP/IP enabled by default after install. – David Browne - Microsoft Sep 17 '20 at 18:26
  • 2
    I know. None of this would be needed if Express's configuration isn't changed though. – Panagiotis Kanavos Sep 17 '20 at 19:12
  • 1
    Had the OP searched how to use a static port with SQL Server Express, or how to use Express as a default instance, [Change a Sql-Server (express) from a named instance to localhost?](https://stackoverflow.com/questions/17281071/change-a-sql-server-express-from-a-named-instance-to-localhost) would have come up. In fact, had I understood that was the actual question, I'd have closed it as duplicate. – Panagiotis Kanavos Sep 17 '20 at 19:20
  • 1
    Me too. I initially read the question that he wanted TCP/IP but to not expose the instance on external IP address, only loopback addresses. – David Browne - Microsoft Sep 17 '20 at 19:21
  • Guys, very much appreciate your help. I have no idea i could change the sql-server express from a named instance to localhost. That why this question came about. If it pleases you so much, please mark this as duplicate. – terms of service 212 Sep 19 '20 at 06:38
  • 1
    "Me too. I initially read the question that he wanted TCP/IP but to not expose the instance on external IP address, only loopback addresses" David Browne, this is what i"ve asked help on. You replied with the correct answer. I think i am pretty sure you understood what i wrote, because you've hit the nail on the head. Thanks for your help again. – terms of service 212 Sep 19 '20 at 06:43