Cannot connect to database master at sql server – Checklist

The problem

You get the following error during SharePoint 2013 Products Configuration Wizard on the Specify Configuration Database Settings page:

Cannot connect to database master at sql server <Servername>. The database might not exist, or the current user does not have permission to connect to it.

Cannot connect to database master at sql server
Cannot connect to database master at sql server

Checklist

Your first move should be the following: Try it again, but this time mark the time between you press Next > and when the error message pops up.

Based on this:

  • if its more than a few seconds, you have network connectivity issue or SQL server configuration problem (start from no 1)
  • if it pops up almost instantly, you have permission problems on the SQL server instance (See no. 10 and 9)

Accounts

Let’s start with a little overview of the accounts. At this moment you have two important accounts to play with:

  • setup/install account: you install SharePoint with this account, just use it to log on to the machines and start the prerequisite installer then SharePoint installer and finally the configuration wizard. It has to be an administrator on the SharePoint machines to perform these steps. Furthermore it provisions the databases on the SQL backend and needs to own the dbcreator and sysadmin roles.
  • farm admin: it has no rights first, simply a domain user. SharePoint config wizard will grant everything for it, just submit its name and password during setup. You do not log in with it during install, you use the previous account for that and you do not grant anything for it, the wizard takes care of that.

The checklist

  1. Check you typed the SQL server’s name and you typed it correctly.
  2. If you use an instance other than the default, check that you specified its name
  3. Check you can resolve the SQL server’s name to the IP address (if your SQL has multiple interfaces, check it is the correct one)
  4. Check your SQL server service is running on your SQL box
  5. Check in SQL Server Configuration Manager that TCP/IP protocol is enabled
  6. Check TCP/IP protocol listening ports and IP addresses
  7. Check Windows Firewall (profile settings and rules)
  8. Test connectivity between your servers
  9. SQL server client alias configuration on the SharePoint server
  10. Your install account login and permission settings in the SQL instance

Sometimes you can gain useful information from various logfiles so it is always a good idea to have a look at them or at least know where they are:

  • Event viewer on the SharePoint machine
  • SharePoint Products Configuration Wizard’s logfile in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS (by default and their name’s start with PSCDiagnostics_<date>_<SomeID>)
  • Event viewer on the SQL server
  • Windows firewall logfiles (you have to turn it on first though, see that part later)

1. Check you typed the correct name

Well, this is very straightforward but make sure you set up everything on the correct server and you typed that name here, also be careful with typos.

2. Check your instance name

Do you want to use a named instance other than the default one, submit it in the following order: Servername\instancename Make sure it has the same name on SQL server as well among services.

3. Name resolution check

Start a command prompt, ping your machine and review the IP address. Now check the IP addresses on your SQL machine with ipconfig or on the interfaces. If they are matching jump on the next point.

My SP machine resolves this name to an incorrect IP
On my SharePoint machine: resolves this name to an incorrect IP
My SQL machine does not have an IP like that
On the SQL machine: it does not have an IP like that (10.1.1.111)

Otherwise try troubleshooting this name resolution problem. If your SQL server has multiple cards make sure DNS resolves it to the correct one. Review your records in DNS and correct them if necessary.

DNS record is correct and it resolves to the 10.1.1.5 address
DNS record is correct and it resolves to the 10.1.1.5 address

Also make sure that your SharePoint machine’s host file (C:\windows\system32\drivers\etc) does not contain invalid data.

Hosts file
my hosts file contains invalid data

4. SQL server service is running

It’s lame, I know, but I have seen this so just make sure your DB service of the correct instance is running:

Service is not running
Service is not running (SQL Server MSSQLSERVER)

5. TCP/IP protocol for SQL server is enabled

On the SQL box open SQL Server Configuration Manager and under Network Configuration node check that TCP/IP is enabled. (If you modify this don’t forget to restart the service)

TCP/IP protocol is not enabled on my SQL instance
TCP/IP protocol is NOT enabled on my SQL instance

6. Check listening ports and IP addresses

In SQL Server Configuration Manager open the Properties of the enabled TCP/IP protocol. Here you can specify the IP address and listening port of this instance. On the first tab if Listen All is enabled (Yes) the used port is in the IP Addresses tab’s last section, IPAll/TCP port. This means that the service listens on all interfaces on this port. Use netstat -ab to review this.

Listen on all interfaces and use port 14333
Listen on all interfaces and use port 14333
Sql listens on port 14333
According to the settings above SQL indeed listens on port 14333

If it is not the default port (1433) you have to indicate this in your connection string (in Config Wizard’s database server parameter)

On the first tab if Listen All is disabled (No) you can set listening on individual ports and cards just don’t forget to enable at least one!

Listening on this interface is enabled, uses default port
Listening on this interface is enabled, uses default port
Netstat reflects the changes
The netstat output shows the changes nicely

Wit this info in hand you should be able to telnet from the SharePoint machine to the specified port with the name to verify network connectivity.

Telnet failed
Telnet failed

If it does not work check firewall settings on the SQL box and between the servers.

7. Windows firewall

If windows firewall is enabled verify it blocks the connection or not. First enable logging here (do this on the profile you use): Windows Firewall with Advanced Security / Right click and select Properties / Click Customize in the Logging section:

  • Log dropped packets : Yes
  • Note the logfile’s path

