SharePoint 2013: cannot connect to database at SQL server at... The database might not exist...

Problem

You are engaged in building a new SharePoint Server 2013 farm on Windows Server 2012 servers.  You have successfully installed all of the prerequisites and roles and features on all servers that will host SharePoint.  You have installed and configured a new instance of SQL Server 2012. You have configured a database server alias on all SharePoint servers.  You have not yet run the configuration wizard, but are now beginning configuration tasks. Your first task is to create the configuration database manually so as to avoid the lengthy GUID that SharePoint configuration wizard normally appends to the database name. On the batch serverYou run New-SPConfigurationDatabase, and then experience the following response in the management shell:

New-SPConfigurationDatabase : Cannot connect to database master at SQL server at [DatabaseAlias]. The database might not exist, or the current user does not have permission to connect to it. At line:1 char:1 + New-SPConfigurationDatabase -DatabaseName DB_Config -DatabaseServer [alias] - ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData:(Microsoft.Share... urationDatabase:SPCmdletNewSPConfigurationDatabase) [New-SPConfigurationDatabase], SPException + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewSPConfigurationDatabase

This is a critical problem, as it prevents further installation efforts.  Below are my troubleshooting steps and ultimate resolution.

Troubleshooting

1. Action: verified spelling of database server.
1. Results: verified.
2. Action: verify database alias (cliconfg).
1. Results: alias configured and appears to be correct.
3. Action: verify farm service account name and password by adding to managed accounts of another SharePoint farm.
1. Results: farm service account name successfully added to Managed Accounts of another farm.
4. Action: verify that farm service account added to SQL Server instance logins and configured with dbcreator and securityadmin roles.  Start SQL Server Management Studio, navigate to [name]\security\.
1. Results: verified.
5. Action: verify that all SQL server services are running.  Started SQL Server Configuration Manager
1. Results: verified that SQL Server, SQL Server Agent and SQL Server Browser are all running.
6. Action: verify that TCP/IP protocol is enabled. started SQL Server Configuration Manager
1. Results: verified that TCP/IP is enabled for SQL Native Client 11.0 Configuration (32 bit), SQL Server Network Configuration and SQL Native Client 11.0 Configuration.
7. Action: verify that remote connections are enabled. In SQL Server Management Studio, right-click server name instance in tree, select Properties, select Connections, look for Allow remote connections to this server.
1. Results: was enabled.
8. Action: check (trarget) SharePoint server Application log.
1. Results: found the following events correlated with attempts to run script:

   Log Name:      Application
   Source:        Microsoft-SharePoint Products-SharePoint 
                  Foundation
   Date:          [date/time]
   Event ID:      5586
   Task Category: Database
   Level:         Error
   Keywords:      
   User:          [Administrator]
   Computer:      [ServerName]
   Description:
   Unknown SQL Exception -1 occurred. Additional error information 
   from SQL Server is included below.
   
   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)
   Event Xml:...
   ...
   

   
   and
   
   

   Log Name:      Application
   Source:        Microsoft-SharePoint Products-SharePoint 
                  Foundation
   Date:          [date/time]
   Event ID:      3363
   Task Category: Database
   Level:         Critical
   Keywords:      
   User:          [Administrator]
   Computer:      [ServerName]
   Description:
   Cannot connect to database master at SQL server at 
   [DatabaseServerAlias]. The database might not exist, or the 
   current user does not have permission to connect to it.
   Event Xml:
   ...
   

2. Observation: issue likely due to connectivity with database; possible firewall issue - firewall blocking communication.
9. Action: disable firewall.  On Server Manager, select Local Server, then click link next to Windows Firewall.  Then click Turn Windows Firewall on of off.
1. Result: Settings are managed by GPO - can't change.
10. Action: run netsh command: netsh firewall set allprofiles state off, then re-run New-SPConfigurationDatabase.
1. Result: same connection error.
11. Action: run netsh command: netsh firewall set opmode state off, then re-run New-SPConfigurationDatabase.
1. Result: same connection error.
12. Action: attempt ODBC connection using ODBC Data Source Administrator
1. Results: connection failed.
   
13. Action: in Services control panel, set startup to Disabled.  Restarted server.
1. Results: On reboot, Firewall disabled.
14. Action: attempt ODBC connection using ODBC Data Source Administrator.
1. Results: connection succeeded.
2. Observation: this is a firewall issue on the SQL Server instance.
15. Action: started Firewall service, then ran netsh scripts again, this time setting states to On.  Then tested ODBC connectivity again.
1. Results: connection failed.
16. Action: on SQL Server, using the Windows Firewall with Advanced Security configured two TCP and two program inbound firewall rules: SQL - sqlbrowser.exe, SQL - sqlservr.exe, SQL - TCP 1433 and SQL - UDP 1434. Then tested ODBC connectivity again.
1. Results: connection failed.
17. Action: reviewed Firewall log.
1. Results: all TCP packets sent to SQL Server ports were being dropped.
18. Action: discussed results with sysadmin, who noted impact of group policy object.  recommend that rules be created in local GPO instead.  This can be verified by viewing rules under Windows Firewall with... / Monitoring / Firewall.
1. Results: none of the new firewall rules were listed.
2. Observation: New firewall rules were being overridden by GPO.
19. Action: launched local GP editor applet.  Configured the four rules noted previously.  Then tested ODBC connectivity again.
1. Results: connection succeeded.

Solution

• Implement firewall rules as noted in reference [3].  These may need to be configured in GPO if Firewall access controlled by GPO.

References

1. SharePoint: How to setup a database server alias
2. New-SPConfigurationDatabase
3. How to configure SQL Express 2012 to accept remote connections
4. Stopping the Windows Authenticating Firewall Service and the boot time policy
5. Top 10: Windows Firewall Netsh Commands
6. Enabling/Disabling the firewall using command line

Notes

• Traditional SharePoint farm topology.