Sql Server 2008 remote access problem

sql-server-2008 remote-access
Vladimir picture Vladimir · Jun 28, 2011 · Viewed 12.8k times · Source

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.

Answer

Vladimir picture Vladimir · Jul 5, 2011

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.

Related questions

Completely disable remote access to SQL Server

I'm on the panic-mode. Today, when navigating to my website, I saw the website was down because of SQL Server timeouts (after an attack with million of login attempts.) ASP.Net pages can't be processed, because SQL Server requests just …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More