What does a client need to connect remotely to an SQL Server?

2

i have set up a Microsoft SQL Server 2008 R2 Express on my home computer. My home computer has Windows 7 and is behind a router. I configured it i think ok, because i can connect to him from work through management studio. Means - i opened all necessary ports, i configured SQL to accept connections on TCP. I have also on my router set up DynDNS and i connect through my DNS name and it works.

I use also Microsoft PortQueryUI [ http://www.microsoft.com/download/en/details.aspx?id=24009 ] to test if the server is visible and from my work computer and it is visible. My PortQueryUI output at work is ok (attached bloew) but people from other networks cannot connect to my server.They have in the test to port 1434 UDP (big bold) an error code 0x00000002 where i have it correct - 0x00000000.

What is the reason, is there something that needs to be done on their computers? Maybe their network is blocking something?

My PortQueryUI output from work:

    =============================================

 Starting portqry.exe -n myhome.dyndns.net -e 1434 -p UDP ...


Querying target system called:

 myhome.dyndns.net

Attempting to resolve name to IP address...


Name resolved to 83.xxx.xxx.xxx

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName ROBERT-KOMPUTER
InstanceName SQL2008R2
IsClustered No
Version 10.50.1600.1
tcp 1433
np \\ROBERT-KOMPUTER\pipe\MSSQL$SQL2008R2\sql\query



==== End of SQL Server query response ====

UDP port 1434 is LISTENING
portqry.exe -n myhome.dyndns.net -e 1434 -p UDP exits with return code 0x00000000.
=============================================

 Starting portqry.exe -n myhome.dyndns.net -e 1433 -p TCP ...


Querying target system called:

 myhome.dyndns.net

Attempting to resolve name to IP address...

Name resolved to 83.xxx.xxx.xxx

querying...

TCP port 1433 (ms-sql-s service): LISTENING
portqry.exe -n myhome.dyndns.net -e 1433 -p TCP exits with return code 0x00000000.
sql-server
udp
asked on Stack Overflow Feb 16, 2012 by Robert Niestroj • edited Dec 22, 2020 by Robert Niestroj

3 Answers

1

Make sure port 1433 is open on your firewall where SQL is, you should probably put some sort of IP range/scope on that too for security.

You need to set your database to Accept Remote Connections in the DB properties. You need to setup a username and password and give it access to the database so your remote connection recognizes it.

Here's an example of allowing the connection:

http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Here's an example of connecting:

http://www.youtube.com/watch?v=hT9p_FPt720

answered on Stack Overflow Feb 16, 2012 by Control Freak
0

I had the same problem but I had already setup open ports on 1433 on the firewall. The solution for me was simple on SQL 2012:

  1. Goto "Sql Server Connection Manager"
  2. Drop down SQL server network configuration
  3. Protocols for MSSQLSERVER > Right click TCIP/IP
  4. on every "TCP Port" entered 1433 or whatever port you wish
  5. done
answered on Stack Overflow Oct 20, 2015 by arogers
0

I have a non-default named instance. So I`ve had to enable and start the SQL Server Browser service on services.msc

Make sure to check Microsoft's troubleshooting below:

https://docs.microsoft.com/en-us/archive/blogs/sql_protocols/steps-to-troubleshoot-sql-connectivity-issues

answered on Stack Overflow Dec 22, 2020 by MZanetti

User contributions licensed under CC BY-SA 3.0