Try to connect with telnet again from the SharePoint machine and review the content of the logfile.

Windows firewall blocks my connection
Windows firewall blocks my connection

To remediate this turn windows firewall off on the selected profile (or on all profiles) otherwise create an allow rule for this incoming connection, whichever suits your scenario.

8. Other connectivity check between your servers

If your telnet does not even reach the SQL server and there is nothing in the logfile maybe you have connectivity issues between your machines. Are there any firewalls between them? If yes, create firewall rules or ask your firewall team to do it.

9. SQL Server client alias setting

Type cliconfg on your SharePoint machine and review the alias tab. If there is any entry make sure it points to the correct server and you use the correct alias during configuration wizard.

SQL alias
My SQL alias points to server sql01 on port 1433 so I can use “sql5” in configuration wizard to set up a connection

SQL alias has the advantage to change SQL server later easily (connection parameters) while the server name remains the same for SharePoint.

10. SQL permissions

On SQL server open the Application log in the event viewer and look for ID 18456, Category Logon. Don’t be fooled this won’t be marked as an error or warning, simple Information is the level.

Failed logon with the install account
Failed logon with the install account

Did you start the configuration wizard with your install account? If yes you have to check SQL Logins and assigned roles for your user with Management Studio (connect to your instance). Remember, your setup user needs a Login and have to be a securityadmin and a dbcreator! (http://technet.microsoft.com/en-us/library/ee662513.aspx)

Setup user roles
Setup user roles: yes indeed, he can create databases and manage permissions

If everything seems right you can always check the logs to gain more information, here they are again:

  • Event viewer on the SharePoint machine
  • SharePoint Products Configuration Wizard’s logfile in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS (by default and their name’s start with PSCDiagnostics_<date>_<SomeID>)
  • Event viewer on the SQL server
  • Windows firewall logfiles

That’s all from me, if you miss something or you met with other problems, please let me know and I include it!

[Update]: 07.04.2013: Added Accounts section.

Advertisements

21 thoughts on “Cannot connect to database master at sql server – Checklist”

  1. Hey thanks for posting this. I had created an account for sharepoint but each time I used the sharepoint account to connect to database it threw this error. Your article pointed me to the Application event log and even though I was trying my sharepoint login, the event log was recording the administrator account wasn’t found. Now that’s strange that it made that mapping. My work around was to add administrator to the database users, why? I have no idea. Thanks for the tips

    1. You have 2 users in this scenario: setup account, farm account.
      Setup account: you run the configuration wizard with the setup account, it needs the securityadmin and dbcreator roles on sql server. This ensures it can connect to sql, create databases and add the farm account with the required roles.
      Farm account: you specify this account during the config wizard and all sql permissions will be granted, you don’t have to grant anything for it on sql.
      In your scenario I gues your setup account is administrator (that’s the reason he was in the event log) and it needs these permissions on sql. Your sharepoint account will be added automatically.

    2. Updated: the administrator account was the account I was logged into the sharepoint server with. So I rolled back and logged in as my new sharepoint domain account and administrator no longer needed permissions in the database instance.

  2. Hi kovacsz1, so it did not ask me for the setup account info, only the farm account, therefore it assumed the account that I was logged into the server for the setup. So as I would understand it then the setup account only required the permissions for “Setting up” the database? That seems a little baffling if that’s the case. One would “Assume” that it would setup the database with the farm account.. certainly doesn’t seem to be the case though. Thanks!

    1. Ok, I totally get it now, I assumed that I needed to setup the sharepoint farm user in the database myself granting all the permissions needed and that would be the user that the setup wizard would use to build the sharepoint database. I had it backwards though, the setup user has to have the database permissions in order to setup the database AND setup the sharepoint database user that I specified during setup. And it so happens to be that the setup user is the user that is logged into the server that is running the sharepoint setup and again, is the user that initially needs access to the database where sharepoint will be created.

      This really is a great post for the sharepoint setup nuances. Thanks again

      1. Thanks for the feedback!
        This makes me think to update it with a section about user accounts 🙂

  3. Thanks for the above…but….in the interests of keeping the firewall on..you also need to add a Firewall program exception to ..\MSSQL11.\MSSQL\Binn\sqlservr.exe in addition to opening up those ports.

  4. Thank you! Missed one property in AutoSPInstaller and BAM my sqlserver had an alias to “server\instance” 🙂

  5. Thanks for the article! It took me until #9, but I found my issue. Using AutoSPInstaller, I had created a bad alias entry. Removed the entry and the script is now able to run. I will say that it did take time for this to error out, so #9 is not only for those immediate error responses.

  6. if it is not connecting using server name and instance name try connecting with IP address of the sql server.
    In my case I did all the above steps and there were no luck, but when I changed the server name to Ip address it worked !!

  7. One additional consideration: If you are using SQL 2008 R2, you must install SQL 2008 R2 Service Pack 2 on both DDBB Server and each SharePoint Servers (the server and the native client must have the same version). If not, you got the same error message, allthough they are properly configured all aspects as detailed here.

    This post is excellent, thanks a lot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s