Sql Server 2008 remote access problem

9
votes

I have problem with connecting to Sql Server from my local machine.

Seems like I did everything I could, but still I'm unable to connect to it.

This is what I did:

1) Right click on Sql Server in Sql Management Studio (SMS) -> Connections -> Allow remote connections

2) Right click on Sql Server in Sql Management Studio (SMS) -> Security -> Allow remote connections -> Set Auth mode to Win and Sql auth mode (i.e. mixed mode)

3) Disabled firewall

4) Sql Server Configuration Manager -> Network Configuration -> Protocols -> Enable Shared memory, Named pipes, TCP/IP

5) Sql Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP -> Properties -> Set TCP Port to 1433, Set TCP Dynamic Port to 0. After that I restarted MainSql service in "services.msc"

6) I ran "EXEC xp_readerrorlog 1" in SMS, found this:

Server is listening on [ 'any' 1433]. Server is listening on [ 'any' 1433]. Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\MAINSQL ]. Server named pipe provider is ready to accept connection on [ \.\pipe\MSSQL$MAINSQL\sql\query ]. Server is listening on [ ::1 64825]. Server is listening on [ 127.0.0.1 64825].

7) Tried to telnet [myip] 1433 from my home machine. Result: connecting To [myip]...Could not open connection to the host, on port 1433 : Connect failed

8) Tried to telnet [myip] 1433 from server - it worked!

Seems like this is some kind of a network issue. I have another server with Sql Server installed, I did the same on it and I was able to connect to it from my home machine, but not this one! I tried to connect to this server from the second one and it didn't work too.

Please help me, I don't know what to do. I did everything I could but it still doesn't work.

Update: Sql Server Browser service is started on the server machine. The server is a dedicated server at a hosting, I'm trying to connect to it from my home machine and from another server witch is a dedicated server as well, from another country.

2
sql-server-2008remote-access
Is your server and home machine on the same subnet? if not, are there any routing/firewall issues between them? - cairnz
No, server is not and the same subnet as home machine. Well, I'm not sure about how to troubleshout such issues. Server has IIS and I can interact with the web-sites on it - Vladimir
How do you connect to the Server, what subnet is it on, and what subnet are you on ? Do you connect through VPN? (I think you might be connecting to the wrong addresses, based on what's available for use for you) - cairnz
I use RDP to connect to the server. And there is Sql Management Studio installed on it. I use it to configure the server and its databases. I don't use VPN to connect the server, all communications are though internet. BTW, should I? What I want to do - I have a dedicated server from one hosting company and I have another dedicated server from another hosting company. They are not related to each other. I want the second server to connect the first one's SQL DB - Vladimir
What i'm saying is that since this is on different hosting centers, there's probably firewall rules applying here, and especially blocking external access to SQL Server port. If you can VPN to the hosting center where you want to connect to the SQL DB - then try to connect, and this works, the problem is the firewall between. - cairnz

2 Answers

10
votes

The problem was in firewall. To fix it I ran a script from here: http://support.microsoft.com/kb/968872

This is really strange because I fully disabled server's firewall. However it now works for me, thanks to cairnz and Gregory A Beamer for help.

2
votes

It looks like you hit one of two most likely causes: You have set protocols. Just double check the client side to ensure the same protocols are enabled on both sides and you are done there.

The second most likely, with SQL 2005 on, is the Browser service is turned off on the server. If this is true, you will often have issues with remote calls, ala http://support.microsoft.com/kb/914277.