Common SQL Server TCP and UDP Ports

TCP 80

Default port for HTTP connections.  Typically used for report server access.

TCP 135

Used by Transact-SQL debugger, but is also used to control SQL Server Integration Services.

TCP 443

Default port for HTTPS connections.  Typically used for report server access using secure socket layer (SSL) connections.

TCP 445

SMB port for use with named pipes protocol.   The SQL Server is configured to listen for incoming client connections by using named pipes over a NetBIOS session, SQL Server communicates over TCP port 445, just like file and printer sharing.

TCP 1433

Default port for SQL Server clientconnections.  The SQL Server can be reconfigured to listen on other ports, but 1433 is the most common implementation.

TCP 1434

Default port for Dedicated Admin Connections.

UDP 1434

The SQL Server Browser listens on this port for incoming connection requests, and responds on the TCP port of the named instance.  

TCP 2382

Default port for the SQL Server Browser to listen for connection requests to a named instance of Analysis Services.

TCP 2383

Default port for SQL Server Analysis Services.

TCP 4022

Default port for SQL Server ServiceBroker.

The commands below can be used with Command Prompt to open the firewall ports required by SQL. 

 

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
@echo Enabling Dedicated Admin Connection port 1434
netsh advfirewall firewall add rule name = "SQL Admin Connection" dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN
@echo Enabling SQL Server Service Broker port 4022
netsh advfirewall firewall add rule name = "SQL Service Broker" dir = in protocol = tcp action = allow localport = 4022 remoteip = localsubnet profile = DOMAIN
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh advfirewall firewall add rule name = "SQL Debugger/RPC" dir = in protocol = tcp action = allow localport = 135 remoteip = localsubnet profile = DOMAIN
@echo Enabling Named Pipes Access port 445
netsh advfirewall firewall add rule name = "SQL Named Pipes Access" dir = in protocol = tcp action = allow localport = 445 remoteip = localsubnet profile = DOMAIN
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh advfirewall firewall add rule name = "Analysis Services" dir = in protocol = tcp action = allow localport = 2383 remoteip = localsubnet profile = DOMAIN
@echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name = "SQL Browser" dir = in protocol = tcp action = allow localport = 2382 remoteip = localsubnet profile = DOMAIN
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh advfirewall firewall add rule name = "HTTP" dir = in protocol = tcp action = allow localport = 80 remoteip = localsubnet profile = DOMAIN
@echo Enabling SSL port 443
netsh advfirewall firewall add rule name = "HTTPS" dir = in protocol = tcp action = allow localport = 443 remoteip = localsubnet profile = DOMAIN
@echo Enabling port for SQL Server Browser Service
netsh advfirewall firewall add rule name = "SQL Browser UDP" dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh advfirewall set currentprofile settings unicastresponsetomulticast enable

Anti-Ransomware File System Resource Manager Lists

FSRM is a server role that can be used to protect network shares from crypto-variant viruses from encrypting files to your server.

Once an infection has been identified by FSRM, a notification email can be sent for further investigation.

The filename patterns used are obtained from ā€“ https://fsrm.experiant.ca

This website already provides a link for a PowerShell script that installs the FSRM role, and downloads the required filename patterns.

I decided to not use the provided PowerShell script, and Iā€™m using the simplified script below instead.

After manually installing the FSRM role I ran the following PowerShell script to create the required file screening group.

new-FsrmFileGroup -name "Anti-Ransomware File Groups" -IncludePattern @((Invoke-WebRequest -Uri "https://fsrm.experiant.ca/api/v1/combined").content | convertfrom-json | % {$_.filters})

Once the file group “Anti-Ransomware File Groups” has been imported, a file screen can be manually created for a server drive or individual folders.

To update the file group change new-FsrmFileGroup to set-FsrmFileGroup in the PowerShell script.