Cannot make remote connection to SQL Server Express

I'm having trouble making a remote connection to a SQL Server Express
database. The service is started, and the connection works locally. I
tested it using a .udl. In this local .udl, I use the SQL Native
Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
with username/password log on, and initial catalog.
Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
\SQLEXPRESS. The latter entry is a mock-up of the web server's name,
which is 15 characters long, the last 3 being "WEB". The 'B' is
truncated from the server name.
All these connections work locally on the web server (where SQL
Express is running).
The network administrator has opened TCP port 1433 on the web server.
The service has been configured to allow remote connections on TCP/IP
and Named Pipes.
>From the remote machine, the same .udl is set up, but with a Data
Source modified to use an IP address. The Data Source entry then looks
like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
"Test connection failed because of an error initializing provider.
Login timeout expired."
The network administrator reports attempts to communicate with
152.21.110.58 on port 1433. These attempts are being allowed.
I suspect the data source is the problem. Some documentation indicates
port 1433 must be explicitly stated when connecting remotely to SQL
Server 2005 and its variants (including Express). However, I can't
find any examples of explicit port designation in a .udl.
The alternative to explicit port connection (according to some
sources) is to open port 1434 for SQL Browser. That port was opened
and SQL Browser service started, but the connection failed in the same
fashion.
The application that needs to connect is written in C#/.NET.
Can the .udl be configured to test the remote connection? The
initiator of the connection is an Oracle box, so SQLCMD is not an
option for testing remote connections.
Is there something amiss with the Data Source designation? Everything
I read points to replacing server specs like '.' and '(local)' with IP
designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
Is this correct? The SQL Server is a web server in a DMZ, so DNS
translation is not available.
How do I monitor SQL Express for login attempts, both local and
remote?
Thanks,
DanHi Dan
"dan@.wagers.net" wrote:
> I'm having trouble making a remote connection to a SQL Server Express
> database. The service is started, and the connection works locally. I
> tested it using a .udl. In this local .udl, I use the SQL Native
> Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
> with username/password log on, and initial catalog.
> Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
> \SQLEXPRESS. The latter entry is a mock-up of the web server's name,
> which is 15 characters long, the last 3 being "WEB". The 'B' is
> truncated from the server name.
> All these connections work locally on the web server (where SQL
> Express is running).
> The network administrator has opened TCP port 1433 on the web server.
> The service has been configured to allow remote connections on TCP/IP
> and Named Pipes.
> >From the remote machine, the same .udl is set up, but with a Data
> Source modified to use an IP address. The Data Source entry then looks
> like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
> "Test connection failed because of an error initializing provider.
> Login timeout expired."
> The network administrator reports attempts to communicate with
> 152.21.110.58 on port 1433. These attempts are being allowed.
> I suspect the data source is the problem. Some documentation indicates
> port 1433 must be explicitly stated when connecting remotely to SQL
> Server 2005 and its variants (including Express). However, I can't
> find any examples of explicit port designation in a .udl.
> The alternative to explicit port connection (according to some
> sources) is to open port 1434 for SQL Browser. That port was opened
> and SQL Browser service started, but the connection failed in the same
> fashion.
> The application that needs to connect is written in C#/.NET.
> Can the .udl be configured to test the remote connection? The
> initiator of the connection is an Oracle box, so SQLCMD is not an
> option for testing remote connections.
> Is there something amiss with the Data Source designation? Everything
> I read points to replacing server specs like '.' and '(local)' with IP
> designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
> Is this correct? The SQL Server is a web server in a DMZ, so DNS
> translation is not available.
> How do I monitor SQL Express for login attempts, both local and
> remote?
> Thanks,
> Dan
>
By default SQLExpress does not have network protocols enabled by default see
http://msdn2.microsoft.com/en-us/library/ms190198.aspx ,
http://msdn2.microsoft.com/en-us/library/ms143446.aspx
and
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#disablenetworkprotocols
Using the command line installation option of DISABLENETWORKPROTOCOLS with
values of 0 or 2 will enable Shared Memory and TCP/IP (0 also enables named
pipes)
If this has not be done you can still enable remote connections after you
have installed SQLExpress by using the Surface Area Configuration Tool
(SqlSAC.exe)
to enable remote connections see
http://msdn2.microsoft.com/en-us/library/ms173748.aspx
John