Day 87: Deploying SQL Server 2012 using Chef – Part 2

In last week’s post, Day 82: Deploying SQL Server 2012 using Chef – Part 1, we demonstrated how to deploy SQL Server 2012 Standard using Chef. In that post, you may be have noticed that we used variables within the recipe to hold our SQL Credentials in Clear Text. While this is acceptable for testing purposes, it is definitely not best practice within a Production Environment. This week, we will be demonstrating how to Encrypt and Store those credentials in Chef as Data Bag Items. Additionally, we will be showing a modified version of last week’s recipe that will use the Data Bag Items created in this post.

So what is a Data Bag? Per Chef’s Documentation, “A data bag is a global variable that is stored as JSON data and is accessible from a Chef server. A data bag is indexed for searching and can be loaded by a recipe or accessed during a search.” Additionally, Data Bags can be created on a Host running the Chef Client 11.8.0 and up and will run under Chef-Zeroa small and fast in-memory version of Chef Server that runs locally allowing you to test your Data Bag Items in your recipes before pushing them out to your Chef Server.

There are a variety of different tools (which you can read about here) that you can use to store encrypted data in Chef, in this particular instance we are going to use the native Encryption available in Chef Data Bags.

 

Prerequisites

Download and install notepad++ onto your Windows Host.

Since we are going to be generating Data Bags from PowerShell on Windows, we need to update the knife editor settings on the Windows Host.

Add the following line to the knife.rb file in C:\Users\<username>\.chef if you are using notepad++

 

If you have a different text editor you prefer, you can add them by following the  Chef Documentation.

 

 

Generate a Shared Secret File

You have the option of either generating Encrypted Data Bags in Chef using either a shared secret password or a shared secret file. Since we do not want our passwords to be in clear text in our recipe, we are going to generate a shared secret file on our Ubuntu Workstation and then copy it over to our Windows Host.

From your Ubuntu Chef Workstation, run the following command to generate a random number and store it within a file

 

Next, copy the file over to your Windows Server 2012 R2 Host using WinSCP. Place the encrypted_sql2012_data_bag_secret file into C:\Users\<USERNAME>\.chef\

 

 

Creating Data Bags

You have the option of creating Data Bags locally or creating them on your Chef Server. First we are going to create our Data Bags locally on the Windows Host. Once the Recipe has been edited, we will be adding the Data Bags onto the Chef Server.

When creating the Data Bags locally on the Windows Host, they will be stored in C:\Users\<USERNAME>\chef-repo\data_bags

The basic syntax of creating a Data Bag locally is shown below:

 

How this is created in Windows locally is shown in the Syntax below:

 

 

Create Data Bag Item for SQL Account Usernames

From your Windows Host, open up a PowerShell prompt and cd to C:\Users\<USERNAME>\chef-repo and run the following command:

 

A text file will open up in notepad++ and will contain the following:

 

Modify the text file to match the text below but replace the <USERNAME> fields with the local or domain account you wish to use. If you use a domain account, make sure to use two backslashes and not one, i.e. – DOMAIN\\USERNAME. Once you are done making changes, save and close the file.

 

You should see the following response in your PowerShell Window:

Additionally, you should see a file named sql_service_account_usernames.json located in C:\Users\<USERNAME>\chef-repo\data_bags\SQL2012

 

 

Create Encrypted Data Bag Item for SQL Account Passwords

Next, we will create the Encrypted Data Bag Item to store the SQL Account Passwords.

From your Windows Host, open up a PowerShell prompt and cd to C:\Users\<USERNAME>\chef-repo and run the following command:

 

A text file will open up in notepad++ and will contain the following:

 

Modify the text file to match the text below but replace the <PASSWORD> field with the respective password of the accounts we created earlier. The same rules with special characters that applied to creating the usernames above apply here as well. Once you are done making changes, save and close the file.

 

You should see the following response in your PowerShell Window:

Additionally, you should see a file named sql_service_account_passwords.json located in C:\Users\<USERNAME>\chef-repo\data_bags\SQL2012

 

 

Update the SQL Server 2012 Recipe

Below is an updated version of the SQL Server 2012 recipe used in last week’s post.

 

The Recipe above will perform the following actions:

– Create a Temp Directory at C:\Temp on the Windows Host
– Download the SQL Server 2012 ISO from a specified web URL.
– Mount the SQL Server 2012 ISO
– Install SQL Server 2012 with Default Settings
– Dismount the SQL Server 2012 ISO
– Delete the SQL Server 2012 ISO from the Temp Directory at C:\Temp

Before you run the chef recipe (as shown below) make sure to replace the following variables at the top of the recipe with their respective values:

 

 

Run the Recipe in Local Mode

Once you have put in the respective variable values you can deploy the recipe locally on the Windows Host by running the chef-client command below:

 

Note that the recipe will take between 5 and 10 minutes to run.

Once the command has finished running, you should be able to open up services.msc and see the SQL Server (MSSQLSERVER) Service Running.

 

Conclusion

Next week we will go over how to create the Data Bags on the Chef Server and then upload the SQL Server 2012 Recipe to the Chef Server for distribution. Additionally, we will look at extending the functionality of the recipe to look like something for a Production Environment.

 

Previous Installments

100 Days of DevOps with PowerShell