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