SQL Server Connection

Problem:

How to configure the SQL server to listen on a specific port?

Solution:

Due to security issues it is often not recommended to use the default port number 1433 for communicating with the SQL server. This article helps set up the SQL server to use a non-standard port number.

  1. Run the SQL Server Configuration Manager.
  2. Select the SQL Server Network Configuration.
  3. Select from the list the instance you want to configure to listen to on a specific port.
  4. To change the port assignment right-click on the TCP/IP protocol and select Properties.
  5. Click on the IP Addresses tab.

    TCP/IP Addresses

    Please note! Both IP5 and IP6 are disabled and the TCP Dynamic Ports setting is set to “0”, which means that the database engine is listening on dynamic ports. This instance currently uses port number 1433.
  6. Specify the port number you want to use instead of 1433 by entering the preferred port number. In this case the new port number is going to be 8181. Also, turn off the dynamical port number setting by removing the “0” mark in the TCP Dynamic Ports field.

    TCP/IP Properties

  7. In order to finish the adjustment, select SQL Server Services, right-click the SQL Server and restart it.

Problem:

The SQL Server Browser service is not running. When this service is not running you cannot connect on named instances.

Solution:

The SQL Server Browser service needs to be up and running.

  1. Open SQL Server Configuration Manager on your SQL server.
  2. Click on SQL Server Services. Find the SQL Server Browser service, right-click on it and press Start.
  3. Do not forget to allow inbound traffic on TCP Port 1434.

Problem:

The wrong database server name has been entered while trying to connect to the database server through the SysKit Wizard.

Solution:

Check if your SQL server is configured to use a named instance, e.g.Server/InstanceName.

  1. Run the Microsoft SQL Server Management Studio.
  2. Right-click on the preferred server and select Properties.
  3. Check the full server name, i.e. both server and instance name.
  4. Check Full Server Name

  5. Change the server name in the SysKit Configuration Wizard according to the name written in the Microsoft SQL Server Management Studio.
  6. SQL connection should now be available.

Problem:

TCP/IP is not enabled on the SQL server.

Solution:

TCP/IP network traffic needs to be enabled on the SQL server, so that remote connections will be allowed on the SQL server.

  1. Run the SQL Server Configuration Manager.
  2. Click on the SQL Server Network Configuration.
  3. Select Protocols for SQLEXPRESS and check if the TCP/IP Protocol is enabled.
  4. If the TCP/IP is disabled, double-click on it and change the Enabled row status to Yes.
  5. In order to finish the adjustment, select SQL Server Services, right-click on the SQL Server and restart it.
  6. The SQL connection should now be available.

Problem:

Port 1433 is closed on the SQL server.

Solution:

Inbound traffic on TCP Port 1433 needs to be allowed on the SQL server.

  1. Connect to your SQL server.
  2. Open the Windows firewall.
  3. Click on Inbound Rules.
  4. Select New Rule.
  5. Select the Port type of the rule.
  6. Specify the ports to which this rule applies. In this case, 1433 stands for the SQL server, and 1434 for the SQL server browser.

    Specify Ports

  7. In this step, leave the default selection: Allow the connection.

    Allow The Connection

  8. Select the profiles for which this rule applies.

    Specify The Profiles For Which This Rule Applies

    Please note! If the SQL server is in the same domain with other servers and you wish to open this port only for the domain traffic, it is enough to select Domain. For other scenarios use the Private or Public profile option.
  9. Specify the name and the description of this rule.

    Rule Name

  10. Inbound traffic on the SQL server is now allowed and SQL connection should be available!