Configure SQL Server

In this section, we'll start by setting up a user account for Rock to use to access the SQL Server. But before you begin creating users, it's important to understand the type of SQL Server environment you're working with:

• Azure SQL Database: A fully managed database service where you interact with the database directly. To create users in Azure SQL Database, you'll need to use actual SQL commands in SSMS. We provide you with those SQL commands after the series of screenshots below.
• SQL Server on Azure VMs: A traditional SQL Server instance running on an Azure Virtual Machine. In the context of creating new users, this is the same as hosting locally. Instead of running SQL commands, you can use the windows and screens pictured below.

Creating a New User Account (Azure VM or Local)

To begin, let's open SQL Server Management Studio. Change the Windows Authentication option to SQL Server Authentication and log in using the password we created during the SQL Server installation. By default, the admin username is sa. Use that username when logging in for the first time.

On the Object Explorer, expand the Security folder and right click on Logins and click on New Login.

Create a login called RockUser (or another username if you prefer), set the Authentication Type to SQL Server authentication, and create a password. Be sure to keep this password secured and only share it with those who truly need it. Also, uncheck the Enforce password policy setting for this account.

Next, click on Server Roles. Check the dbcreator role. Then click OK.

Creating a New User Account (Azure SQL)

Open SQL Server Management Studio and change the Windows Authentication option to SQL Server Authentication. Then, log in with a username of sa and the password we created during the SQL Server installation.

We're going to be running some SQL commands. Don't worry if you're not familiar with SQL, we'll walk you through each step.

To start, expand the Databases folder and then expand the System Databases folder. Right-click the master database and select New Query.

Paste the SQL below into the New Query window. Be sure to update the password, keeping it in single quotes. Do not use special characters for this password.

CREATE LOGIN [RockUser]

WITH PASSWORD = 'xxxxxxx';

CREATE USER [RockUser]

FROM LOGIN [RockUser]

WITH DEFAULT_SCHEMA = dbo;

ALTER ROLE dbmanager ADD member [RockUser]

ALTER ROLE loginmanager ADD member [RockUser]
            

To run the SQL, simply click the Execute button as pictured below.

At this point you would proceed with the SQL Server Configuration Manager setup described in the next section below, followed by the firewall configuration. Then proceed to the Installing Rock chapter, where your database will be built.

After your database is built, come back to SSMS. To access your new database, expand the Databases folder. The new database should be listed below the System Databases and Database Snapshots folders. Just like we did above, right-click the database name and select New Query. Paste the below SQL into the query window and execute it.

CREATE USER [RockUser]

FOR LOGIN [RockUser]

WITH DEFAULT_SCHEMA = dbo;

ALTER ROLE db_owner ADD member [RockUser]
            

You can test the newly-created RockUser login by quitting and restarting SSMS, then entering the new user and password to log back in.

SQL Server Configuration Manager

Now let's open up SQL Server Configuration Manager. Expand the SQL Server Network Configuration item and click on Protocols for MSSQLSERVER. Right click on TCP/IP and click on Enable.

Next, click on SQL Server Services and then right click on SQL Server Agent (MSSQLSERVER) and click on Properties.

Set the start mode to Automatic and click on Apply. Now, since our earlier changes warned us that we'd need to restart the service to take effect, you should right-click the SQL Server service and choose "Restart".

Firewall with Advanced Security

Next, let's open up Firewall with Advanced Security. Right click on Inbound Rules and click New Rule.

Click on Port and click Next.

Choose TCP and type in the specified local port, "1433."

Click on Allow the connection and click Next.

Choose which profiles this rule is applied to and click Next.

Give the rule a name and click Finish.

If you're installing Rock and SQL Server on the same server (see warning below) then you'll probably want to lock down port 1433 so that your database is not exposed to the internet. In that case, open the firewall rule you created above. Under the Scope tab add 127.0.0.1 to both the Local IP address and to the Remote IP address sections. 127.0.0.1 is the local host where SQL Server is installed. This prevents anyone from outside that server from accessing your database. This does not apply to an Azure setup.

That's it! Let's move on to the next chapter.