The MS-SQL server does not answer

Answer

There can be many reasons you cannot connect to your MS-SQL server, but generally there are two main reasons:

• A local firewall blocks connections
• The utilized communications port is disabled

Before you start troubleshooting the MS-SQL connection, you should test the connection itself.

Test the MS-SQL connection

You can use the Telnet client program to test the connection. Using the Telnet client program is fast and avoids having to type username and password again and again during the test and subsequent troubleshooting.

Machines running most Windows operating systems will have the Telnet client enabled by default, but machines running Windows 7 and Windows Server 2008 do not have the Telnet client enabled by default.

To enable the Telnet client on Windows 7 or Windows 2008 server machines

1. On the local machine, click Start > Control Panel to open the Control Panel form.
2. In the Control Panel form, click Programs and Features to open the Programs and Features form.
3. In the Programs and Features form, click Turn Windows features on or off to open the Windows features form.
4. In the Windows features form, select the Telnet Client check box and click the OK button to close the form.
5. Close the Programs and Features form and the Control Panel form.
6. On the local machine, click the Start button and enter “CMD” in the search programs and files field.
7. In the Command Prompt, enter “Telnet IP-address 1433”
8. If the port 1433 is closed, an error will be returned immediately. If the port 1433 is open, you will be able to connect to the MS-SQL server.

To use the Telnet client to test a connection

Local Firewall on the MS-SQL server can block connection

If the Telnet client returns an error when testing the connection on port 1433, the local firewall may be blocking connection to the MS-SQL server.

Disable the firewall on the MS-SQL server temporarily to test if the firewall is blocking the connection.

If you are able to connect to the MS-SQL server when the firewall is disabled, you can create a firewall rule granting either local scope or specific IP addresses the right and enable the firewall again.

If you are not able to connect to the MS-SQL server when the firewall is disabled, the port 1433 itself might be disabled. Enable the firewall and can enable the port 1433 to test the connection.

Note :

Firewalls can be embedded in the Windows operating system of the local machine, part of your antivirus software package or locally installed software made by a third party software firewall company. You must identify and disable/enable the correct firewall.

Due to the many combinations and existing firewall software, it is beyond the scope of this FAQ to describe how to disable and enable the various types of firewalls.

Port 1433 disabled on the MS-SQL Server

In previous versions of MS-SQL server, port 1433 was enabled (open) by default, but as a hardening procedure implemented to improve security of the MS-SQL server, port 1433 is disabled in current versions of MS-SQL Server.

If a port is disabled, the local machine will ignore any communication requests made through that port.

Enable port 1433 on the MS-SQL Server

You can enable port 1433 on the MS-SQL server in two ways:

• Through the SQL Server Configuration Manager
• Through the Windows Registry

To enable port 1433 on the MS-SQL Server through the SQL Server Configuration Manager

1. On the MS-SQL Server machine, click Start > All Programs > MS-SQL Server 2008 R2> Configuration Tools > SQL Server Configuration Manager to open the SQL Server Configuration Manager¹
2. In the left pane of the SQL Server Configuration Manager form, click SQL Server Network Configuration > Protocols for SQLExpress
3. In the right pane, right-click the TCP/IP field in the Protocol Name column and select Properties to open the TCP/IP Properties form.
4. In the TCP/IP Properties form> IP addresses tab, expand the IPALL group and in the TCP port field, enter “1433”
5. Click the OK button to close the TCP/IP Properties form and close the SQL Server Configuration Manager form.

¹The following process description is based on the MS-SQL Express 2008 R2. Other MS-SQL versions will vary slightly.

To enable port 1433 on the MS-SQL Server through the Windows Registry¹

1. On the local machine, click the Start button and enter “Regedit” in the search programs and files field to open the Registry Editor form.
2. In left pane of the Registry Editor form, navigate to the HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ SQLEXPRESS \ MSSQLServer \ SuperSocketNetLib \ TCP folder.
3. In the TcpPort registry key, change the setting to 1433 and close the Registry Editor form.

¹The following process description is based on the MS-SQL Express 2008 R2. Other MS-SQL versions will vary slightly.