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 <g...@greglow.com <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
