CODEWITHSUNDEEP
c#sqlsql-serverport
Thomas
Thomas

Reputation: 21

Connection to SQL Server uses random ports

I have a problem connecting to a SQL Server using C#. I have to connect to a SQL Server instance, that is behind a firewall. I am using the following code (I don't have the exact code snippet right now, but this should be very close to it):

using (SqlConnection myConnection = new SqlConnection("Server=myServerName\myInstanceName;" +
                                                      "Database=myDataBase;" +
                                                      "User Id=myUsername;" +
                                                      "Password=myPassword;")
{
    myConnection.Open();

    using (SqlCommand myCommand = new SqlCommand("select * from table", 
                                                  myConnection))
    {        
        myReader = myCommand.ExecuteReader();
        while(myReader.Read())
        {
            // do sth.
        }   
    }
}

When I run this code, it seems to need TCP-Connections on a range of very high ports (that is what my Firewall administrator told me). The first time I ran it, it tried to establish TCP-Connections on ports 65175-65177. When we tried it again a while later, it then tried to connect via TCP on three ports somewhere around 45000.

This is a problem, because we don't know which ports to open, because they seem to be random (which seems very weird to me).

All I could find with google is, that normally you only need port 1433 TCP and 1434 UDP. We opened those, but then encountered the problem above.

Can somebody explain to me, why this code tries to connect on these high, random ports? Or how to tell my code not to do that?

A bit of additional information:

Upvotes: 1

Views: 2284

Answers (1)

Thomas
Thomas

Reputation: 21

As Martin Smith pointed out in a comment, the solution was to change the configuration of SQL Server, because it was configured for dynamic ports.

Copied from https://technet.microsoft.com/en-us/library/ms177440(v=sql.105).aspx:

To assign a TCP/IP port number to the SQL Server Database Engine

1.In SQL Server Configuration Manager, in the console panel, expand SQL Server Network Configuration, expand Protocols for instance name, and then double-click TCP/IP.

2.In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

3.If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

4.In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

5.In the console pane, click SQL Server Services.

6.In the details pane, right-click SQL Server (instance name) and then click Restart, to stop and restart SQL Server.

Upvotes: 1

Related Questions