Share via

SharePoint 2013: Troubleshooting - Cannot Connect to the Configuration Database

  • Article

Symptoms

***"Cannot connect to the configuration database orUnable to connect to database. Check database connection information and make sure the database server is running."
***This is “the” error that everyone has once in their life seen on SharePoint Product Configuration Wizard..

Causes

There are many reasons why:

  • You applied a Hotfix or Service Pack to your SQL Server and have not restarted the SQL Server.
  • The SQL database may not be running.
  • The account that is used by the application pool does not have the required permissions to the SQL Server database.
  • Network connectivity has been lost between the SharePoint Server and the Microsoft SQL Server server.

Troubleshooting steps

Symptoms

"Cannot connect to the configuration database or Unable to connect to database. Check database connection information and make sure the database server is running."

This is the error that everyone has once in his life seen on SharePoint Product Configuration Wizard.

Causes

There are many reasons why:

  • SQL database and services are down.
  • The SQL database may not be running correctly
  • You applied a Hotfix or Service Pack and did not reboot.
  • The Firewall is blocking the communication
  • The SharePoint Installation Account does not have the required permissions to the SQL Server database.
  • Network connectivity is not optimal between the SharePoint Server and SQL Server.
  • …

 

Troubleshooting steps

1.      Check logs:

Review the PSCDiagnostics log at, C:\program files\common files\Microsoft shared\web server extensions\15 or \14 for the SharePoint logs

This is the kind error that you can receive with maybe more information: System.Data.SqlClient.SqlException was thrown. Additional exception information: 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. (Provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

2.      Verify that the SQL database is running and services.

In the list of services, locate the MSSQLSERVER service and be sure that it’s running.
Be even sure that on the Microsoft SQL Server, the following services are running:

  • SQL Browser (if your aren’t using the default instance name)
  • All other SQL services

3.      Firewall

Firewall can block access and communication with your Microsoft SQL Server so you have 2 possibilities.

  1. Disable Firewall, easiest way on development machine but not secure and recommended for a Production environment.
  2. So you can create 2 rules on the Firewall:
    1. One inbound TCP rule with ports:1433,2383,2382
    2. One inbound UPD rule with port: 1434

1433: SQL Server is a Winsock application that communicates over TCP/IP by using the sockets network library. SQL Server listens for incoming connections on a particular port. The default port for SQL Server is 1433. The port doesn't need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

2383: TCP port 2383 should be open when installing a default instance or creating an Analysis Services failover cluster.

2382: TCP port 2382 should be open when installing a named instance. Named instances use dynamic port assignments. As the discovery service for Analysis Services, SQL Server Browser service listens on TCP port 2382 and redirects the connection request to the port currently used by Analysis Services.

1434: the client computer would need to open a random UDP port and the server UDP port 1434 will be used to send the instance name, and if the instance is clustered, the version of the SQL instance, the TCP port number that the instance is listening on, and the named pipe that the instance is using. However, if the goal is to minimize the number of ports open on the firewall, a static port number should be chosen for the default instance and any named instance. The client computers would need to be configured to connect to a particular ServerName or ServerName instance and specific port number.

4.      Is your SQL configured correctly?

 

Is actually your SQL server correctly setup? Are you sure about the steps that you executed? If not please check here. All these links are official TechNet articles:

  1. Installation how-to Topics
  2. Install SQL Server 2012 on Server Core
  3. Validate a SQL Server Installation
  4. Check Parameters for the System Configuration Checker
  5. Product Updates in SQL Server 2012 Installation
  6. Configure the Windows Firewall to Allow SQL Server Access

 

5.      User Permissions

Next, you have to verify that your account has the required permissions on the SQL Server database.

  1. Click Start, point to Programs, point to Microsoft SQL Server, and click Enterprise Manager
  2. In the left pane, double-click Microsoft SQL Servers, and then double-click your SQL server group.
  3. Double-click your server.
  4. Double-click Security.
  5. In the left pane, click Logins.
  6. In the right pane, double-click the user for your Farm Admin Global Administrator.
  7. In the SQL Server Login Properties dialog box, click Server Roles.
  8. And select the following:  Security Administrators and the Database Creators check boxes and then click Database Access.

 

6.      Can they talk to each other?

  1. Verify that SharePoint is using the correct IP address for the SQL server. To do this, run the ping command on the Windows SharePoint Services server.
  2. Verify that the SharePoint server is obtaining the correct IP address for the SQL server from DNS. To do this, run the nslookup command from the SharePoint Server.
  3. Make sure that there are no incorrect entries for the SQL server. To do this, examine the Hosts file on the SharePoint server. This file is in the following location:
    %systemroot%\system32\drivers\etc\Hosts
  4. On the SharePoint server, look for SQL client aliases. To do this, follow these steps: Click
    1. Start, click Run, and then type cliconfg in the open box.
    2. Click the Alias tab. By default, there are no SQL client aliases. If you have any aliases for the SQL server, verify that they are correct, or remove them.
  5. Open the SQL Server Configuration Manager (Start SQL Server 2008  Configuration Tools  SQL Server Configuration Manager
    1. Navigate to the SQL Server Network Configuration  Protocols for MSSQLSERVER node in the tree view
    2. Enable TCP/IP and Named Pipes (you’ll be warned that these changes will not apply until the service is shut down)

7.      SID

Please be sure that if you made a copy of a Virtual Machine that you used sysprep before to avoid getting the same SID! You can use PSTOOLS to change this if it’s not the case.

  • First, click to Start->Run, type sysprep and press OK.
  • This will open sysprep folder which is located in c:\Windows\System32. Open sysprep application.
  • This will open System Preparation Tool 3.14 window. As a System Cleanup Action select Enter System Out-of-Box Experience (OOBE). Important: select generalize if you want to change SID, it’s not selected by default. As Shutdown Options select Reboot.
  • After rebooting you’ll have to enter some data, for example, Country or region, Time and currency and Keyboard input.

 

8.      Reset Database-connection-timeout and is your DB up-to-date

  1. Follow the http://technet.microsoft.com/en-us/library/cc263314.aspx and change the timeout to 45 with the next command: stsadm -o setproperty -pn database-connection-timeout -pv 45
  2. Click Start, click Run, type cmd in the Open box, and then click OK.
    1. Change to the following directory:  system drive\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Bin
    2. Run the following command:
      psconfig -cmd upgrade -inplace b2b
  3. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
  4. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  5. If the TCP Port box isn’t 1433, type the port number 1433 and then click OK.
  6. In the console pane, click SQL Server Services.
  7. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

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

  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP. 
  2. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  3. If the TCP Port box isn’t 1433, type the port number 1433 and then click OK.
  4. In the console pane, click SQL Server Services.
  5. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

10.  SharePoint administrative accounts: Local Administrator

The installation account is used to set up each server in your farm by running the SharePoint Configuration Wizard, the initial Farm Creation Wizard, and Windows PowerShell. For the examples in the setup user administrator account is used for farm administration, and you can use Central Administration to manage it. Some configuration options, for example, configuration of the SharePoint 2013 Search query server, require local administration permissions. The setup user administrator account requires the following permissions:

  • It must have domain user account permissions.
  • It must be a member of the local administrators group on each server in the SharePoint farm, excluding the server running SQL Server and the Simple Mail Transfer Protocol (SMTP) server.

 

Gokan Ozcifci [MVP]