Restart the affected SQL Server instance.Make the change to your port-based firewall to allow inbound traffic on the TCP port you've chosen.Either under IPAll or each individual enable adapter, provide the static port you want to use in the "TCP Port" field.You must leave it blank to use a static port. When 0 is provided in this field, SQL Server listens on a dynamic port. Either under IPAll or each individual enable adapter, delete the 0 in the "TCP Dynamic Ports" field.For many SQL Servers and most standalone instances, there is only one network adapter needed, though others like the loopback adapter will appear. If you want to disable unneeded network adapters, on the "IP Addresses" tab, you'll need to enable each adapter(s) desired, and configure the TCP Port setting for each. If the Listen All is Yes, only the TCP Port and TCP Dynamic Port values under the IPAll section will be honored. If you want to keep using Listen All, then on the "IP Addresses" tab, only use the IPAll section to set the static port. You can disable unused adapters, and on the "IP Addresses" tab, enable only the network adapters needed. SQL Server will listen on all network adapters, including the loopback adapter(s). By default, in the "Protocol" tab, you'll see that Listen All is enabled by default.(As a book author, can I just say how impossible it is not to notice little inconsistencies like the capitalization of "Sql" vs "SQL" in the same screen.) On the server, open Sql Server Configuration Manager, expand SQL Server Network Configuration, and go to Protocols for InstanceName, click on properties of the TCP/IP protocol.
![sql server port sql server port](https://www.mssqltips.com/tipimages2/2495_image004.png)
How do you configure a SQL Server instance to use a static port instead of a dynamic port? (This change won't take affect until you restart SQL Server service, so I recommend you only make this change during a maintenance window.) Instead, choose any port in that same range, for example the one most recently dynamically assigned.
![sql server port sql server port](https://1.bp.blogspot.com/-NDLwheAmShs/XtHDFQoDugI/AAAAAAAAHlQ/cMmQlpT_YA4B_4wgnfpYdExbC7EVl_CbACEwYBhgLKs4DAL1OcqyU5Fe7sL20c_Kf0TVyTjLcn5Gsqd3n_MCmTvvAa5he3l1g3nab5PeEAymfW71wfX2nQegIXFg84TOraCu4BtZAg8NKS9CCWCmzI0IJ9_QJ7yt9aQTAZwOgLgkepFS1NfcJU37MEfVV6Yn9LoDNUnecVdMD0pI5Y1tPwNrccYB25jMjxvwEyPVwenFW5KBFuUh6JV4twCaflBAF8-DRHgWbhq_UnK0-lbd5-ZYDLuJ6FxCpzFRVdHYLK97Ap1rsFEUIm18K4i_WgO5sIop3xpMT4ypQbrVZRkitIZpamfUDZ6q_7kNUCK4aZvd1ztLa8DCEmMR_2lS1TEls7oDP6J0dpyDNO8doGkB2NkC8DPZWe_20UtqMP6l94lnDTeR-fdKkYA-TjHUJHTtwrvoBCRPfaETsnO3WhpEaCyCbvBtKnLxP73ZHClJhpntv79AtgkluHmCf5Uu6Mr-7_t6DP59xoODG2_cxHmttY_jWnTrHl5RsRIObnXUOsunu_LZ7fws1rWZs2BW7miXUKhdArDrXoRTi2nW7yyzi-_ATn8yZ8-asW4_lRIVecVPyjjXJ6jLl4075puvp55N98XW0Ix_3LgpPPSc-5SSZMI2Lx_YF/s1600/3.png)
You can configure a named instance to use TCP 1433, though it may be misleading and confusing if another SQL Server default instance is ever installed on this server. The solution to the dynamic port problem is to change the SQL Server to use a static port. (The SQL Browser isn't required, if you'd rather always specify a port number in all connection strings, or set up SQL alias(es).) Connections to SQL Server from a client running on the same server always try to use Shared Memory first, and are faster for it.)įor connections external to the SQL Server, the SQL Browser (UDP 1434) handles the connection and routes traffic to the correct TCP port for each instance, even if there is only one instance on the server. (But why do local connections still work, like a local installation of SQL Server Management Studio or an application running on the same server as SQL Server? Because those connections probably aren't using using the TCP protocol but the Shared Memory protocol. This is incompatible with port-based firewall rules unless you unwisely choose to specify a huge range of port exceptions for your firewall (not recommended).
![sql server port sql server port](https://www.engisv.info/wp-content/uploads/2017/06/Static_port-SQL-3.png)
Most importantly, a SQL Server using a dynamic port will change port every time the Database Engine service starts up. Named instances can be configured to use TCP 1433, but by default, named instances are configured to use dynamic ports in a range between 49152–65535.
SQL SERVER PORT WINDOWS
A Windows Server can host many SQL instances, but only one can be the default instance, and there doesn't have to be a default instance.ĭefault instances use TCP 1433 by default, but this isn't "the default port for SQL Server" necessarily. This was a pretty simple problem to solve, and not uncommon to face right after installing a SQL Server named instance.Įven though there was only one SQL Server instance on the server, the instance was installed as a named instance ( servername/instancename), not the default instance ( servername). Looking at their firewall logs, they saw connections blocked on port TCP 49153. Recently I had a client and their vendor trying to configure a firewall for a new installation of SQL Server 2019 on Windows Server 2019.Ĭonnections to the SQL Server were being blocked by the firewall.