Hello,
On SQL Server 2005 Dev Ed I have disabled all protocols except TCP IP.
With Sql Server Configuration manager, I have set the server port to 1450 (in sql server 2005 network configuration) and the client port to 1450 (in Sql Native Client configuration). The result is that I cannot connect anymore to my local server. I get the following error:
Cannot connect to ...
Additional information : ...TCP provider, error 0 - no connection could be made because the machine actively refused it ...
Moreover, when I run netstat during the connection I see a SYN to port 1433 which would tend to suggest that despite my configuration SQL Server Management studio try to connect on port 1433.
Anyone having successfully changed the TCP port on SQL Server 2005
Thanks a lot.
Florent
Cannot change default server port 1433 on SQL Server 2005
chandra shekhar
In TCP configuration for server protocols, when Listen All is "yes", you should modify the port number for IP All under "IP Address" tab.
Vance Chen
fourmi,
Now, I understand the problem. For native c/c++ driver, we honor the client configuration. But for managed driver, i.e. system.data, we don't. Because SQL Server Management Studio uses system.data, your client configuration does not affect your connection.
The workaround are (1) use the port number explicitly in the connection string. (2) configure alias using the SQL Server Configuration Manager and configure the port number in the alias.
AustinA6
Well, this is for security reasons... and for compatibility with our SQL Server 2000 legacy apps.
Moreover, I think is is a regression from SQL Server 2000. Enterprise manager correctly reads client network configuration. The SQL Server 2005 behaviour is also quite disturbing at first time when you don't know it.
Florent
mgalliers
Is your server a default or named instance If it is a named instance, port 1434 needs to be unblocked for the client to discover which port it should connect on. If it is a default instance, then the port will be read from the client configuration. However, if your application is specifying the port in its connection string, then only that port will be used and the client configuration won't be used.
ETE
above8848
My server is a default instance. I test only locally and have disabled Windows firewall.
My configuration is as follows:
Services: SQLServer (MSSQLSERVER) and SQL Server Browser running
Protocols for MSSQLSERVER:
Shared Memory: disabled
Named pipes: disabled
TCP/IP: enabled
VIA: disabled
TCP/IP configuration:
- Listen All: no
- IP Addresses:
- IP1: active, enabled=yes, IP Address: 10.10.10.9, TCP Dynamic ports: blank, TCP Port: 1450
- IP2: active, enabled=no, IP Address: 127.0.0.1, TCP Dynamic Ports: blank, TCP Port: 1450
- IPAll: TCP Dynamic ports: blank, TCP port: 1450
Client Protocols:
Shared Memory: disabled
Named pipes: disabled
TCP/IP: enabled, order = 1
VIA: disabled
TCP/IP protocols: Default port: 1450, Enabled=Yes
With Shared memory + TCP: able to connect with shared memory, not TCP/IP, still attempt to connect on 1433
with TCP/IP (1) + Named pipes (2) : able to connect with named pipes, not TCP/IP, still attempt to connect on 1433
with TCP/IP (2) + Named pipes (1) : able to connect with named pipes, not TCP/IP, still attempt to connect on 1433
More, if I try with osql: osql.exe -Stcp:berlioz -U sa ... or osql.exe -Stcp:berlioz,1450 -U sa ...
I can connect.
If I change the client port and try with osql without the port I can't connect, with the port I can connect.
My conclusion is that osql correctly read client configuration somewhere but SQL server management studio not.
This is quite annoying. All my production servers (mostly SQL Server 2000) are not on standard port.
Thanks,
Florent
LuckLess
Thanks Nan,
I created aliases for all my servers and I can connect.
However isn't it a bug I mean if you just modify your server port and disable all except TCP you cannot connect in management studio right away without creating an alias for your own server...
Florent
Ziran Lin - MSFT