dutyDBA.com

As a DBA, you often provision a new database to a user or a team, and provide them with SQL Server connectivity details like the name of the SQL Server, the port it is listening on, and the login details. Sometimes due to network oonnectivity issues or firewall issues, the users come back to you saying they are unable to connect to their SQL Server.

In this article, I am going to address the scenario where the necessary SQL Server ports are not opened in the firewall, which prevents the users from obtaining a successful connection to their SQL Server. The issue with firewall is more common in today’s world of cloud computing, be it a private/internal cloud or public/external cloud. In this case, many ports are not allowed through the firewall by default.

To resolve the firewall related connectivity issues, you as a database administrator are required to prove that there is a firewall issue, so that the users can get the necessary firewall rules in place by liaising with the relevant network or security team in your organisation.

How do you go about checking if a port is open or not in the firewall?

A PowerShell based solution:

Its quite easy to check if a TCP/IP port is open or not between a client machine and SQL Server using the Test-NetConnection cmdlet in PowerShell.

Simply run the below PowerShell script from the user’s computer, or even better, ask the user to run this themselves in PowerShell as shown below:

Test-NetConnection -ComputerName "LdnSQLServer001" -Port 1433

Test-NetConnection -ComputerName "LdnSQLServer001" -Port 1433

if the port 1433 is open on the target server LdnSQLServer001, you would get the output shown below. Notice the last line of the output, which indicates that the port is open.

ComputerName     : LdnSQLServer001
RemoteAddress    : 10.36.65.202
RemotePort       : 1433
InterfaceAlias   : Ethernet0
SourceAddress    : 10.37.68.109
TcpTestSucceeded : True

If the port is not open, you would get an error message as shown below. Notice the warnings at the top and the Pingsucceeded and TcpTestSucceeded elements with a status of False.

WARNING: TCP connect to (10.36.65.202 : 1433) failed
WARNING: Ping to 10.36.65.202 failed with status: TimedOut


ComputerName             : LdnSQLServer001
RemoteAddress            : 10.36.65.202
RemotePort               : 1433
InterfaceAlias           : Ethernet0
SourceAddress            : 10.37.68.109
PingSucceeded            : False
PingReplyDetails (RTT)   : 0 ms
TcpTestSucceeded         : False

Now you know how to check the status of the port in the firewall using PowerShell. Imagine a scenario where you are creating a linked server between two SQL Servers and experiencing a connectivity issue. You now want to check whether the port is blocked by the firewall on the target server. You could execute the above PowerShell command from the source SQL Server to check this – but that requires you to login to the source SQL Server using RDP (Remote desktop session). It gets tedious if you have to repeat this check from multiple source SQL Servers.

Running the same PowerShell command from within SQL Server:

This example shows you how to run the same PowerShell command from within SQL Server, by calling xp_cmdshell. For this to work, make sure xp_cmdshell is enabled via sp_configure. Also note that to run xp_cmdshell you would need higher permissions like CONTROL SERVER or sysadmin server role membership. Its also possible to grant execute permissions on xp_cmdshell to non-sysadmin users. Please check xp_cmdshell documentation on the Microsoft SQL Server website.

The below script calls PowerShell.exe using xp_cmdshell and passes the command Test-NetConnection as a parameter to it. It then captures the output into a table variable and displays it. Please replace the server name and port number from the below script to suit your needs:

SET NOCOUNT ON

--Target server name
DECLARE @ServerName sysname = 'LdnSQLServer001'

--Target port to be checked
DECLARE @PortNumber varchar(6) = '1433'

--Powershell command to be executed
DECLARE @CMD nvarchar(256) = 'powershell.exe -Command "Test-NetConnection -ComputerName ' + @ServerName + ' -Port ' + @PortNumber + '"'

--Temporary table variable to capture the PowerShell output
DECLARE @Output table (RID int IDENTITY, OutputRow nvarchar(128))

--Execute the PowerShell script and capture the output into table variable
INSERT INTO @Output (OutputRow) EXEC master..xp_cmdshell @CMD

--Display clean output by filtering out NULLs
SELECT OutputRow
FROM @Output
WHERE OutputRow IS NOT NULL
ORDER BY RID

SET NOCOUNT ON

--Target server name
DECLARE @ServerName sysname = 'LdnSQLServer001'

--Target port to be checked
DECLARE @PortNumber varchar(6) = '1433'

--Powershell command to be executed
DECLARE @CMD nvarchar(256) = 'powershell.exe -Command "Test-NetConnection -ComputerName ' + @ServerName + ' -Port ' + @PortNumber + '"'

--Temporary table variable to capture the PowerShell output
DECLARE @Output table (RID int IDENTITY, OutputRow nvarchar(128))

--Execute the PowerShell script and capture the output into table variable
INSERT INTO @Output (OutputRow) EXEC master..xp_cmdshell @CMD

--Display clean output by filtering out NULLs
SELECT OutputRow
FROM @Output
WHERE OutputRow IS NOT NULL
ORDER BY RID

Once you run the above script, your output would look like below:

OutputRow
----------------------------------------

ComputerName     : LdnSQLServer001
RemoteAddress    : 10.36.65.202
RemotePort       : 1433
InterfaceAlias   : Front LAN 01
SourceAddress    : 10.37.68.109
TcpTestSucceeded : True

Completion time: 2026-05-07T16:56:06.1879437+01:00

Hopefully this will come in handy for you at some point. You could also use the xp_cmdshell technique to invoke PowerShell.exe for completing administrative tasks. If you have any interesting ideas to use PowerShell this way, please share them by leaving a comment below!