Troubleshooting SQL Server Connection Issues

You may encounter connection issues with MS SQL Server because of some of its settings not being set up properly. Some of the produced errors such as "Login failed for user" are self explanatory and can be used to fix the connection issue, while the others may not clearly mention the cause and the setting that needs to be changed.

First make sure that you have the proper SQL Server user and user permissions following the instructions in our Creating IntranetConnections User in SQL Server article.

If you're still having issues with your SQL Server connection, please make sure you have all the following set up properly:

1) Server Authentication

Make sure you have the SQL Server authentication set to mixed mode:

  1. Open SQL Server Management Studio and connect to your server instance.
  2. Right click on the name of the server (e.g. localhost) and select Properties from the context menu.
  3. In the new window, from the left hand side menu select Security
  4. Make sure the "SQL Server and Windows Authentication mode" option is selected under Server authentication.
  5. Click OK.

SQL_Server_Authentication.PNG

 

You may encounter the following error message if this is not set up correctly:

 

2) Login issues

Confirm your SQL User login password is correct

  1. Open SQL Server Management Studio and connect to your server instance.
  2. Expand Security > Logins
  3. Right-Click the IntranetConnections login (or the login you are using to connect on your datasource in Railo or ColdFusion).
  4. Click Properties
  5. Set a password for this login (you will need to be logged in with an account that has sys admin rights on the SQL Server, such as SA).
  6. Log back into ColdFusion or the Railo Web Admin > edit the datasource > update the SQL password for the login.

You may see the following message with login issues:

3) Protocols for SQL Server Instance

For SQL Server to be able to talk to other applications, the TCP/IP protocol needs to be enabled:

  1. Open SQL Server Configuration Manager (Start > Microsoft SQL Server [version] > Configuration Tools > SQL Server [version] Configuration Manager)
  2. From the left side menu select SQL Server Network Configuration > Protocols for [instance name]
  3. Make sure that TCP/IP protocol is enabled. If not, right click on it, and select Enable.
  4. If you made any changes, restart the service from the SQL Server Services section.

SQL_TCPIP.PNG

Note: the same error message shown previously may also appear due to TCP/IP being disabled.

4) SQL Server Browser Service

In order to be able to listen for incoming requests and respond with the correct instance information, SQL Server needs the SQL Server Browser service to be running:

  1. Open SQL Server Configuration Manager (Start > Microsoft SQL Server [version] > Configuration Tools > SQL Server [version] Configuration Manager)
  2. From the left side menu select SQL Server Services.
  3. Make sure SQL Server Browser is running and the start mode is set to automatic. You can start the service by right clicking on the service and selecting start.

SQL_Browser_Service.PNG

If you can't start the service or the start mode is not automatic you need to perform the following:

  1. Right click on the service and select Properties.
  2. Select the Service tab.
  3. Change the Start Mode from Disabled to Automatic.
  4. Click OK.

SQL_Browser_Service_Properties.PNG

 

5) Railo not Working with Instance Names (Railo Only)

Railo has issues connecting to SQL Server using the format [host name]\[instance name] (e.g. localhost\IntraConnections). If this is the case for your SQL Server instance and you cannot use just the host name, use the following instructions to work around the problem:

  1. Open SQL Server Configuration Manager (Start > Microsoft SQL Server [version] > Configuration Tools > SQL Server [version] Configuration Manager)
  2. From the left side menu select SQL Server Network Configuration > Protocols for [instance name]
  3. Right click on TCP/IP and select Properties.
  4. In the new window, select the IP Addresses tab
  5. Scroll all the way down to the IPAll section and make a note of the TCP Dynamic Ports

SQL_TCPIP_Properties.PNG

 

Now go to the datasource you have in the Railo Web Administor page and change the Port to the one you saw under TCP/IP properties.

SQL_Railo.PNG

 

6) Windows Firewall

Make sure you have set up your firewall such that SQL Server traffic is allowed. If you have a running firewall, you need to allow TCP traffic over port 1433, and UDP over port 1434. Please note these are the default ports, and if you are using other ports for your SQL Server, you need to use those ports.

Please refer to Microsoft's Configure a Windows Firewall for Database Engine Access to see how this can be done, or what you need to do to allow traffic over dynamic ports.

You may encounter the following error message if your firewall is blocking the SQL port you are using:

 

Referenced by:

Have more questions? Submit a request

0 Comments

Article is closed for comments.