Umbraco ships with a user interface for connecting to your local SQL but sometimes you scratch your head wondering why you can't seem to connect at all to the database, even though the database user and the setup seems correct.
You may reach this interface when you first built your project and are visiting your local site's url.
To setup a local SQL database for your Umbraco project make sure you select the Customize button when filling in your user account on the creation page.
Here are some tips to follow:
Checklist
1. Did you start with creating the database?
Umbraco is magical, but not in every essence. You need to create a "placeholder database" in SQL Management Studio for connecting locally. Don't forget to give the user login to the database priviliges of the db_owner membership!
2. Have you enabled the "SQL Server and Windows Authentication" setting?
Right click on your server in SQL Management Studio and choose properties. Navigate to the Security tab and make sure you enable the radio button to the SQL Server and Windows Authentication. This is necessary if you have created a custom SQL login user for the database verification! Check action point number 3 below if you need to configure this.
3. Are your SQL instances properly running?
Make sure your Windows Services are running as they should. Navigate to services.msc. Check for SQL Server (SQLEXPRESS) and SQL Server Browser are running. If not start/restart if able. Some configurations require these services to be restarted when fiddling with SQL Management Studio settings.
4.Make sure your custom SQL db user has access
When creating the user responsible for logging onto the Umbraco database make sure you've mapped the correct settings for your database and with user role db_owner and is in the default schema of dbo.
5. "Umbraco cannot start. A connection string is configured but Umbraco cannot connect to the database."
When receiving this, your connectionString tag in your web.config surely is misspelled/misconfigured somewhere. Check for typos!
6."Login failed for 'database user'"
This is most likely because of action point number 2 and 3. You may also have set your user login password wrongly.
7. Your web.config doesn't pick up the configurations on the installation page
Make sure your web.config's connectionString section looks like this below:
<remove name="umbracoDbDSN" />
<add name="umbracoDbDSN" connectionString="server=127.0.0.1\SQLEXPRESS;database=yourDbName;user id=yourUserName;password='yourUserPassword'" providerName="System.Data.SqlClient" />
Where \SQLEXPRESS is your instance name when you installed SQL Local.
Make sure you are removing any default Umbraco shipment connectionstrings with the remove attribute before adding your custom as well.