Can't connect remotely to SQL Server Express

As SQL Server 2005 Express Edition is installed as a Named Instance by
default (unless you change it during SQL Server Setup), it must be using a
Dynamic Port for TCP protokol now.

In some cases I witnessed people experienced conneting remotely and they
were using Dynamic Ports. When they change it to a Static Port, they
successfully connected.

I've an article about remote connection settings, however it's in Turkish.

You can configure your port settings through SQL Server Configuration
Manager. Simply, to use a Static Port for your SQL Server Instance:
- Open up SQL Server Configuration Manager
- From the left side list, expand "SQL Server 2005 Network Configuration"
node
- Choose your SQL Server Instance
- Go to TCP/IP' s Properties
- In the TCP/IP Properties window, go to IP Addresses tab
- You'll see there is IP All in the bottom of this window and there, you'll
see that there is a port number in the TCP Dynamic Ports property. This
means that your SQL Server Instane using a Dynamic Port.
- To set it as a Static Port, clear that port number in the TCP Dynamic
Ports property (just clear it, don't type "0" or something).
- Then in the TCP Port property (which is just below the TCP Dynamic Ports
property), type a Static Port for your SQL Server Instance. Ensure that this
port is not a standard port or it's being used by another
service\application.
- After configuring your SQL Server Instance' s TCP/IP settings, you should
restart your SQL Server Instance' s Database Engine service so that these
new settings will be applied.

You can check if the port has been opened and being listening by going to
Command Prompt. Then run "Netstat -a" command. This will show you info about
the TCP and UDP ports.

I also suggest you to check your TCP/IP settings.

For example, when you open TCP/IP Properties and then IP Addresses tab,
you'll see IPs listed there. Mostly IP1 is the one we use to connect to our
SQL Server Instances. Ensure that it's Enabled and Activated. Also, ensure
that the correct IP address is written in the IP Address property for this
IP1's settings. This IP Address is the one which is your SQL Server
server's.

--
Ekrem Önsoy




"Roger Withnell" wrote in message

> Thanks for your help, but I don't understand what you mean.
>
> Could you elaborate, please?
>
> Many thanks.
>
> Roger
>
> "Ekrem Önsoy" wrote:
>
>> I suggest you to use a static port instead of a dynamic one.
>>
>> --
>> Ekrem Önsoy
>>
>>
>>
>>
>> "Roger Withnell" wrote in
>> message
>>
>> >I have installed SQL Server Express on a Windows 2003 platform.
>> >
>> > I have set the Windows firewall to allow sqlservr.exe and
>> > sqlbrowser.exe
>> > and
>> > udp port 1434. SQL browser is running.
>> >
>> > I have enable tcp/ip protocol. The SQL Server 2005 Surface Area
>> > Configuration is set to local and remote connections using tcp/ip only.
>> >
>> > The Server Properties are set to Server authentication Mixed mode.
>> >
>> > I have set up two users in mixed mode. I can connect locally through
>> > Management Studio Express with both these users.
>> >
>> > But if I try to connect remotely, I get the error "... under the
>> > default
>> > settings SQL Server does not allow remote connections (provider; named
>> > pipes
>> > provider, error: 40 - could not open a connection to SQL
>> > server)(Microsoft
>> > SQL Server, Error: 53).
>> >
>> > Any help much appreciated.
>>
>>
 >> Stay informed about: Can't connect remotely to SQL Server Express 

I think what is meant is
open sql server configuration manager
in SQL server network configuration
Protocolas for mssqlserver
select TCP/IP
Right click and select properties
in the IP Address tab
you will see IP1, IP2 etc
ensure IP1 is your IPV4 (e.g 192.168.1.1) address not the IPV6




RogerWithnel wrote:

Re: Can't connect remotely to SQL Server Express
08-Aug-08

Thanks for your help, but I don't understand what you mean.

Could you elaborate, please?

Many thanks.

Roger

"Ekrem ??nsoy" wrote:

Previous Posts In This Thread:

On 05 August 2008 19:14
ekre wrote:

Re: Can't connect remotely to SQL Server Express
I suggest you to use a static port instead of a dynamic one.

--
Ekrem ??nsoy




"Roger Withnell" wrote in message


On 06 August 2008 20:03
RogerWithnel wrote:

Can't connect remotely to SQL Server Express
I have installed SQL Server Express on a Windows 2003 platform.

I have set the Windows firewall to allow sqlservr.exe and sqlbrowser.exe and
udp port 1434. SQL browser is running.

I have enable tcp/ip protocol. The SQL Server 2005 Surface Area
Configuration is set to local and remote connections using tcp/ip only.

The Server Properties are set to Server authentication Mixed mode.

I have set up two users in mixed mode. I can connect locally through
Management Studio Express with both these users.

But if I try to connect remotely, I get the error "... under the default
settings SQL Server does not allow remote connections (provider; named pipes
provider, error: 40 - could not open a connection to SQL server)(Microsoft
SQL Server, Error: 53).

Any help much appreciated.

On 08 August 2008 13:53
ekre wrote:

Re: Can't connect remotely to SQL Server Express
As SQL Server 2005 Express Edition is installed as a Named Instance by
default (unless you change it during SQL Server Setup), it must be using a
Dynamic Port for TCP protokol now.

In some cases I witnessed people experienced conneting remotely and they
were using Dynamic Ports. When they change it to a Static Port, they
successfully connected.

I've an article about remote connection settings, however it's in Turkish.

You can configure your port settings through SQL Server Configuration
Manager. Simply, to use a Static Port for your SQL Server Instance:
- Open up SQL Server Configuration Manager
- From the left side list, expand "SQL Server 2005 Network Configuration"
node
- Choose your SQL Server Instance
- Go to TCP/IP' s Properties
- In the TCP/IP Properties window, go to IP Addresses tab
- You'll see there is IP All in the bottom of this window and there, you'll
see that there is a port number in the TCP Dynamic Ports property. This
means that your SQL Server Instane using a Dynamic Port.
- To set it as a Static Port, clear that port number in the TCP Dynamic
Ports property (just clear it, don't type "0" or something).
- Then in the TCP Port property (which is just below the TCP Dynamic Ports
property), type a Static Port for your SQL Server Instance. Ensure that this
port is not a standard port or it's being used by another
service\application.
- After configuring your SQL Server Instance' s TCP/IP settings, you should
restart your SQL Server Instance' s Database Engine service so that these
new settings will be applied.

You can check if the port has been opened and being listening by going to
Command Prompt. Then run "Netstat -a" command. This will show you info about
the TCP and UDP ports.

I also suggest you to check your TCP/IP settings.

For example, when you open TCP/IP Properties and then IP Addresses tab,
you'll see IPs listed there. Mostly IP1 is the one we use to connect to our
SQL Server Instances. Ensure that it's Enabled and Activated. Also, ensure
that the correct IP address is written in the IP Address property for this
IP1's settings. This IP Address is the one which is your SQL Server
server's.

--
Ekrem ??nsoy




"Roger Withnell" wrote in message


On 08 August 2008 14:18
RogerWithnel wrote:

Re: Can't connect remotely to SQL Server Express
Thanks for your help, but I don't understand what you mean.

Could you elaborate, please?

Many thanks.

Roger

"Ekrem ??nsoy" wrote:

EggHeadCafe - Software Developer Portal of Choice
Installing Visual Studio .NET Beta 2: The Oddysey
http://www.eggheadcafe.com/tutorials/aspnet/3ed9b4c7-d35b-4992-a694-2d...69bb89e
 >> Stay informed about: Can't connect remotely to SQL Server Express 

This tutorial may help:
http://msdn.microsoft.com/en-us/library/ms345318(SQL.90).aspx
--
Rick Byham, MSFT
(Implies no warranty or rights)


<Vince Chevens> wrote in message

>I think what is meant is
> open sql server configuration manager
> in SQL server network configuration
> Protocolas for mssqlserver
> select TCP/IP
> Right click and select properties
> in the IP Address tab
> you will see IP1, IP2 etc
> ensure IP1 is your IPV4 (e.g 192.168.1.1) address not the IPV6
>
>
>
>
> RogerWithnel wrote:
>
> Re: Can't connect remotely to SQL Server Express
> 08-Aug-08
>
> Thanks for your help, but I don't understand what you mean.
>
> Could you elaborate, please?
>
> Many thanks.
>
> Roger
>
> "Ekrem ??nsoy" wrote:
>
> Previous Posts In This Thread:
>
> On 05 August 2008 19:14
> ekre wrote:
>
> Re: Can't connect remotely to SQL Server Express
> I suggest you to use a static port instead of a dynamic one.
>
> --
> Ekrem ??nsoy
>
>
>
>
> "Roger Withnell" wrote in
> message
>
>
> On 06 August 2008 20:03
> RogerWithnel wrote:
>
> Can't connect remotely to SQL Server Express
> I have installed SQL Server Express on a Windows 2003 platform.
>
> I have set the Windows firewall to allow sqlservr.exe and sqlbrowser.exe
> and
> udp port 1434. SQL browser is running.
>
> I have enable tcp/ip protocol. The SQL Server 2005 Surface Area
> Configuration is set to local and remote connections using tcp/ip only.
>
> The Server Properties are set to Server authentication Mixed mode.
>
> I have set up two users in mixed mode. I can connect locally through
> Management Studio Express with both these users.
>
> But if I try to connect remotely, I get the error "... under the default
> settings SQL Server does not allow remote connections (provider; named
> pipes
> provider, error: 40 - could not open a connection to SQL server)(Microsoft
> SQL Server, Error: 53).
>
> Any help much appreciated.
>
> On 08 August 2008 13:53
> ekre wrote:
>
> Re: Can't connect remotely to SQL Server Express
> As SQL Server 2005 Express Edition is installed as a Named Instance by
> default (unless you change it during SQL Server Setup), it must be using a
> Dynamic Port for TCP protokol now.
>
> In some cases I witnessed people experienced conneting remotely and they
> were using Dynamic Ports. When they change it to a Static Port, they
> successfully connected.
>
> I've an article about remote connection settings, however it's in Turkish.
>
> You can configure your port settings through SQL Server Configuration
> Manager. Simply, to use a Static Port for your SQL Server Instance:
> - Open up SQL Server Configuration Manager
> - From the left side list, expand "SQL Server 2005 Network Configuration"
> node
> - Choose your SQL Server Instance
> - Go to TCP/IP' s Properties
> - In the TCP/IP Properties window, go to IP Addresses tab
> - You'll see there is IP All in the bottom of this window and there,
> you'll
> see that there is a port number in the TCP Dynamic Ports property. This
> means that your SQL Server Instane using a Dynamic Port.
> - To set it as a Static Port, clear that port number in the TCP Dynamic
> Ports property (just clear it, don't type "0" or something).
> - Then in the TCP Port property (which is just below the TCP Dynamic Ports
> property), type a Static Port for your SQL Server Instance. Ensure that
> this
> port is not a standard port or it's being used by another
> service\application.
> - After configuring your SQL Server Instance' s TCP/IP settings, you
> should
> restart your SQL Server Instance' s Database Engine service so that these
> new settings will be applied.
>
> You can check if the port has been opened and being listening by going to
> Command Prompt. Then run "Netstat -a" command. This will show you info
> about
> the TCP and UDP ports.
>
> I also suggest you to check your TCP/IP settings.
>
> For example, when you open TCP/IP Properties and then IP Addresses tab,
> you'll see IPs listed there. Mostly IP1 is the one we use to connect to
> our
> SQL Server Instances. Ensure that it's Enabled and Activated. Also, ensure
> that the correct IP address is written in the IP Address property for this
> IP1's settings. This IP Address is the one which is your SQL Server
> server's.
>
> --
> Ekrem ??nsoy
>
>
>
>
> "Roger Withnell" wrote in
> message
>
>
> On 08 August 2008 14:18
> RogerWithnel wrote:
>
> Re: Can't connect remotely to SQL Server Express
> Thanks for your help, but I don't understand what you mean.
>
> Could you elaborate, please?
>
> Many thanks.
>
> Roger
>
> "Ekrem ??nsoy" wrote:
>
> EggHeadCafe - Software Developer Portal of Choice
> Installing Visual Studio .NET Beta 2: The Oddysey
> http://www.eggheadcafe.com/tutorials/aspnet/3ed9b4c7-d35b-4992-a694-2d...69bb89e
 >> Stay informed about: Can't connect remotely to SQL Server Express