Tag Archives: Chef

Automating SQL Server With Chef

Chef has really made great strides recently to enhance the support and capabilities of managing Windows hosts. I’ve recently been working on automation of SQL Server with Chef, and wanted to share details in this post on how you can run TSQL against a SQL Server instance.

There are a couple of ways to run TSQL against a server. One is to use the database cookbook that makes a few Lightweight Resource Providers (LWRP) available to manage databases, and one of those is for SQL Server. The other way is to use either the execute resource that calls sqlcmd.exe or the powershell_script resource and use the invoke-sqlcmd Powershell cmdlet. The thing to keep in mind is that the database LWRP doesn’t play well with TSQL with multiple batches (using the GO keyword), so if this is the case in your situation you’re probably better off with the execute or powershell_script resources. Just a decision point to keep in mind.

So let’s cover the execute resource method of running a TSQL script. In this case it’s a database maintenance script (based on the awesome Ola Hallengren scripts) that set up some standard maintenance activities. This is a script that I drop on the server with a Chef template resource. In this case there’s a couple variables that come in to play to set some values in the script itself.

template 'C:\DBScripts\DatabaseMaintenance.sql' do
  source 'DatabaseMaintenance.sql.erb'
  variables(
    :backup_path => 'C:\db_backups'
    :retention_hours => '168'
  )
end

Now I want to actually execute the script. First, I make sure that the sqlps module is imported. This is needed in the guard for the execute resource and ensures that the invoke-sqlcmd cmdlet is present on the system. In my specific case it was much easier to get the return values from the invoke-sqlcmd command than trying to get it through sqlcmd.exe.

powershell_script 'sqlps module' do
  code 'Import-Module "sqlps" -DisableNameChecking'
end

Next comes the actual execute resource. I wasn’t able to get the full functionality of the script working when using the powershell_script resource in my case so I had to mix the execute resource with the powershell_script guard. I’ll be circling back to see if I can get it working fully in the powershell_script resource so that it’s a bit cleaner, but this works for now. Here I run the script through sqlcmd.exe, but not_if the number of jobs that start with DBMaint (a naming standard I follow) is not equal to 11. If there are 11 jobs we know the jobs are there and we’re good. If there aren’t 11 jobs, we need to run the script. The code in the not_if guard will either return true or false depending on the results of the query. This brings a bit of idempotence to the setup of these jobs, ensuring that we only do something when something actually needs to be done. It’s not perfect, but is a good starting point.

execute 'setup db-maint jobs' do
  command "sqlcmd -S localhost -i \"C:\\DBScripts\\DatabaseMaintenance.sql\""
  guard_interpreter :powershell_script
  not_if "(invoke-sqlcmd -ServerInstance \"localhost\" -Query \"select count(*) from msdb.dbo.sysjobs where name like 'DBMaint%'\").Column1 -eq 11"
end

Putting it all together in one recipe:

template 'C:\DBScripts\DatabaseMaintenance.sql' do
  source 'DatabaseMaintenance.sql.erb'
  variables(
    :backup_path => 'C:\db_backups'
    :retention_hours => '168'
  )
end

powershell_script 'sqlps module' do
  code 'Import-Module "sqlps" -DisableNameChecking'
end

execute 'setup db-maint jobs' do
  command "sqlcmd -S localhost -i \"C:\\DBScripts\\DatabaseMaintenance.sql\""
  guard_interpreter :powershell_script
  not_if "(invoke-sqlcmd -ServerInstance \"localhost\" -Query \"select count(*) from msdb.dbo.sysjobs where name like 'DBMaint%'\").Column1 -eq 11"
end

This is a quick and easy way to execute TSQL against an instance using standard utilities (or cmdlets) that we use outside of Chef, yet still allows for idempotence so that we only do something when something needs to be done, and ensuring that that server is in the same desired state after every chef-client run.

Advertisements

AWS KMS Encryption/Decryption Script

As a follow up to my blog post on Keeping Secrets in Chef with AWS Key Managment Service I wanted to post an updated script that can be used to encrypt/decrypt sensitive information. I’ve updated the following script to allow for a few parameters. Specifically:

-e --encrypt STRING (encrypt the specified string)
-d --decrypt STRING (decrypt the specified string)
-k --key KEY (full ARN or Key ID to be used to encrypt/decrypt)
-r --region REGION (region the key is located in)

You can find the script in my GitHub repo here, feel free to use it to encrypt/decrypt your sensitive information.

Keeping Secrets in Chef with AWS Key Management Service

Handling sensitive data in Chef can be a bit of a challenge. You can use encrypted data bags, but that can be trickty if you want a new node to access an existing encrypted data bag as you have to re-encrypt the data bag after the node has been bootstrapped. You can also use an external method of encryption/decryption (OpenSSL), but have to handle security around the keys themselves. Enter AWS Key Management Service. KMS is a service that you can use to store keys for encryption/decryption in AWS (EBS volume encryption, for instance) and can also be used as a sort of “Encryption as a Service”. I’ll show you how to do encryption of Chef secrets using KMS and a little Ruby. This works best if you’re Cheffing servers that will be running within AWS (as you can use IAM roles to provide greater security) but is not exclusive to servers running in AWS, you can use it anywhere. Here’s how it works.

