Remote connection to SQL Server 2005

I am unable to connect to a SQL Server 2005 instance running remotely
on a Windows 2003 Server machine behind a router. I can connect to the
instance via SQL Server Management Studio Express when I place the
server machine in the DMZ, but I cannot connect when I forward traffic
on port 1433. Note that I am able to forward Remote Desktop requests to
my server, so I know that port forwarding works in other cases.
Any suggestions? I'd rather not leave my server machine in the DMZ.
Thanks for the help.Only the default (un-named) instance listens on port 1433. If you have a
named instance or are running SQL Express, which is a named instance named
(SQLEXPRESS) you are listening on dynamic ports.
For more ideas, run through my short connectivity tutorial at
http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
<bostonguy@.gmail.com> wrote in message
news:1147702273.174968.231850@.i40g2000cwc.googlegroups.com...
>I am unable to connect to a SQL Server 2005 instance running remotely
> on a Windows 2003 Server machine behind a router. I can connect to the
> instance via SQL Server Management Studio Express when I place the
> server machine in the DMZ, but I cannot connect when I forward traffic
> on port 1433. Note that I am able to forward Remote Desktop requests to
> my server, so I know that port forwarding works in other cases.
> Any suggestions? I'd rather not leave my server machine in the DMZ.
> Thanks for the help.
>|||Is it recommended to use something other than port 1433 when
configuring a static port for a named instance like (SQLEXPRESS)?
Thanks,
John
Rick Byham [MS] wrote:[vbcol=seagreen]
> Only the default (un-named) instance listens on port 1433. If you have a
> named instance or are running SQL Express, which is a named instance named
> (SQLEXPRESS) you are listening on dynamic ports.
> For more ideas, run through my short connectivity tutorial at
> http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> <bostonguy@.gmail.com> wrote in message
> news:1147702273.174968.231850@.i40g2000cwc.googlegroups.com...|||There is no particular reason to use a different port number. Some people
use different numbers in case there is another SQL Server installed on the
system. Some people like to make it harder for viruses or hackers that are
targeting port 1433, but using a different port doesn't confuse them that
much.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"John" <john@.destinytours.com> wrote in message
news:1147905901.881404.59600@.j55g2000cwa.googlegroups.com...
> Is it recommended to use something other than port 1433 when
> configuring a static port for a named instance like (SQLEXPRESS)?
> Thanks,
> John
> Rick Byham [MS] wrote:
>|||Yes, I experienced this first hand when I discovered someone from
Amsterdam trying to hack into my server with repeated login attemps
(trying every few seconds).
Rick Byham [MS] wrote:[vbcol=seagreen]
> There is no particular reason to use a different port number. Some people
> use different numbers in case there is another SQL Server installed on the
> system. Some people like to make it harder for viruses or hackers that are
> targeting port 1433, but using a different port doesn't confuse them that
> much.
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "John" <john@.destinytours.com> wrote in message
> news:1147905901.881404.59600@.j55g2000cwa.googlegroups.com...

Remote connection failed, even with proper configuration

I am unable to connect to a SQL instance on the server from a workstation on the same network.

SQL Express SP1 is installed on the server, and allows mixed authentication. The instance name is <SERVERNAME>\SQLEXPRESS.

Both the Database and Browser services are running.

Local and remote connections are enabled under SAC. I also used sp_configure to make the same changes.

All Protocols for SQLEXPRESS have been Enabled using the Configuration Manager.

The firewalls on both machines have been disabled.

Rebooting the server or restarting services after various changes hasn't affected the outcome.

Local connections made directly on the server are always successful using either authentication mode.

SQL Native Client Driver is installed on the workstation.

The workstation user is trusted. However, neither authentication mode is successful.

The connection string from the workstation uses the named instance above and the SQL Native Client driver.

Other services on the server are working and avaialble to the workstation, such as shared folders.

When I attempt a connection from the workstation I receive the following errors:

Connection failed:

SQLState: '08001'

SQL Server Error: -1

[Microsoft][SQL Native Client]SQL Networtk Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Native Client]Login timeout expired

Connection failed:

SQLState: '08001'

SQL Server Error: -1

[Microsoft][SQL Native Client]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 setting SQL Server does not allow remote connections.

I have tried all of the recommendations I can find here without any luck. What am I missing?

Hi,

can you post full connection string used to connection machines!!?

Are you able to get connected each other servers from SSMS?

Hemantgiri S. Goswami

|||

Try to connect to the Server directly using the appropiate port rather than just using the instance name (to see if the SQL Browser is causing the problem) Use the format Servername,portnumber for this. If this does not work, you might have not enabled TCP/IP for connections.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||THank you for the suggestions. The problem was caused by an unrelated program on the same server. Uninstalling the offending program, and reinstalling SQL Express, did the trick.|||

Would you mind posting what the offending program was? I'm getting the same reaction from SQL Server 2005 Express in my LAN with an application that was running just a couple of weeks ago.

TIA

Mike Baker
Adobe Community Expert