Member Avatar for Traps

Two parted question really...

First off, I've been trying to enable my SQL Server 2008 R2 to allow remote access so my friend can view it from his RDBMS (also SQL Server 2008). I've enabled TCP/IP, I've allowed Remote Access/Connection, I've opened TCP and UDP Ports and also added the sqlserver.exe and sqlbrowser.exe as inbound and outbound rule to my firewall and I've started my SQL Browswer server so it's running (all of this is because of tutorials on google on how to enable remote access...). But for the life of me I can't get it to work. I've got the server up and running and it works absolutely fine locally so it's nothing like that...

Secondly, how do I actually test this? Could it be that it actually does work but I'm unable to test it myself and he needs to be the one who tests it? If so, how would he do it. I read somewhere about some connection string but that's doing it with code, right? Once again, I have no trouble logging on/accessing my SQL Server locally regardless of whether or not I'm doing it with code or via a RDBMS.

Hope it wasn't tl/dr; and thanks in advance

  • 2 Contributors
  • 2 Replies
  • 298 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Traps

Dani AI

Generated

Since later confirmed that router port forwarding was the missing piece, the following concise guide clarifies practical tests, server-side checks not yet covered in the thread, and basic hardening steps to use before exposing SQL Server across the internet.

Client-side testing and common connection formats: SQL Server Management Studio or sqlcmd are the usual tools. In SSMS the Server field accepts publicIP,port (explicit TCP) or publicIP\InstanceName (named instance — relies on SQL Browser). Example connection string and command-line test:

ADO.NET/ODBC style:
Server=203.0.113.45,1433;Database=MyDatabase;User Id=remoteuser;Password=StrongPass;

sqlcmd example:
sqlcmd -S 203.0.113.45,1433 -U remoteuser -P StrongPass

Server-side checks often overlooked: confirm the SQL service is actually listening on the expected IP/port (SQL Server error log shows listening endpoints, or inspect with netstat). For named instances prefer assigning a static TCP port (or ensure SQL Browser/UDP 1434 is reachable). Verify authentication mode (Mixed Mode if using SQL logins) and that the remote login exists, is enabled, and has the necessary database permissions. Test from a second machine on the same LAN first to isolate NAT/router forwarding issues before testing across the internet.

Security and operational notes: exposing SQL Server directly is risky — a VPN or SSH tunnel is the safer default. If port forwarding is unavoidable, forward only the single TCP port, restrict inbound firewall/router scope to the client's public IP, use a dedicated least-privilege SQL account with a strong password, enable encrypted connections (TLS), disable/rename the sa account, and monitor logs. As suggested, use dynamic DNS if the public IP is not static.

Recommended Answers

All 2 Replies

Member Avatar for adam_k

Use a different computer on the same network to connect to your SQL server either via code or SQL Management studio or just a DSN and test connection (or supply username/password and try to set a db as default for that DSN).
I've got the feeling that your friend's SQL server is on his network and you are trying to connect over the internet. If this is the case then verify that you can connect from within your own network, setup your router/firewall to forward packets received from internet for port 1433 to be forwarded to your SQL on port 1433 and give your friend your public IP (ISP assigned?).
If your IP is not static you might want to use a service like dynDNS (there are free alternatives, google for them) , so that you won't have to give your friend your public IP everytime it changes.

As a test your friend can try to use his SQL management studio to login to your SQL and if that works then go ahead and setup a linked server.

PS: Opening an SQL server to the internet if you don't know what you are doing can be dangerous for your data

Member Avatar for Traps

Yeah I had forgot to open up/forward ports on my router..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.