At a high level we’ll do the following:

  1. Create a new KMS key
  2. Run Ruby script to encrypt some text
  3. Create a new server in EC2 with an IAM role allowing access to the KMS key
  4. Use the encrypted string in a Chef recipe

First off you’ll need a KMS key. As with many things in AWS there is a cost associated with this service. It’s $1.00 per key per month, plus some small charges for key usage. Check out the KMS pricing page for more details. KMS can be found as an option within IAM, it doesn’t have it’s own entry on the master AWS services list.

AWS Security & Identity

Click on Identity and Access Management and on the right side you’ll see an Encryption Keys link, click that and you’ll be taken to your KMS keys. There may already be a few there for usage of services within AWS, so leave those alone. You’ll need to create a new key for encryption/decryption within Chef. Note that once a key is created, it cannot be deleted, only disabled. You aren’t charged for disabled keys or for keys used created and used by AWS services themselves. Click the Create Key button to create a new key. This will take you through a few screens. Give it a name and a description, grant permissions to users/roles to administer the key, grant permissions to users/roles to use the key, and then create your new key. If you forget to add a user/role to either of those screens, you can always change that after key creation. Click on the key name after creation and you can see details on the key, and modify its options if you need.

So now you have a key, great! But how do we use it? Let’s take a look at some Ruby that can be used to encrypt/decrypt within KMS:

require 'aws-sdk-core'

key_id = 'arn:aws:kms:us-east-1:012345678901:key/01abc2d3-4e56-78f9-g01h-23ij45klm6n6'
kms = Aws::KMS::Client.new(region:'us-east-1')

# Get text from user
puts "Please enter the text you want to encrypt"
text = gets.chomp

# Encrypt entered text
encrypted = kms.encrypt({
key_id: key_id,
plaintext: text
})

# Display raw encrypted text
puts "Encrypted text raw:"
puts encrypted.ciphertext_blob
puts

# Display Base64 encoded text
puts "Encrypted text Base64 encoded:"
puts Base64.encode64(encrypted.ciphertext_blob)
puts

# Display Base64 strict encoded text
puts "Encrypted text Base64 strict encoded:"
puts Base64.strict_encode64(encrypted.ciphertext_blob)
puts

# Decrypt the encrypted text
puts "Now lets decrypt that"
decrypted = kms.decrypt({
ciphertext_blob: encrypted.ciphertext_blob
})

# Display the decrypted text
puts "Here's the decrypted text:"
puts decrypted.plaintext

Open your editor of choice and paste this code in. Replace the key_id with the full ARN of the key you want to use, and ensure the region in the kms variable is set properly as well. This also assumes that you have your AWS keys set up locally with an account that has access to the KMS key for access through the API. Save this file as kms-test.rb and run it from the command line with the command ruby kms-test.rb. You should see something like the following:
kms output

Note the difference between the raw output, the Base64 and Base64 strict output. The Base64 strict encoded text is what we’ll want to use in Chef since it’s one long string with no carriage returns. This allows for easy storage in a Chef attribute, handy if we want to store a password for an account for example.

So how do we use this in Chef? Let’s say you have a recipe that you want to decrypt the password for use by a command.

First, you’re going to need an IAM role associated with your node. When you build a new node in AWS, ensure that it has the following IAM permissions (replace the ARN with the ARN of your KMS key):

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": "principal",
    "Action": [
    "kms:Encrypt",
    "kms:Decrypt",
    "kms:ReEncrypt",
    "kms:GenerateDataKey*",
    "kms:DescribeKey"
    ],
    "Resource": "arn:aws:kms:us-east-1:012345678901:key/01abc2d3-4e56-78f9-g01h-23ij45klm6n6" 
  }
}

Now, if you store the Base64 strict encoded string (which you can get via the kms-test.rb script) in a node['my-cookbook']['password'] attribute, you can use the following Ruby in your recipe to access it and decrypt it from your new node.

pw = node['my-cookbook']['password']
key_id = node['my-cookbook']['kms_key']
kms = Aws::KMS::Client.new(region:'us-east-1')
pw = kms.decrypt({
     ciphertext_blob: Base64.strict_decode64(node['my-cookbook']['password'])
     }).plaintext

execute 'use-password' do
command "command that uses #{pw}"
sensitive true
end

This can be used without an IAM role (for instance if you want to use it outside of AWS) by updating the KMS object in the recipe with the following:

kms = Aws::KMS::Client.new(
        region:'us-east-1',
        access_key_id: node['my-cookbook']['aws_access_key_id'],
        secret_access_key: node['my-cookbook']['aws_secret_access_key']
        )

That said, storing keys locally on any system is much less secure than using IAM roles, so make sure you understand the risks and implications in doing so.

Using these techniques, you can easily store and retrieve sensitive data via KMS for use in Chef and whip up some more awesome!