RE: [OT] Public SQL Server [answer found]

[GregAtGregLowDotCom]

All good Greg. When you have it set to dynamic, there are a few things you have 
to do:

 

1.   Make sure that TCP/IP is enabled as a protocol using SQL Configuration 
Manager. (By default SQLEXPRESS doesn’t want external people connecting).

2.   Open 1434 for UDP inbound for the SQL Browser Service in your firewall.

3.   Configure your firewall to allow ports that are opened by the SQL 
Server executable. (That makes the dynamic port be open).

 

Then they just connect to yourmachine\SQLEXPRESS.

 

Regards,

 

Greg

 

Dr Greg Low

 

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax 

SQL Down Under | Web:  <http://www.sqldownunder.com/> www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Greg Keogh
Sent: Wednesday, 27 November 2013 5:57 PM
To: ozDotNet
Subject: Re: [OT] Public SQL Server [answer found]

 

Thanks Doctor L, thank makes pretty good sense about the fixed/dynamic ports. 
It's funny no one mentioned this issue to me before. I don't like having 
non-standard setups, so I'd actually prefer to leave all the ports the way they 
were for my SQLExpress instance, but then I don't know how to connect to an 
instance from the outside world -- Patient K

 

On 27 November 2013 17:25, GregAtGregLowDotCom mailto:g...@greglow.com> > wrote:

Hi Greg,

 

Back in the dreamtime, you could only have a single copy of SQL Server 
installed on a computer. When SQL Server 2000 appeared, they provided the 
ability to install additional copies (ie: named instances) of SQL Server in 
addition to the “default” instance. At the time, you could have 16 of them, now 
you can have 50 of them.

 

When you connect just using the name of your computer, you are connecting to 
port 1433 for the default instance.

 

Using an unmodified installation, SQL Express installs itself as a named 
instance called “SQLEXPRESS”, so instead of connecting to “mycomputer”  you 
would connect to “mycomputer\SQLEXPRESS”. You could also use the shared memory 
provider by connecting to “.\SQLEXPRESS” or “(local)\SQLEXPRESS”.

 

You can install Express as a “default” instance but that won’t be what you will 
have done. You will have installed it as a “named instance” called SQLEXPRESS.

 

The default for “named instances” is that they use dynamic ports. That’s why 
you will have seen 0 in the port settings in SQL Configuration Manager. When 
you connect to “somecomputer\someinstance”, your client starts by having a UDP 
based discussion (on port 1434) with the SQL Browser service. That service 
returns details of the port that the instance you mentioned is currently 
listening on. Your client then connects to that port.

 

Named instances can, however, be configured to use fixed ports.

 

What it sounds like you have now configured, is that you have a named instance 
configured for port 1433. While that’s uncommon, there’s nothing wrong with it 
per se. It just means that if you then tried to install a default instance (for 
example a SQL Server developer edition) using default settings, that install 
would fail.

 

Regards,

 

Greg

 

Dr Greg Low

 

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax 

SQL Down Under | Web:  <http://www.sqldownunder.com/> www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com>  
[mailto:ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> ] 
On Behalf Of Greg Keogh
Sent: Saturday, 23 November 2013 10:03 AM
To: ozDotNet
Subject: [OT] Public SQL Server [answer found]

 

It's all to do with dynamic and static ports, something I haven't anyone 
discuss before.

 

http://technet.microsoft.com/en-us/library/ms177440.aspx

 

I deleted the Dynamic Ports 0 and added TCP Port 1433 in all the IP settings. I 
don't know if all need to be changed, but I haven't got time to debug it all. I 
hope this change doesn't have any nasty delayed side effects.

 

Greg

 

Re: [OT] Public SQL Server [answer found]

[Greg Keogh]

Thanks Doctor L, thank makes pretty good sense about the fixed/dynamic
ports. It's funny no one mentioned this issue to me before. I don't like
having non-standard setups, so I'd actually prefer to leave all the ports
the way they were for my SQLExpress instance, but then I don't know how to
connect to an instance from the outside world -- Patient K


On 27 November 2013 17:25, GregAtGregLowDotCom  wrote:

