Cannot Connect to the SQL Server or Logon Failed

1 Problem
2 Solution
- 2.1 SQL Native Client Version
  - 2.1.1 07.01.00.00 and later
  - 2.1.2 07.00.175.00
  - 2.1.3 07.02.08.00
- 2.2 DNS Problems
- 2.3 Connectivity Error
- 2.4 Allow SQL Server Through the Firewall
- 2.5 Prefer TCP/IP Connections
- 2.6 Allow Remote Connections
3 Related articles

Problem

Connectivity issues from the client to the SQL server can manifest itself in a number of ways. Sometimes no connection is possible at all, other times communication between the client and server is "flaky", giving intermittent error messages. Below are two examples of common connectivity issues:

Example 1 - Cannot log in to Jiwa - an error message similar to "Error : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections." is displayed when attempting to log in to Jiwa.

Example 2 - Running a report within Jiwa gives an error message of "Logon Failed. Please try again."

Solution

If you are having trouble connecting to the SQL Server, you should begin the troubleshooting process by using SQL Server Management Studio - this is the official tool used to connect to and manage Microsoft SQL Servers. Microsoft offer this as a free download available from [here]. After installing, confirm that you still cannot connect to the SQL Server by attempting a connection using the SQL Server Management Studio:

Look carefully at the error message given. If you have simply entered the username or password incorrectly, the error message will reflect this.

If you are indeed having connectivity issues rather than an incorrect username or password, the error message given may be something more like:

There will normally be a delay between clicking the connect button and receiving the error message if it is connectivity issue rather than an authentication issue. This is because in the case of an authentication issue, the connection is made immediately but the server refuses login, whilst in the case of a connectivity issue the SQL Management Studio will try over and over to connect for around 15-30 seconds before giving up and reporting the error.

SQL Native Client Version

If you are able to connect using the credentials with SQL Management Studio, and Jiwa the application is able to connect but generating reports produces the "Logon Failed" error - then the problem may be the SQL Native Client has been removed from the machine, or the SQL Native client version is no longer the version Jiwa expects.

Jiwa installs with the SQL Native Client 11 and when generating crystal reports Jiwa will force the database provider to be "SQLNCLI11" - SQL Native Client 11 - so if a user or other software has removed or changed the version of SQL Native Client then crystal reports will generate this error.

To rectify this, either re-instate the correct version of SQL Native Client, or use one of the following plugins to modify Jiwa's behaviour to force Crystal Reports to instead use the OLEDB provider - making report generation in Jiwa no longer dependent on the SQL Native Client.

07.01.00.00 and later

07.00.175.00

07.02.08.00

DNS Problems

If you are able to connect using the credentials with SQL Management Studio, and Jiwa the application is able to connect but generating reports produces the "Logon Failed" error - then the problem may be your DNS service of your network. Jiwa uses connection resiliency and transient fault tolerance which will often mask DNS issues, but Crystal Reports does not employ such strategies, and so you may see the generating of reports produce the "Logon Failed" error.

To test if your DNS service is a factor, change the connections in Jiwa to use the IP address of the SQL Server instead of the SQL Server name (This is done by choosing the Connections... button of the Jiwa login form). If the problems no longer occur when generating reports, then you should make corrective action with the DNS configuration of your network - consult your IT Department or IT consultant.

Note that DNS issues can be notoriously intermittent - so test connections using the IP address for at least several hours before pointing the blame on DNS issues alone.

Connectivity Error

In the case of a connectivity error (unable to connect using the credentials with SQL Management Studio ), then there are 3 troubleshooting steps we can take in the case of a connectivity error.

Allow SQL Server Through the Firewall
Prefer TCP/IP Connections
Allow Remote Connections

Allow SQL Server Through the Firewall

The server machine may have a firewall enabled that is blocking incoming connections to the SQL Server. To allow SQL Server communication to occur through the firewall follow the steps below.

If the SQL Server is running multiple instances, then dynamic ports will be in use. To this end we will be allowing the SQL Server program to communicate through the firewall on any port it wishes rather than opening a specific port.

1. Right click on the Windows Start menu and choose "Control Panel".

2. From the Control Panel choose "Windows Firewall".

3. From the Windows Firewall choose "Advanced settings:

4. Create a new "Inbound Rule" by right clicking on the "Inbound Rules" node on the left and choosing "New Rule..." from the context menu that appears.

5. Select "Program" as the rule type and click Next.

6. Select "This program path" and browse to the location of the main SQL Server executable. Click Next.

The main SQL Server executable is called "sqlservr.exe" and is usually found at "C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn" (32-bit) or "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn" (64-bit). The "MSSQL12.MSSQLSERVER" part may vary depending on the version of SQL Server installed, i.e. the "12" indicates SQL Server 2014, whereas SQL Server 2016 would have "13" in the place of the "12".

If you cannot find the correct location, try performing a file-system search for "sqlservr.exe".

7. Choose "Allow the connection" as the action. Click Next.

8. Ensure that Domain, Private, and Public are all ticked for the profile. Click Next.

9. Give the new rule a name of "SQL Server". Click Next.

10. Click Finish.

SQL Server Management Studio should now be again used to attempt to establish a connection. If the connection still fails, follow the second troubleshooting step provided below.

Prefer TCP/IP Connections

If allowing the SQL Server program through the firewall as per above does not solve the connectivity issue, then we must check that the SQL Server itself is listening for TCP/IP connections. To do this the "SQL Server Configuration Manager" is used on the SQL Server machine.

1. Launch the "SQL Server Configuration Manager". In Windows Server 2012 R2 and above this is done by clicking on the Start menu, typing "SQL Server Con", and then clicking the "SQL Server 2014 Configuration Manager" icon which appears on the right.

2. Expand the "SQL Server Network Configuration" node on the left.

3. Click "Protocols for SQLEXPRESS" (this may vary depending on the version of SQL Server installed).

4. Right-click on "Named Pipes" on the right and choose Disable (if it isn't already disabled).

5. Right-click on "TCP/IP" on the right and choose Enable (if it isn't already enabled).

6. For the changes to take affect, the SQL Server must be restarted. To do this, click on the "SQL Server Services" node on the left.

7. In the right-hand pane, right-click on "SQL Server" and choose "Restart".

After a minute the SQL Server Service will have restarted and will be listening for TCP/IP connections. SQL Server Management Studio should now be again used to attempt to establish a connection. If the connection still fails, follow the third troubleshooting step provided below.

Allow Remote Connections

If you are able to connect to the SQL Server using SQL Server Management Studio on the server machine itself, then you should ensure that the SQL Server is configured to accept connections from remote computers.

1. Connect to the SQL Server using SQL Server Management Studio (i.e. on the physical server itself).

2. Right click on the server node and choose "Properties".

3. On the Server Properties windows, choose "Connections" in the left hand pane.

4. On the right hand pane, ensure that the "Allow remote connections to this server" is ticked (see screenshot above). If it is not, tick it, and restart the SQL Server Service for changes to take affect.