[RESOLVED] [sql2008] failed remote connection

[jlbantang]

Hi team, we made the following installation in a new server.

Server Platform: Win2008 Standard R2 32bit
Database: SQL Server Express 2008 R2

SQL Firewall Exception
135 – Transact SQL Debugger TCP
1433 – SQL Traffic TCP
1434 – SQL Browser Traffic UDP
2383 – SQL Analytics Traffic TCP
4022 – SQL Broker Traffic TCP

Instance: SQLEXPRESS

The sql browser and service is running properly.

We use a small application for testing connection with parameters
1. host ip
2. sql instance
3. database
4. login user
5. port

The application return a valid connection when run locally. But when tested remotely it returns an invalid connection.

Even turning off host firewall did not make any difference. Anything missing in the setup?

[si_the_geek]

SQL Server Express does not allow remote connections by default, you need to enable them using one of the items provided on the Start menu.

The name depends on the version etc, but it will be called something like "SQL Server Configuration Manager".

[jlbantang]

hey,

I believed the tools no longer available start from 2008 r2.

http://msdn.microsoft.com/en-us/library/cc707782.aspx

And this did not work either.

http://www.linglom.com/2009/03/28/en...-2008-express/

[si_the_geek]

Can you clarify what you mean by "And this did not work either."? (eg: did it make any difference at all? did you perform all of the steps? ...)

[jlbantang]

After repeating the procedure a couple of times the error turns out to be the same.

An error has occurred while establishing a connection to the server...

And the configuration by far is this.



Here is another walkthrough.

To enable SQL Server Express to accept remote connections, we need to perform the following steps:

    1. Make sure SQL Server is up and the instance you try to connect is running.

    2. Make sure that the SQL Server Browser service is started on the server.

    3. Enable Named Pipes and TCP/IP protocol, make sure that SQL Server port is by default 1433 and then restart the SQL Server service to take effect. By the way, please don't enable VIA protocol.

    4. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server.

    5. Make sure TCP port 1433 and UDP port 1434 are not blocked by Windows firewall. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

    6. Enable Mixed Mode(SQL Server authentication and Windows authentication).

    7. Make sure the login has sufficient rights to open its default database. Maybe its default database was removed or offline.

    8. Make sure your server name and instance name are correct and there is actually such an instance on your target machine, such as <computername>\<instancename>.

    9. Make sure the password you typed is correctly and the username exists on the remote SQL Server.

[jlbantang]

OMG i overlooked client anti-virus that blocks sql server host connection...