> Hi Greg,
>
>
>
> Back in the dreamtime, you could only have a single copy of SQL Server
> installed on a computer. When SQL Server 2000 appeared, they provided the
> ability to install additional copies (ie: named instances) of SQL Server in
> addition to the “default” instance. At the time, you could have 16 of them,
> now you can have 50 of them.
>
>
>
> When you connect just using the name of your computer, you are connecting
> to port 1433 for the default instance.
>
>
>
> Using an unmodified installation, SQL Express installs itself as a named
> instance called “SQLEXPRESS”, so instead of connecting to “mycomputer”  you
> would connect to “mycomputer\SQLEXPRESS”. You could also use the shared
> memory provider by connecting to “.\SQLEXPRESS” or “(local)\SQLEXPRESS”.
>
>
>
> You can install Express as a “default” instance but that won’t be what you
> will have done. You will have installed it as a “named instance” called
> SQLEXPRESS.
>
>
>
> The default for “named instances” is that they use dynamic ports. That’s
> why you will have seen 0 in the port settings in SQL Configuration Manager.
> When you connect to “somecomputer\someinstance”, your client starts by
> having a UDP based discussion (on port 1434) with the SQL Browser service.
> That service returns details of the port that the instance you mentioned is
> currently listening on. Your client then connects to that port.
>
>
>
> Named instances can, however, be configured to use fixed ports.
>
>
>
> What it sounds like you have now configured, is that you have a named
> instance configured for port 1433. While that’s uncommon, there’s nothing
> wrong with it per se. It just means that if you then tried to install a
> default instance (for example a SQL Server developer edition) using default
> settings, that install would fail.
>
>
>
> Regards,
>
>
>
> Greg
>
>
>
> Dr Greg Low
>
>
>
> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913
> fax
>
> SQL Down Under | Web: www.sqldownunder.com
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com [mailto:
> ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Greg Keogh
> *Sent:* Saturday, 23 November 2013 10:03 AM
> *To:* ozDotNet
> *Subject:* [OT] Public SQL Server [answer found]
>
>
>
> It's all to do with dynamic and static ports, something I haven't anyone
> discuss before.
>
>
>
> http://technet.microsoft.com/en-us/library/ms177440.aspx
>
>
>
> I deleted the Dynamic Ports 0 and added TCP Port 1433 in all the IP
> settings. I don't know if all need to be changed, but I haven't got time to
> debug it all. I hope this change doesn't have any nasty delayed side
> effects.
>
>
>
> Greg
>

RE: [OT] Public SQL Server [answer found]

[GregAtGregLowDotCom]

Hi Greg,

 

Back in the dreamtime, you could only have a single copy of SQL Server
installed on a computer. When SQL Server 2000 appeared, they provided the
ability to install additional copies (ie: named instances) of SQL Server in
addition to the "default" instance. At the time, you could have 16 of them,
now you can have 50 of them.

 

When you connect just using the name of your computer, you are connecting to
port 1433 for the default instance.

 

Using an unmodified installation, SQL Express installs itself as a named
instance called "SQLEXPRESS", so instead of connecting to "mycomputer"  you
would connect to "mycomputer\SQLEXPRESS". You could also use the shared
memory provider by connecting to ".\SQLEXPRESS" or "(local)\SQLEXPRESS".

 

You can install Express as a "default" instance but that won't be what you
will have done. You will have installed it as a "named instance" called
SQLEXPRESS.

 

The default for "named instances" is that they use dynamic ports. That's why
you will have seen 0 in the port settings in SQL Configuration Manager. When
you connect to "somecomputer\someinstance", your client starts by having a
UDP based discussion (on port 1434) with the SQL Browser service. That
service returns details of the port that the instance you mentioned is
currently listening on. Your client then connects to that port.

 

Named instances can, however, be configured to use fixed ports.

 

What it sounds like you have now configured, is that you have a named
instance configured for port 1433. While that's uncommon, there's nothing
wrong with it per se. It just means that if you then tried to install a
default instance (for example a SQL Server developer edition) using default
settings, that install would fail.

 

Regards,

 

Greg

 

Dr Greg Low

 

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax


SQL Down Under | Web:   www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
On Behalf Of Greg Keogh
Sent: Saturday, 23 November 2013 10:03 AM
To: ozDotNet
Subject: [OT] Public SQL Server [answer found]

 

It's all to do with dynamic and static ports, something I haven't anyone
discuss before.

 

http://technet.microsoft.com/en-us/library/ms177440.aspx

 

I deleted the Dynamic Ports 0 and added TCP Port 1433 in all the IP
settings. I don't know if all need to be changed, but I haven't got time to
debug it all. I hope this change doesn't have any nasty delayed side
effects.

 

Greg