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.

回答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

回答2:

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: