How to access database server on my desktop from my laptop?

Per some notes I have saved from a while back on a server with applicable specs, I figured why note throw in an answer and make into some helpful steps to simplify this task.

Run All Commands on the SQL Server instance OS

Important: Be sure to run all elevated as administrator from command prompt.

Find/Confirm Listening TCP Port for the SQL Server Instance

USE [master]
EXEC xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc'
--EXEC xp_readerrorlog 0, 1, N'Server is listening on'

Note: Change <####> in below commands to the confirmed port number


From Elevated Command Prompt

Allow inbound TCP connections to the confirmed SQL port

netsh advfirewall firewall add rule name="Name of rule/description inbound/outbound on TCP <####>" dir=in action=allow protocol=TCP localport=#### profile=domain

Allow Remote Access via SSMS Database Connection

Manually test from all subnets in case you need to adjust the network scope of the rules. Ensure the C:\Program Files\~ path is correct for your environment as well as the profiles and the TCP port number.

netsh firewall add allowedprogram program="C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" name="Microsoft SQL Server 2008 R2 Database Engine Access" profile=domain 

netsh advfirewall firewall add rule name="Allow SSMS Database Engine connections inbound on TCP <####>" dir=in action=allow protocol=TCP localport=<####> profile=domain

Further Resources

  • Configure a Windows Firewall for Database Engine Access
  • Understanding Firewall Profiles