SQL Server 2005 rejects remote internet connections

PROBLEM:

SQL 2005 rejects connection from an ASP.net test webpage (shown below).

CONFIGURATION:

Internet > Concast Cable > Router > Sever01 Nic1 > Server01 SBS 2003 & SQL 2005 > Sever01 Nic2 > Switch > Local Area

Linksys Router WRV54G( Firmware Version: 2.39.2): Port Range Forwarding TCP 1433 and UDP 1434

Small Business Server 2003 Premium (Server Name: Server01)

Two network cards: Internet Ionnection and Local Area Network

Ran: Configure E-mail And Internet Connection Wizzard

Ran: Remote Access Wizzard (disables access to Windows Firewall)

Note: Remote workplace, Outlook Web Access, Sharepoint, etc, on ports 443,444, 3389, 4125 All work remotely - so Linksys router and RRAS port settings are working.

Routing And Remote Access(RRAS) >NAT/Basic Firewall > Internet Ionnection > Services And Ports

Added TCP Incoming Port 1433, Private Address 127.0.0.1, Outgoing Port 1433

Added UDP Incoming Port 1434, Private Address 127.0.0.1, Outgoing Port 1434

SQL Server 2005 Standard Edition version 9.0.3042 (installed on same machine, default instance ID MSSQLSERVER)

By default: TCP protocol is enabled and local remote connections are enabled on standard Edition. Also by default: MSSQLSERVER automatic/started/running and SQL Server Browser automatic/started/running

Ran: SQL Server 2005 Surface Area Configuration to confirm MSSQLSERVER instance settings are running.

Using: SQL Server Authentication: Login and Password

"sa" is a default account that works locally.

SQL Server Configuration Manager:

MSSQLSERVER protocols: Share Memory and TCP/IP enabled

TCP port 1433 by default (confirmed listening by ERRORLOG)

Microsoft SQL Server Management Studio

Database Engine lists SERVER01 (instead of SERVER01\MSSQLSERVER - the default instance is not named explicitly)

Created database "Test" under SERVER01 > Databases.

Created table "tblCategories" in "Test" (included some fields and data)

Server-Side Successful

netstat -ano | findstr 1433
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 2004

Client-Side

Ping Successful

ping <xxx.xxx.xxx.xxx>

Telnet Successful (blank screen)

telnet <xxx.xxx.xxx.xxx> <port#>

TEST ASP.net Web Page Button Sub

I have this button on a page at Godaddy.com connecting to an instance of MS SQL provided by Godaddy and there is no problem connecting to that SQL Server.

Public Sub TestSQL_Click(Sender As Object, e As EventArgs)

Dim rdr As SqlDataReader = Nothing

Dim conn As SqlConnection = new SqlConnection("Data Source=tcp:xxx.xxx.xxx.xxx; Initial Catalog=Server01\tblCategories; User ID=sa; Password=xxx")

conn.Open()

Dim cmd As SqlCommand = new SqlCommand("SELECT Category FROM tblCategories;", conn)

rdr = cmd.ExecuteReader()

rdr.Close()
conn.Close()

End Sub

ERROR:

The follworing error occurs or variations of it depending on different connection string configurations:

Occurs at: conn.Open()

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

THOUGHTS

Possibly a bad connection string or Microsoft security issue.

I've attempted many other connection string formats such as:

Dim conn As SqlConnection = new SqlConnection("Data Source=xxx.xxx.xxx.xxx,1433;

Network Library=DBMSSOCN; Initial Catalog=Server01\Test; User ID=sa; Password=xxx")

But I continue to receive connection failure messages (or invalid connection string errors for incorrectly formed connection strings).

I've tried disabling RRAS and Windows Firewall and the same errors resulted.

Any feedback would be appreciated.

Hi Emanuel,

Running the Server 2005 Surface Area Configuration tool shows "Local and remote connections" and "Using TCP/IP only" are selected for MSSQLSERVER (the default instance).

Running sp_configure (without parameters) against MSSQLSERVER (default instance) returns:

Name Min Max config_value run_value

allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 0 32767 0 0

I used Windows Update to update the SBS server including SQL:

SBS 2003 Service Pack 2

SQL 2005 Service Pack 2

MSSQLSERVER file version 2005.090.3054.00

I also set Sharepoint to use Dynamic port assigment, because Sharepoint was also listening on port 1433 by default. I did not expect this resolve my issue (and it didn't), but I simply wanted to take Sharepoint out of the equation.

ADDITIONAL INFORMATION

I've completed more testing and the following is a better description of the issue:

I have a Godaddy.com(ISP) ASP.net 2.0 website that connects to a Godaddy SQL 2005 instance with no problem.

I also have Gravityfree(ISP) ASP.net 2.0 website that connects to the GravityFree SQL 2005 instance with no problem.

I want to host the website at an ISP like Godaddy and I want the website to connect to a SQL Server at my office (FYI -The website will have high traffic and high uptime. The SQL server will house a selection of very large downloadable content with a fraction of the traffic where uptime is not as critical. Godaddy limits shared hosting accounts to 200MB and the requirement is at least 15GB - one option is a dedicated SQL server at Godaddy)

I have tested connecting from a Godaddy ASP.net webpage to the Gravityfree SQL intance using a working connection string and I get the same connection failure. (This connection uses a named instance, therefore specifying a correct port is unnecessary)

I have also tested connecting from a Gravityfree ASP.net webpage to the Godaddy SQL intance using a working connection string and I get the same connection failure. (Again this connection uses a named instance, therefore specifying a correct port is unnecessary)

Attempting to connect from either the Godaddy or the GravityFree website to the SQL Server(MSSQLSERVER) at my office results in the same type of connection failure.

From all this, I see that if I host a website application and SQL Server together on the same network (my own or an ISP) then I will be able to create working SQL connections. But instead, I really want to connect my web app to a SQL instance on another network.

During the setup of SQL Server, I remember there is a prompt for a Domain. Is access to an instance of SQL Server limited to that Domain specified in that setting

By now, I've reviewed several hundred posts on SQL 2005 connectivity and the great majority of these deal with connectivity issues related to the development environment involving firewall, ports, network, and SQL configuration settings on the same network. I've only found a couple posts that implied (not specifically addressed) the cross-network question I am attempting to resolve, and those posts were treated the same way and remain unresloved.

So the question is how to connect a website application to a SQL Instance on a diferent domain/network

Because I believe this is a distinct question, I've opted to start this thread rather than posting to an existing one.

Thank you for your response,

I had a friend from another city telnet successfully to the default 1433 port.

Next I stopped the MSSQLSERVER instance and had my friend telnet again, and as expected the telnet connection failed.

Finally, I restarted the MSSQLSERVER instance and my friend was again able to telnet successfull.

While I was reviewing the MSSQLSERVER instance logs, I noticed thousands of failed login entries from a hacker using a brute force attack with userIDs "administrator" and "mssql" However my failed attempts from the ASP.net pages do not show up in these logs.

From this I gather that the MSSQLSERVER instance is reachable from the outside.

I would come to the same conclusion based on the telnet and hacker results. Connectionstrings.com is excellent website that has a section on SqlConnection (.NET), that I've been using.

I mentioned two connection strings above as an example, I have already tried at least 100 variations of the connection string. Here are several that I have retried just now, and failed.

Dim conn As SqlConnection = new SqlConnection("Data Source=xxx.xxx.xxx.xxx; Initial Catalog=Test; User ID=xxx; Password=xxx")

Dim conn As SqlConnection = new SqlConnection("Data Source=xxx.xxx.xxx.xxx\SERVER01; Initial Catalog=Test; User ID=xxx; Password=xxx")

Dim conn As SqlConnection = new SqlConnection("Data Source=tcp:xxx.xxx.xxx.xxx; Initial Catalog=Test; User ID=xxx; Password=xxx")

Dim conn As SqlConnection = new SqlConnection("Data Source=tcp:xxx.xxx.xxx.xxx,1433; Initial Catalog=Test; User ID=xxx; Password=xxx")

Dim conn As SqlConnection = new SqlConnection("Data Source=xxx.xxx.xxx.xxx,1433; Network Library=DBMSSOCN; Initial Catalog=Test; User ID=xxx; Password=xxx;")

Dim conn As SqlConnection = new SqlConnection("Data Source=xxx.xxx.xxx.xxx; Network Library=DBMSSOCN; Initial Catalog=Test; User ID=xxx; Password=xxx;")

The more I explore this, the more I believe that there is an issue relating to sql connections across different domains/networks. I can write ASP.net pages that successfully connect to a MS SQL database as long as the web app and SQL server are on the same network and/or ISP. What I am not able to do is write an ASP.net page at one ISP that connects to a SQL instance on another ISP and vice versa. Likewise I've been unsuccessful in writing an ASP.net page at any of the ISPs, that can connect to a the SQL 2005 datasource at my office. Later I will use the "Microsoft SQL Server Management Studio" tool to test a remote connection from a differrent network, and I expect this type of connection to work. (the hacker, I suspect is using some form of a client application and not an ASP.net page.)

Another way of saying this is that when I create an ASP.net page (at Godaddy) that successfully connects to a data source (at Godaddy), and then I move this ASP.net page to Gravityfree, it is still pointing to the same data source but now the connection fails. I believe that I should be move this ASP.net page between ISPs and the data source connection should remain intact, but does not seem to be the case.

I suspect that most developers develop on one network and deploy the entire project on another network, and therefore, never run into the issue I am describing.

Thank you for the response John.

I introduced the Godaddy example to emphasize the across domain/network SQL connection aspect of my question. I agree with you that Godaddy probably has put into place a configuration that limits connections to their SQL servers from their web hosting environment.

I want to host my own SQL server, and I want to connect to it from my website at Godaddy. I am under the impression that having installed SQL 2005 Standard Edition (SQL Authentication) on my Small Busines Server 2003, that I can connect to it from an ASP.net page at the Godaddy website using a TCP/IP connection string. But I'm unable to connect successfully, even after enbling remote access.

A strange observation is that the SQL log shows no record of a connection attempt from the ASP.net page. The connection appears to be failing before it reaches the SQL server. I've openned ports in the Linksys router and the NAT/Firewall the same way I've openned them for Sharepoint and Remote Workplace.

So some of the big picture questions running through my mind are:

Is there something that needs to be configured to expose the SQL server to ASP.net pages, other than enabling remote access

Does IIS need to somehow be involved

Is there something in Small Business Server 2003 that could be interferring with ASP.net page requests to the SQL server

Do I need to run my machine as a web server to make this work

Any help with this configuration would be greatly appreciated.

I have more information:

I'm traveling out of the country right now, and so I've decided to run the ping and telnet tests again. I found that I'm able to telnet into port 1433 (telnet xxx.xxx.xxx.xxx 1433) successfully, but pinging the WAN IP to the Linksys router at my office network fails. Everything else works, such as remote desktop, remote workplace, and the linksys http administration console.

I'm also able to remote desktop into my server at the office and successfully ping the WAN IP from the server which is probably a missleading result.

I now suspect that my SQL connectivity issue may be due to the fact that WAN IP specified in the connection string cannot be pinged. But I'm not sure because everything else does connect through the various assigned ports.

If anyone has experienced this, please let me know. I don't know if the SQL connection requires a successful ping to the ip address in the connection string (of the ASP.net test page) when the port is accessible. In other words, I have access to everything else without being able to ping the WAN IP, why not SQL

I've checked two of my other networks, whch are also behind linksys routers and all three cannot be pinged. Turns out that Linksys routers have a "Block Anonymous Internet Requests" setting under security, which blocks pings. Disabling this setting allows me to ping my networks remotely, but it does not improve my SQL connectivity issue.

I don't believe that the "Block Anonymous Internet Requests" setting would affect SQL remote connectivity where the ip address and port are specified. If anyone knows otherwise, please let me know.

In further testing, I've setup the client tools on a remote computer (different domain/network) and connecting to my MS SQL 2005 server using "Microsoft SQL Server Management Studio" is straight forward and successful. However, connecting with the ASP.net test page continues to fail. I'm beginning to think that the web app hosting company (Godaddy) is blocking non-trusted connections such as the one I'm trying to make using SQL Authentication.

Below is some information to help anyone trying to make a standard remote connection, and some related issues.

For a default instance of SQL Server Std Ed, you can connect with or without specifying the port number (default 1433)

Server Name: xxx.xxx.xxx.xxx or Server Name: xxx.xxx.xxx.xxx,port

For a named instance, specifying the instance name:

Server Name: xxx.xxx.xxx.xxx\InstnaceName

You don't have to open the firewall on the client computer to make a remote connection.

You don't have to open router ports on the client network to make the connection.

If connecting by specifying IP address and port number, the SQL Server Browser service can be turned off (best practice is to keep the SQL Server Browser service off and to use a port other than 1433). The SQL Server Browser service is required when attempting to connect by specifying a named instance. The SQL Server Browser service requires/uses port UDP 1434 (default) on the SQL server network. You can connect to a named instance by specifyings it's port number instead.

If your SQL server becomes compromised, your entire network is a risk. In other words, if you maintain tight security on your network, but the security on your SQL server is not at least as robust, the SQL server will become the door to the rest of your network (running SQL server in itself is an added risk).

I just spoke to the web hosting company (Godaddy) and found out that Godaddy explicitly prohibits ASP.net connections to external SQL databases from their shared hosting plans. Godaddy offers Virtual Dedicate and Dedicate Server hosting plans that do allow this type of database connection. This is probably the case for most web hosting companies.

So the options are a Virtual Dedicate Server or Dedicate Server at some hosting company, or to host the web app and SQL server myself. There is also a possibility of finding a web hosting company that will allow you to make an external sql connection from their shared hosting environment.

I hope this post helps others.