Month: March 2021

MSSQL Server: Opening the Required Firewall Ports

Firewalls are obviously a very important and fundamental part of network security. However, that doesn’t make them any less annoying when you’re working in development or just trying to learn the nitty gritty. You may be tempted to just disable the firewall. This will work and you’ll be on your merry way but you’ve learned nothing and when its time to deploy it can be a headache.

When it comes to Microsoft SQL Server, the default instance is easy enough. It operates on port 1433 out-of-the-box. Lets look at how to open this port a couple of different ways.

Using the GUI

To open it up for remote access, simply open Windows Defender Firewall -> Advanced Settings -> Inbound Rules -> New Rule… then follow the wizard:

Select Port and click Next.

New Inbound Rule Wizard: Rule Type

Select TCP and type 1433 into the Specify local ports field

New Inbound Rule Wizard: Protocol and Ports

Select Allow the connection and click Next.

New Inbound Rule Wizard: Action

Select the Network Profiles that you want the rules to apply to.

New Inbound Rule Wizard: Network Profile

Give the rule a name and description the click Finish.

New Inbound Rule Wizard: Name

Using netsh advfirewall

Using the GUI can be time consuming, if you have multiple machines were you need to open ports we can use the netsh utility to administer the firewall through the command line or a batch file.

Running this command will create an inbound rule that opens communication on port 1433 for the domain network profile:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail. A named instance uses dynamic ports.

The following table lists the ports that are frequently used by the Database Engine.

ScenarioPortComments
Default instance running over TCPTCP port 1433This is the most common port allowed through the firewall. It applies to routine connections to the default installation of the Database Engine, or a named instance that is the only instance running on the computer. (Named instances have special considerations. See Dynamic Ports later in this article.)
Named instances with default portThe TCP port is a dynamic port determined at the time the Database Engine starts.See the discussion below in the section Dynamic Ports. UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.
Named instances with fixed portThe port number configured by the administrator.See the discussion below in the section Dynamic Ports.
Dedicated Admin ConnectionTCP port 1434 for the default instance. Other ports are used for named instances. Check the error log for the port number.By default, remote connections to the Dedicated Administrator Connection (DAC) are not enabled. To enable remote DAC, use the Surface Area Configuration facet. For more information, see Surface Area Configuration.
SQL Server Browser serviceUDP port 1434The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance. Normally the SQL Server Browser service is started whenever named instances of the Database Engine are used. The SQL Server Browser service does not have to be started if the client is configured to connect to the specific port of the named instance.
Instance with HTTP endpoint.Can be specified when an HTTP endpoint is created. The default is TCP port 80 for CLEAR_PORT traffic and 443 for SSL_PORT traffic.Used for an HTTP connection through a URL.
Default instance with HTTPS endpointTCP port 443Used for an HTTPS connection through a URL. HTTPS is an HTTP connection that uses Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL).
Service BrokerTCP port 4022.
There is no default port for SQL ServerService Broker, but this is the conventional configuration used in Books Online examples.
Database MirroringAdministrator chosen port.

