I have a named instance installed and have it set to use TCP port 1433 instead of Dynamic ports. The SQL browser is running and TCP IP is enabled. I have to put the port number 1433 on ssms and without including the port number it wont connect. Please advice what am I missing here. Is this a firewall issue or am I missing something.

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports would be used by default: - SQL Server service - Port 1433 (TCP) - SQL Server Browser service - Port 1434 (UDP) - Dedicated Admin Connection - Port 1434 (TCP) **QUESTIONS:** - Is this advice still relevant? - Should ALL of the above ports be changed?

I have an MSSQLServer2000 database that seems to have been working just fine until today. The DB connects to a java application on a remote server that passes the database data (login credentials, various records in tables) to other system elements in this network. This java application needs to connect to the DB for the other systems to do their job. As far as I can tell there should be no issues with connectivity between this java application and the DB. netstat -an shows an ESTABLISHED connection between both IPs on port 1433, I am able to authtenicate on the SQL server locally with the credentials provided to the java application. There are no apparent errors in the server event viewer logs, nor have there been any changes to the system I am aware of....and yet the application fails to connect. The application needs the following information in its connection pool fields to connect to the DB: Instance (which I queried with SELECT @@servicename) Schema Owner (which i queried with `SELECT schema_name, schema_owner FROM information_schema.schemata`) and the login user and password (which I know to be accurate and can login in via SQL Enterprise Manager) 1) Is there anything else I should be looking out for? 2) are those the correct queries to collect the information I needed? it seems the Instance is MSSQLSERVER and the schema owner dbo. I am very new and green to SQL, not a DBA or software guy by any stretch. I have not ruled out reinstalling the java app. I have checked that TCP/IP port 1433 is enabled (as well as Named Pipes in the SQL Server Network Utility), restarted SQL services, and for good measure rebooted both servers. and this java app remains unable to connect to the server. edit: there was one thing I noticed in the SQL logs. a message about remote access set from 1 to 0? and I ran EXEC sp_configure 'remote access', 1; that didnt seem to have any effect on letting me get connected. edit #2: 1) OS is MS Server 2000. 2) Yes I can. I have another serer running SQL 2008 on MS Server 2008 and I can connect remotely from that SQL Server to this one, using the same credentials I supply to the java application. 3) no recent security updates to my knowledge. thanks for any input!

I have a new server that is configured using a dynamic port as you can see below: The application that connects to this server uses the following connection string, but I am getting the following error: I asked the developer to change the connection port from 61844 to 53187, which is my dynamic port. After done that and restarting their services, the application could connect to SQL Server. Is there any specific reason it needs to be using a dynamic port?

I have built a named instance which is running on a static port, 50101. The network guys have opened the port on the firewall to allow incoming connections. I have asked them open UDP port 1434 too, as I believe the SQL Browser Service runs on this port and needs to pass the correct port for the named instance back to the requesting connection. Even if the named instance is running on a static TCP port. Is that correct? SQL Browser / UDP 1434 is not just for dynamic ports, right? It needs to be open if the port is not explicit in the connection string? Really need to end this argument! MP

Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port? Script i am trying to use: DECLARE @TcpPort VARCHAR(5) ,@RegKey VARCHAR(100) IF @@SERVICENAME !='MSSQLSERVER' BEGIN SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll' END ELSE BEGIN SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPAll' END EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' ,@key = @RegKey ,@value_name = 'TcpPort' ,@value = @TcpPort OUTPUT EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' ,@key = @RegKey ,@value_name = 'TcpDynamicPorts' ,@value = @TcpPort OUTPUT SELECT @TcpPort AS PortNumber ,@@SERVERNAME AS ServerName ,@@SERVICENAME AS ServiceName

I have the following script to find out what is the Static and dynamic port numbers: --=============================== -- sys.dm_exec_connections --=============================== SELECT [user]=suser_sname() ,local_tcp_port ,session_id ,connect_time ,net_transport ,protocol_type ,encrypt_option ,auth_scheme ,last_read ,last_write FROM sys.dm_exec_connections WHERE session_id = @@SPID GO --=============================== -- Execute below script if SQL Server is configured with dynamic port number --=============================== DECLARE @portNo NVARCHAR(10) EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpDynamicPorts', @value = @portNo OUTPUT SELECT [PortNumber] = @portNo GO --=============================== -- Execute below script if SQL Server is configured with static port number --=============================== DECLARE @portNo NVARCHAR(10) EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpPort', @value = @portNo OUTPUT SELECT [PortNumber] = @portNo GO The following link mentions netstat -ano to check usage from the cmdline. When is a Dynamic Port “dynamic”? when is the dynamic port used?

Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?

My SQL Server instance is hosted on an internal network server (10.x.x.x address). It's a named instance currently using dynamic ports. An external application at a third-party hosting location connects to the instance through use of the IP address and port #. After each maintenance window I have to confirm the dynamic port # in use is the same as before as the firewall rule is set to allow connections between application host and database host on that specific port #. Is this the correct way to ensure the externally-hosted app can connect to the host without having to change port numbers in the connection string or should another port forwarding method be used (i.e., port 9999 on application host is mapped to port 1434 on SQL host)? What configuration manager changes in the Network Configuration section should be applied to allow the external application to use the same port # but internal applications to rely on the Browser server to make the correct mapping?

If I have a web server trying to communicate to a SQL Server through a firewall, and the firewall is completely closed both ways (**inbound AND outbound**), I know I have to open port 1433 from the web server to the SQL Server in that direction. However, do I need to open any ports from the SQL Server to the web server for the SQL activities to work? Does SQL Server respond back with any other ports?

I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work. **First question:** If the IPALL TCP Dynmaic Ports setting is a specific number (say 1971) does that signify that you have a static port of 1971 or a dynamic port that is currently 1971 and may change at some point in the future. **Second question:** This is the one I'm most curious about. We have an instance that has had the same port (the value in the IPALL TCP Dynmaic Ports setting) for several years through dozens of instance restarts. What actually causes the dynamic port to actually change after an instance restart?