SQL Express: is there a commandline way to set the default static port of a named instance?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith - 20 Aug 2007 23:23 GMT

Hello,

I'd like to set the default static port of a named SQL Express instance
either at install or immediately afterward from the command line. I know
how to do it using the UI tools, but I want to do this invisibly from my
installer.

I've experienced other apps that do it but can't find any docs or examples.
I half-expected to find a setup switch to go along with
DISABLENETWORKPROTOCOLS, like STATICPORT or something but no.

Is there a way to do it from sqlcmd or something similar? I don't want to
have to install additional tools just to do this either.

Thanks!

Keith

Reply to this Message

Rick Byham, (MSFT) - 21 Aug 2007 16:01 GMT

Check out the Books Online topic How to: Configure the Database Engine to
Listen on Multiple TCP Ports.

Signature

Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

> Hello,
>
[quoted text clipped - 13 lines]
>
> Keith

Reply to this Message

Keith - 21 Aug 2007 18:58 GMT

Is using the method described, e.g.

USE master
GO
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
GOequivalent to what happens when the default port is changed using the
Configuration Manager?Does this turn off dynamic ports as well? I suppose
this means that there is no wayto set this up using the installer and

> Check out the Books Online topic How to: Configure the Database Engine to
> Listen on Multiple TCP Ports.
[quoted text clipped - 15 lines]
>>
>> Keith

Reply to this Message

Keith - 22 Aug 2007 19:52 GMT

OK,

This morning I thought "Hey, I bet I can change the instance's registry
values for the tcp ports!" and bypass all this other stuff. I changed them
using regedit and everything seems to work fine, so I wrote some C# code to
do to test it out and

"UnauthorizedAccessException: Cannot write to Registry Key."
StackTrace = " at
System.ThrowHelper.ThrowUnauthorizedAccessException(ExceptionResource
resource)\r\n at Microsoft.Win32.RegistryKey.SetValue(String name, Object
value, RegistryValueKind valueKind)\r\n at
Microsoft.Win32.RegistryKey.SetValue(String name, Object va...

I'm not sure what to do about this since the exception contains no helpful
information.

Suggestions?

k

Reply to this Message

Keith - 24 Aug 2007 22:24 GMT

Anyone have some thoughts on this?

> OK,
>
[quoted text clipped - 14 lines]
>
> Suggestions?

Reply to this Message

Keith - 22 Aug 2007 20:27 GMT

> Is using the method described, e.g.
>
[quoted text clipped - 9 lines]
> this means that there is no wayto set this up using the installer and
> cmd-line flags?k

OK. Tried this, and SQL Server said:

This "CREATE ENDPOINT" statement is not supported on this edition of SQL
Server."

This is against SQL Server 2005 Express, version 9.0.30.42.

Suggestions?

Reply to this Message

Keith - 24 Aug 2007 22:27 GMT

MS folks, do you have any suggestions or comments on the faliure I listed?
The BOL does not say that CREATE ENDPOINT is for SQL Server 2005 only (vs.
Express) and actually lists specific limitations for Express when using some
options, implying that there should be no other issues.

k

>> Is using the method described, e.g.
>>
[quoted text clipped - 18 lines]
>
> Suggestions?

Reply to this Message

Rick Byham, (MSFT) - 27 Aug 2007 16:57 GMT

I wasn't aware that there was a restriction on CREATE ENDPOINT for SQL
Server 2005 Express. I'll work on confirming that and adding the restriction
to the documentation. That won't help you much though.
I'm surprised that the registry hack didn't work. We don't support people
making direct changes to the registry and I haven't tried it, so I can't
offer advice on that.

Signature

Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

> MS folks, do you have any suggestions or comments on the faliure I listed?
> The BOL does not say that CREATE ENDPOINT is for SQL Server 2005 only (vs.
[quoted text clipped - 25 lines]
>>
>> Suggestions?

Reply to this Message

Keith - 27 Aug 2007 21:22 GMT

Rick,

I appreciate your looking into this. I've continued to monkey around with
the registry approach but haven't had any luck yet.

I've also discovered issues with the following combination: SQL Server,
dynamic ports, firewall exception for sqlservr.exe, and a Virtual PC VM.

Because I haven't been able to programmatically set a static port for use
with the XP firewall, I've tried to use dynamic ports and added an app
exception as recommended by MSDN. the SQL Browser is also running and I
have a port exception (1434 TCP) for it as well. This setup works just fine
on a "real" machine, but on an XP SP2 virtual machine it doesn't. I can
locate the SQL instance using apps that use the SQL browser (like Management
studio) but I can never conect to it, even using other MS tools. I tried
changing the networking style of the VM (from bridged to NAT) and things
just got worse; SQL Browser-aware apps couldn't find the instance at all.

If I manually change to static ports (for both SQL and the firewall)
everything works as expected.

>I wasn't aware that there was a restriction on CREATE ENDPOINT for SQL
>Server 2005 Express. I'll work on confirming that and adding the
>restriction to the documentation. That won't help you much though.
> I'm surprised that the registry hack didn't work. We don't support people
> making direct changes to the registry and I haven't tried it, so I can't
> offer advice on that.

Reply to this Message