Static port in sql server

Question

Static port in sql server

murstfirst

Old Hand

Points: 342
More actions
January 5, 2023 at 5:29 am

#4133843

I'm on sql 2019 and I want to use static port 50010. I already set up an incoming firewall rule for port 50010 and deleted the default port 1433 firewall rule, however I can still connect to sql server using ssms without specifying the port number 50010 using "home\instance2019" instead of "home\instance2019,50010". What did I miss? I thought I was not supposed to be able to connect without the port number. I restarted the sql service on sql config manager too.
Side note on firewall rule:
Firewall rule Program and Services: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe
Advanced: Private

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 1

Please use : SQL server configuration manager to adjust your TCP/IP port. Always disable shared memory due to security reasons.

To change the port assignment right-click on the TCP/IP protocol and select Properties using the IP address tab

under SQL Server Network Configuration section.

DBASupport

murstfirst Old Hand Points: 342 More actions · Answer 2

Thanks for the reply. I tried writing down all the details in my question but I guess you didn't understand.

I was asking why I can still connect to sql server without using the new port number?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

The SQL Server Browser service will be the item which is allowing you to connect to the named instance on any port as that is what it is designed to do.

This service allows you to connect as either of the two below to successfully reach the instance.

home\instance2019

or

home,50010

If you want to force the connection string to use the port, disable the SQL Server Browser service.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

As a bit of a sidebar, we disable the SQL Server Browser as part of the security setup because it also kills some of the auto-discovery of machines.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

murstfirst Old Hand Points: 342 More actions · Answer 5

@Ant-Green

sql4

As you can see sql browser is disabled, static port is enable and I can connect without using the static port. Why I can still connect?

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

Are you connecting locally or are you actually crossing the network?

If so you're probably using named pipes rather than TCP IP

But to test TCP you need to be crossing the network, from another machine install SSMS and try to connect to the the instance across the network.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

murstfirst wrote:

I'm on sql 2019 and I want to use static port 50010. I already set up an incoming firewall rule for port 50010 and deleted the default port 1433 firewall rule, however I can still connect to sql server using ssms without specifying the port number 50010 using "home\instance2019" instead of "home\instance2019,50010". What did I miss? I thought I was not supposed to be able to connect without the port number. I restarted the sql service on sql config manager too.
Side note on firewall rule: Firewall rule Program and Services: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe
Advanced: Private

Quick question, what are the UDP rules for the firewall?

😎

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 8

I hope this site helps resolve your question:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver16

DBASupport

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 9

User datagram protocol (UDP) operates on top of the Internet Protocol (IP) to transmit datagrams over a network. UDP does not require the source and destination to establish a three-way handshake before transmission takes place.

DBASupport