🔌 SQL Server Remote Connection Troubleshooting — Part 1: TCP, Listening Ports & the Shared Memory Trap
The scenario: SQL Server on 10.10.1.3 answers instantly from SSMS — but the IIS app server on 10.10.1.4 can't reach it. Windows Firewall is off on both machines. netstat shows SQL happily listening on 0.0.0.0:1433. So what's left?
In Part 1, we walk through 15 battle-tested commands — with real outputs, live interpretation, and the single most-overlooked trap in SQL Server troubleshooting.
🎬 The Real-World Scenario
Let's start with the raw facts. This is a genuine production incident — the kind every DBA or sysadmin eventually faces.
Network Topology — Private Subnet 10.10.1.0/24
- 10.10.1.3 — SQL Server 2016+ (Named Instance:
PIE), SQL Authentication enabled - 10.10.1.4 — IIS Web Server hosting a .NET application (needs to read/write to the DB)
- Local SSMS on 10.10.1.3: ✅ Connects instantly
- SSMS from 10.10.1.4: ❌ Times out, error 258 / 53 / 10060 depending on client
- Windows Firewall: disabled on both machines (test only — don't do this in production!)
- netstat on 10.10.1.3:
TCP 0.0.0.0:1433 LISTENING 8592— SQL is definitely listening
Port 1433 wasn't chosen randomly. In the late 1980s, Microsoft's networking team picked 0x599 (1433 decimal) as the SQL Server TCP anchor port — and it's been the default ever since, even through Windows NT, Windows 2000, and every version up to SQL Server 2022. If you ever see 1434, that's the SQL Browser Service listening via UDP to help clients find named instances.
Why this case matters: With the firewall off, a lot of engineers conclude "the firewall isn't the problem — so SQL must be at fault." But there are five more layers that can silently block a port: Hyper-V Virtual Switch, VLAN ACL, cloud Security Group, subnet mask mismatch, and — most sneakily — the Shared Memory protocol masking the entire test. Part 1 eliminates them one by one.
💡 Why Local SSMS Works but Remote Doesn't
This is the single most important concept in Part 1. Get it, and 80% of remote connection issues become obvious. Miss it, and you'll chase ghost problems for hours.
When you launch SSMS on the database server itself and connect to localhost, ., or (local), the SQL Client library tries protocols in a specific order — always fastest first:
So when your local SSMS succeeds instantly, there's a very good chance it never touched TCP. It used Shared Memory — a private channel inside the OS kernel that doesn't use port 1433 at all. Which means: your successful test proved nothing about the network.
The #1 trap in SQL Server troubleshooting: "SSMS on the DB server connected fine, so TCP must be working." It's the exact opposite. Local SSMS success proves only that SQL Authentication and the instance are alive — it says nothing about whether TCP/1433 works over the network. The only way to test TCP is with an explicit IP,Port connection (Command 07) or a Test-NetConnection from a remote host (Command 01).
🌳 The Diagnostic Decision Tree
Follow this tree in order. Change one thing at a time. The moment you change two variables, you lose the ability to know which one fixed the problem.
10.10.1.3,1433
⚡ 15 Diagnostic Commands — Your Hands-On Lab
Every command below runs in PowerShell 5.1+ or Command Prompt. For each one you get: where to run it, why it matters, a real sample output, and how to interpret the result. Hit the copy button on any code block to grab it instantly.
Golden rule: Run these in order. Change one variable at a time. If you flip three switches at once and it suddenly works, you'll never know which one actually fixed it — and you'll hit the same problem again next month.
🔍 Interpreting the Output — What Good vs. Bad Looks Like
A command is only as useful as your ability to read its output. Here are the two most important patterns to memorise.
netstat -ano | findstr LISTENING | findstr :1433
This means SQL Server (PID 8592, i.e. sqlservr.exe) is listening on 0.0.0.0 — every IPv4 interface. That's exactly what you want for remote access.
This means SQL Server is listening only on the loopback interface. Remote connections can never succeed. Fix: open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for PIE → TCP/IP → IP Addresses → IPAll → clear "TCP Dynamic Ports" and set "TCP Port" to 1433. Then restart the SQL Server service.
CONNECTIONPROPERTY('net_transport') — the sneaky one
If you connected via localhost and get "Shared memory", you have not yet proven that TCP works. The next step is to open SSMS and connect with an explicit 10.10.1.3,1433. Only then does this query return the truth.
The CONNECTIONPROPERTY function is one of SQL Server's best-kept diagnostic secrets. It was introduced in SQL Server 2008 and can return more than 20 different properties about the current connection — from local_net_address and client_net_address to auth_scheme, net_transport, and protocol_type. A DBA who knows these properties can diagnose in seconds what others spend hours on.
Test-NetConnection -InformationLevel Detailed
The critical clue here is the combination: PingSucceeded : True but TcpTestSucceeded : False. That tells you the packet reaches the machine (ICMP works), but the TCP 1433 handshake never completes. Something between the two hosts is filtering TCP — firewall, VLAN ACL, or a security appliance.
⚠️ 5 Common Pitfalls (and How to Avoid Them)
- "Local SSMS worked, so TCP must be fine." Local SSMS almost always falls back to Shared Memory or Named Pipes. It proves nothing about TCP. Always use an explicit
IP,Portor a remoteTest-NetConnection. - "Ping succeeded, so the port is open." ICMP and TCP are completely separate protocols. A firewall can allow ping while blocking 1433 — or the reverse. Always test the port, not just the host.
- "Skip netstat — just open the firewall." Verify SQL is actually listening first. Otherwise you'll waste an hour opening ports that were never the problem.
- "Firewall is off, so nothing can be blocking." Windows Firewall is only one of many layers. Hyper-V Virtual Switch, VLAN ACLs, cloud Security Groups, and even some antivirus "Network Shield" features can silently filter TCP.
- "Named instance + dynamic port — it'll just work." Named instances default to dynamic ports. Best practice: pin the instance to a static TCP port (e.g. 1433 or 51433). This removes SQL Browser from the equation entirely.
Pro tip: Write these five pitfalls on a sticky note and stick it to your monitor. Every SQL connectivity incident you'll ever debug will map to one of them.
❓ Frequently Asked Questions
The questions we hear most often from engineers facing this exact scenario.
🎓 More Free Resources from FreeLearning365
Over 100 free resources for developers, DBAs, sysadmins, and students — no registration, no paywall, no nonsense.
Job Interview Preparation — Programming, Cloud, Data, ERP
Full interview prep for SQL, DBMS, Networking, Cloud, and Programming — used by thousands of engineers worldwide.
Start Prep →Free Programming Tutorials
JavaScript, Angular, Python, SQL, Data Analysis & More — complete free tutorials and structured learning paths.
Learn Now →100+ Free Online Tools & Utilities
For developers, SEO specialists, and students — 100+ free tools with zero registration required.
Explore Tools →Class 1-12 Notes, Lecture Sheets & Suggestions PDF
SSC, HSC, Dakhil, Fazil — all subjects, all classes, in one place. Completely free.
View Notes →Free Question Bank — BCS, HSC, SSC, JSC, PSC
Question sets and solutions for dozens of competitive exams, all free.
Browse Bank →Advance Your IT Career with Professional Training
Specialised IT training courses for engineers wanting to level up — cloud, data, and software architecture.
See Training →🏁 Part 1 Wrap-Up — What You Now Know
In Part 1 we established six truths that will save you hours on every future SQL connectivity incident:
- ✅ Local SSMS success ≠ TCP works — Shared Memory and Named Pipes silently mask the network layer
- ✅
netstat -ano | findstr :1433is your first proof that SQL is truly listening on all interfaces - ✅
ipconfigconfirms subnet mask and gateway — the fastest way to catch a misconfigured network - ✅
CONNECTIONPROPERTY('net_transport')tells you exactly which protocol your current session uses - ✅
Test-NetConnection -InformationLevel Detailedseparates ICMP problems from TCP problems - ✅ SSMS with explicit
IP,Portis the only local test that actually exercises TCP
Up next — Part 2: We'll walk through the four layers that can block TCP even when Windows Firewall is off — Windows Firewall rules, Hyper-V Virtual Switch, VLAN ACLs, and Cloud Security Groups — with the exact PowerShell commands to create a controlled test rule and prove which layer is the culprit.
0 Comments
thanks for your comments!