What ports do I need to open for offshore to access our SQL Server db?

0

When using SSMS, for example, for connect to a SQL Server, I would specify the server name to which a wanted to connect as well as the Authentication method, in this case, Windows Authentication. If the SQL Server was running on a port other than the default 1433, I woyuld append a comma and the port number after the server name.

Recently, we got a new server and I am able to conenct to it without specifying the port number so my assumption was that the port number it was running on was 1433. However, I notice that when I run the following query (I am admin on the box but not the DBA), it shows me a port other than 1433.

select @@servername go DECLARE       @portNumber   NVARCHAR(10)

EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE'
                        ,@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll'
                        ,@value_name = 'TcpDynamicPorts'
                        ,@value = @portNumber OUTPUT



SELECT
    [Port Number] = @portNumber

GO

Let's say the query returns a port number of 2121. When I connect with the port number 2121, I also successfully connect.

Q: If I do not specify the port number, shouldn't the attempt fail because the attempt should default to 1433?

I am trying to figure out why our offshore folk do not have access due to ports not being opened. Shold I have requested 1433 to be opened also or just the poprt number returned by the above SQL ?

Chad

Posted 2015-03-04T18:27:03.563

Reputation: 1 654

You certainly shouldn't open any ports for your offshore folk to access SQL server. They should be connecting through a VPN. If you have been doing this, you should assume your data has been compromised. – ChrisInEdmonton – 2015-03-04T19:27:07.770

@Chris, Thanks. Offshore uses CITRIX. Does this require that a port be opened for every resource that they need to access? I'm not knowledgable at all on this topic. In my ignorant mind, I would think of CITRIX as sort of RDPing to a server within the firewall, and that a single port, the one that CITRIX uses, would need to be opened. Since I would expect the remote server to be within the firewall, I would expect no further ports to have to be opened. – Chad – 2015-03-05T15:38:49.007

Citrix is a company which has very many products. – ChrisInEdmonton – 2015-03-05T16:17:11.130

Answers

1

Answering the specific question about why the request does not fail, the SQL client being on the local network will be able to find the dynamic SQL port, however clients outside the firewall will not.

SQL named instances are configured with "dynamic" ports by default. Once a port has been "dynamically" allocated on the server, it will keep that port unless it is in use when the SQL server is started. A static port can be set if this is an issue, or if it is desired to have the SQL server listen on a particular port, eg. the default 1433.

You can either set a static port or give the offshore folk the port returned by the SQL manager. Static port means there's no chance of it changing later (even though that's a small probability).

This article shows how to configure the port statically:

http://support.microsoft.com/kb/823938

jotap

Posted 2015-03-04T18:27:03.563

Reputation: 291

@chad I can't comment on your question, the answer to your question about Citrix is that they are likely using Citrix XenApp or XenDesktop. This would likely mean that the Citrix server is in a DMZ and does not have access to the SQL server due to a firewall, or alternately it's possible that it just can't find the dynamic port because it's on a different network. To resolve, open up the relevant port (2121 in this case) between the Citrix server and the SQL server, and also create a SQL alias on the Citrix server to tell it where to find the SQL instance you are trying to connect to. – jotap – 2015-03-06T18:01:07.450

Asked: 2015-03-04T18:27:03.563

Viewed: 138 times

Active: 2015-03-04T19:21:40.140