There is no default port for database mirroring however Books Online examples use TCP port 5022 or 7022. It is important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specify a Server Network Address (Database Mirroring).
ReplicationReplication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.)

Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File Sharing. File sharing uses UDP port 137 and 138, and TCP port 139 if it using NetBIOS. File Sharing uses TCP port 445.		For sync over HTTP, replication uses the IIS endpoint (ports for which are configurable but is port 80 by default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance.

During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher, not between subscriber and IIS.
Transact-SQL debuggerTCP port 135

See Special Considerations for Port 135

The IPsec exception might also be required.		If using Visual Studio, on the Visual Studio host computer, you must also add Devenv.exe to the Exceptions list and open TCP port 135.

If using Management Studio, on the Management Studio host computer, you must also add ssms.exe to the Exceptions list and open TCP port 135.

Service Broker and AlwaysOnAG (mirroring) don’t have defaults. If you need to check what these ports are configured as on an existing system, connect via SSMS or SQLCMD and run the following queries:

AlwaysOn AG (Mirroring):

SELECT name, protocol_desc, port, state_desc 
   FROM sys.tcp_endpoints
   WHERE type_desc = 'DATABASE_MIRRORING'

Service Broker:

SELECT name, protocol_desc, port, state_desc
   FROM sys.tcp_endpoints
   WHERE type_desc = 'SERVICE_BROKER'

Batch Script

Below is an example batch script that creates all of the aforementioned firewall rules. Modify the ag_endpnt and sb_endpnt variable at the beginning if your ports are different. Script should be executed from an elevated command prompt or powershell window. Provided for educational purposes only and as always don’t run anything in production that you haven’t tested first.

rem OpenSQLServerPorts.bat
rem Date Modified: 03/26/2021
rem Author: Avery Lane

set "ag_endpnt=5022"
set "sb_endpnt=4022"

rem SQL Default Ports Inbound/Outbound
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPort dir = out protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

rem SQL Dedicated Admin Port Inbound (Default Instance)
netsh advfirewall firewall add rule name = SQLPortDedicatedAdmin dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem SQL Database mirroring/AlwaysOn AG (Default is 5022)
netsh advfirewall firewall add rule name = SQLPortAG dir = in protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortAG dir = out protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN

rem SQL Browser Service
netsh advfirewall firewall add rule name = SQLPortBrowser dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem Service Broker (Default is 4022)
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = in protocol = tcp action = allow localport ="%sb_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = out protocol = tcp action = allow localport = "%sb_endpnt%" remoteip = localsubnet profile = DOMAIN

rem Replication
netsh advfirewall firewall add rule name = SQLReplUDP dir = in protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = in protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

netsh advfirewall firewall add rule name = SQLReplUDP dir = out protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = out protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

Output of netsh advfirewall firewall add rule ?

Usage:
      add rule name=<string>
      dir=in|out
      action=allow|block|bypass
      [program=<program path>]
      [service=<service short name>|any]
      [description=<string>]
      [enable=yes|no (default=yes)]
      [profile=public|private|domain|any[,...]]
      [localip=any|<IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
         <IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [localport=0-65535|<port range>[,...]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
      [remoteport=0-65535|<port range>[,...]|any (default=any)]
      [protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
         tcp|udp|any (default=any)]
      [interfacetype=wireless|lan|ras|any]
      [rmtcomputergrp=<SDDL string>]
      [rmtusrgrp=<SDDL string>]
      [edge=yes|deferapp|deferuser|no (default=no)]
      [security=authenticate|authenc|authdynenc|authnoencap|notrequired
         (default=notrequired)]

Remarks:

      - Add a new inbound or outbound rule to the firewall policy.
      - Rule name should be unique and cannot be "all".
      - If a remote computer or user group is specified, security must be
        authenticate, authenc, authdynenc, or authnoencap.
      - Setting security to authdynenc allows systems to dynamically
        negotiate the use of encryption for traffic that matches
        a given Windows Defender Firewall rule. Encryption is negotiated based on
        existing connection security rule properties. This option
        enables the ability of a machine to accept the first TCP
        or UDP packet of an inbound IPsec connection as long as
        it is secured, but not encrypted, using IPsec.
        Once the first packet is processed, the server will
        re-negotiate the connection and upgrade it so that
        all subsequent communications are fully encrypted.
      - If action=bypass, the remote computer group must be specified when dir=in.
      - If service=any, the rule applies only to services.
      - ICMP type or code can be "any".
      - Edge can only be specified for inbound rules.
      - AuthEnc and authnoencap cannot be used together.
      - Authdynenc is valid only when dir=in.
      - When authnoencap is set, the security=authenticate option becomes an
        optional parameter.

Reference Links & More Information

BASE and CAP

BASE Model

NoSQL (actually short for “not only” SQL) often times rely on a softer transaction model than ACID abbreviated as BASE:

  • Basically Available
  • Soft State
  • Eventually Consistent

Basically Available

This has to do with the perceived availability of the data. The idea is for data to always be available even if it is not in a consistent or hardened state. There are no isolation levels. One transaction may access a file that is being actively modified by another.

Soft State

Values stored in the database may change because of the eventual consistency model.

Eventually Consistent

Data and changes to said data are replicated across nodes and eventually become consistent. For example, in Hadoop, when a file is written to the HDFS, the replicas of the data blocks are created in different data nodes after the original data blocks have been written. For the short period before the blocks are replicated, the state of the file system isn’t consistent.

As tongue in cheek as BASE is, it’s a bit misleading. It is not meant to be the polar opposite of ACID because most NoSQL implementations do not abandon all ACID principles. Soft State and Eventually Consistent are also so closely related that their distinction serves no real purpose.

The point though, is that a BASE model uses a more relaxed approach to consistency for the sake of being able to scale horizontally much more easily and provide a high degree of availability.

This model is very popular with social media platforms and search engines where “some (maybe partial) results now” is much more important than “all results no matter how long it takes”.

CAP Theorem

Also known as Brewer’s theorem, named after computer scientist Eric Brewer, proposes that it is only possible for a distributed data store to provide two out of the three following guarantees:

Consistency: Every read receives the most recent write or an error

Availability: Every request receives a (non-error) response, without the guarantee that it contains the most recent write

Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes

Transactions and ACID

A database transaction is a unit of work performed against a database that is independent of other transactions. The purpose of a transaction is two-fold:

  1. To provide a reliable record and means to recover in case of failure when performing work.
  2. To provide isolation between applications or processes that are accessing data concurrently.

In any good relational database management system, transactions adhere to a set of strict rules or properties abbreviated as ACID:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity

Guarantees that either all operations in the transaction succeed or they all fail. This prevents partial updates that could leave a database in a questionable or incomplete state. For instance, imagine your database transaction was a financial transaction that transferred money from one account to another. That would consist of two operations: a deduction from account A and a deposit into account B. This is an obvious example where we want both operations to succeed or both to fail. If only one operation was successful we would either be destroying money or inventing new money from nothing (something apparently only the federal reserve is allowed to do).

Consistency

Is similar to atomicity but has more to do with referential integrity. This is where constraints come into play, such as a primary key. If you have an ID column used to track rows based on a unique identifier, you wouldn’t want to allow multiple rows to have the same value for ID; if there were the database would be in an inconsistent state. A transaction that preserves consistency ensures that the database is in a consistent state when the transaction begins and when the transaction ends.

Isolation

This protects transactions from the effects of other transactions via mechanisms like locks and row versioning. Database management systems typically employ different levels of isolation. For instance, in Microsoft SQL Server, the READ UNCOMMITTED isolation level allows transactions to read data that other transactions are modifying, while the stricter SERIALIZABLE isolation level will lock ranges of rows or entire tables to prevent the reading of data until the transaction modifying data completes and commits.

Durability

A transaction is considered durable when it commits and stays committed. This is accomplished by flushing the data to a stable/nonvolatile media (think disk vs in-memory) although as storage mediums continue to advance and innovate, the implementation may change. The important thing is that the work is saved and is not prone to being lost or rolled back without intervention.

Microsoft SQL Server, Oracle, MySQL, and PostgreSQL are some of the most popular database systems which follow ACID properties by default.