How to Access Database Server on Desktop from Laptop – Networking Guide

networkingroutersql serverwindows 7wireless-networking

I have a desktop PC, a laptop and a Netgear Wireless Router.

The Netgear Wireless Router has a secured Wireless Network Connection setup already. Its SSID is NETGEAR44.

The desktop PC's name is Srh-PC. Its operating system is Windows 7 Professional Service Pack 1 64-bit. It has Microsoft SQL Server 2008 R2 database server installed. It is connected to NETGEAR44 as wireless for internet.

The laptop's name is Srh-HP. Its operating system is Windows 7 Professional Service Pack 1 64-bit. It has Microsoft SQL Server client tools installed. It is connected to NETGEAR44 as wireless for internet.

On my laptop I want to open SQL Server Management Studio and then connect to database server of my desktop PC. How can I do that? I am not strong in networking so any tutorial pointer will help.

Best Answer

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

Related Question