无法通过公网IP访问SQL Server，IPALL静态TCP端口1433修改后自动还原求助

Hey there, let's work through this frustrating port reset issue and get your remote MSSQL connection up and running for pyodbc. I’ve dealt with this exact problem a few times, so here’s a step-by-step breakdown of what to check:

1. Make sure you’re editing the right SQL Server instance #

It’s easy to mix up instances in Configuration Manager—especially if you have both a default instance and named instances. Expand SQL Server Network Configuration and double-check you’re modifying protocols for the instance that’s actually running (look for Protocols for [YourInstanceName]).

2. Always run Configuration Manager as Administrator #

This is a super common gotcha. If you open Configuration Manager without admin rights, any port changes you make won’t save properly. Right-click the Configuration Manager shortcut, select Run as administrator, then go back to edit the TCP/IP settings.

3. Fix TCP/IP settings for all IP addresses, not just IPALL #

Just updating IPALL isn’t always enough—you need to tweak individual IP entries too:

• Double-click TCP/IP under your instance’s protocols to open properties
• Switch to the IP Addresses tab
• For every listed IP (like IP1, IP2, etc.), set TCP Dynamic Ports to empty and TCP Port to 1433 (or your preferred fixed port)
• Confirm IPALL also has TCP Dynamic Ports empty and TCP Port set to 1433
• Click OK, then fully stop and restart the SQL Server service (don’t just "restart"—stop first, wait a few seconds, then start)

4. Verify the port is actually listening #

After restarting the service, open Command Prompt and run:

netstat -ano | findstr :1433

You should see a line with LISTENING and a PID that matches the sqlservr.exe process in Task Manager. If you don’t see this, your port changes didn’t stick—go back to step 2 and try again.

5. Lock down firewall rules (critical for remote access) #

Even if the port is set correctly, firewalls will block remote connections by default:

• Windows Firewall: Create an inbound rule allowing TCP traffic on port 1433. Make sure it applies to private/public networks as needed.
• Cloud Server Security Groups: If this is a cloud VM, add an inbound rule to your security group that allows your client’s public IP to access port 1433 over TCP.

6. Test with SSMS before jumping to pyodbc #

Before writing any Python code, confirm you can connect remotely using SQL Server Management Studio (SSMS):

• Use the server address format: [YourPublicIP],[1433] (e.g., 203.0.113.45,1433)
• Use SQL Server Authentication (if you’ve set up a login) or Windows Auth (if your domain supports it)
  If SSMS connects, your pyodbc string will work—here’s a sample:

import pyodbc
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=203.0.113.45,1433;"
    "DATABASE=YourDatabaseName;"
    "UID=YourUsername;"
    "PWD=YourPassword"
)
conn = pyodbc.connect(conn_str)

7. Check SQL Server error logs if issues persist #

If none of the above works, dig into the SQL Server error logs (in SSMS, go to Management > SQL Server Logs). Look for entries about port binding—they’ll tell you if the server is failing to listen on 1433, which can point to permissions conflicts or other hidden issues.

内容的提问来源于stack exchange，提问作者Dean