Automate rotating IAM credentials for SQL Server backups to Amazon S3

In this blog post, we will look at how to automate the rotation of the AWS Identity and Access Management (IAM) credentials synchronized with Microsoft SQL Server credentials to meet password rotation requirements.

Solution overview

The IAM Access key and Secret access key are used to create a SQL Server credential; therefore, to rotate the IAM keys, we will also need to alter the SQL Server credential. There are character restrictions specific to this use case that also need to be checked for. We will accomplish this by using Python scripts within AWS Lambda to rotate the keys and launch an AWS Systems Manager document that will change SQL Server credentials. The document will connect to individual Amazon Elastic Compute Cloud (Amazon EC2) instances to alter the SQL Server credential. This multi-step process will be managed by a Systems Manager Automation document that will be scheduled to execute every seven days. We will store individual credentials in AWS Secrets Manager.

This blog post assumes the architecture, discussed in Backup SQL Server databases to Amazon S3, has already been configured and that AWS Systems Manager is correctly configured in your account.

The diagram in Figure 1 illustrates our solution architecture to rotate SQL credentials:

Architectural Design Diagram

Figure 1. Architectural Design Diagram

Scheduler Triggers
Automation Document Starts
Lambda (RotateCreds) Runs
Secret is returned to Lambda
Key is Rotated
Key is written back to Secret
Lambda (UpdateCreds) Runs
Command Document Start
SQL Credentials are updated

1. Solution walkthrough

1.1. Create secrets in AWS Secrets Manager

We will need to create five secrets for this demonstration. To create these secrets, follow the instructions in the Create an AWS Secrets Manager secret document using the plaintext tab and replacing {“”:””} with the values shown in Figure 2:

Name	Value
bucketurl	sql-backups-2022.s3.us-east-1.amazonaws.com/backups
iamuser	sql-backups-user
sqlcredential	AAA:bbbbbb
sqluser	cred-rotation-user
sqlpass	cred-rotation-user

Figure 2. Table of AWS Secrets Manager secrets

1.2. Create AWS Systems Manager documents

This example assumes Systems Manager is correctly configured in your account and the Amazon EC2 instances are listed under Systems Manager > Fleet manager. For help in setting up Systems Manager, review Setting up AWS Systems Manager.

Next, we will Create the Systems Manager document to connect to each SQL Server and run the ALTER CREDENTIAL command. For this post, we will create a command document named SQL-Credentials-UpdateCreds using the JSON document presented in Figure 3:

{
  "schemaVersion": "2.2",
  "description": "Execute SQL query on customers instance and map output into JSON.",
  "parameters": {
    "executionTimeout": {
      "type": "String",
      "default": "300",
      "description": "(Optional) The time in seconds for a command to complete before it is considered to have failed. Default is 3600 (1 hour). Maximum is 172800 (48 hours).",
      "allowedPattern": "([1-9][0-9]{0,3})|(1[0-9]{1,4})|(2[0-7][0-9]{1,3})|(28[0-7][0-9]{1,2})|(28800)"
    }
  },
  "mainSteps": [
    {
      "precondition": {
        "StringEquals": [
          "platformType",
          "Windows"
        ]
      },
      "action": "aws:runPowerShellScript",
      "name": "runPowerShellScript",
      "inputs": {
        "runCommand": [
          "Clear-Host",
          "",
          "## Version 1.37",
          "",
          "# log file path",
          "$programDataPath = \"$env:ProgramData\"",
          "$Logpath = \"$programDataPath\\\\Amazon\\\\SQL-Cred-Rotate\"",
          "$Logfile = \"$Logpath\\\\SQL-Cred-Rotate.log\"",
          "",
          "# write log file with timestamp",
          "Function Log-Write {",
          "    Param ([string]$logString)",
          "    $timeStamp = Get-Date -Format g",
          "    try {",
          "        Add-content $Logfile -value \"$timeStamp :  $logString\"",
          "    } catch {",
          "        $errorMessage = $_.Exception.Message",
          "        throw \"Error writing log file: $errorMessage\"",
          "    }",
          "}",
          "",
          "# Handle vital error. Print and exit the program",
          "function Print-Error-Exit {",
          "    param(",
          "        $errorMessage",
          "    )",
          "    Log-Write \"Error : $errorMessage Program terminated.\"",
          "    throw \"Error : $errorMessage Program terminated. - Print-Error-Exit()\"",
          "}",
          "",
          "function Get-Sql-Instance-Status {",
          "    $server = $env:computername",
          "",
          "    $object = Get-service -ComputerName $server  | Where-Object {($_.name -like \"MSSQL$*\" -or $_.name -like \"MSSQLSERVER\" -or $_.name -like \"SQL Server (*\") }",
          "",
          "    if ($object) {",
          "        $object = $object | Select-Object Name,Status",
          "    } else {",
          "        Print-Error-Exit \"0 SQL Server instances discovered. Program exit.\"",
          "    }",
          "    return $object",
          "}",
          "",
          "# Check if the sql server is running",
          "function Is-Sql-Running {",
          "    param(",
          "    [string] $SqlServerInstance,",
          "    [object] $SqlStatusMap",
          "    )",
          "    $serverName = \"MSSQLSERVER\"",
          "    if ($SqlServerInstance -ne \"MSSQLSERVER\") {",
          "        $serverName = 'MSSQL$' + $SqlServerInstance",
          "    }",
          "    return $SqlStatusMap | Where-Object {($_.name -eq $serverName)}",
          "}",
          "",
          "# Check the SQL Cluster State",
          "function SQL-Cluster-Check {",
          "",
          "    [string[]]$ClusterInfo = @()",
          "    [string[]]$PortInfo = @()",
          "    [string[]]$InstanceInfo = @()",
          "    [string[]]$RegInfo = @()",
          "",
          "    $inst = (Get-ItemProperty 'HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server').InstalledInstances",
          "",
          "    foreach ($i in $inst) {",
          "",
          "        [string[]]$instname = ''",
          "        $instname = (Get-ItemProperty 'HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\Instance Names\\\\SQL').$i",
          "",
          "        $RegInfo += $instname",
          "",
          "        $InstanceInfo += $i",
          "",
          "        if ((Get-ItemProperty \"HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instname\\\\MSSQLServer\\\\SuperSocketNetLib\\\\Tcp\\\\IPAll\").TcpPort -ne '') {",
          "            $PortInfo += (Get-ItemProperty \"HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instname\\\\MSSQLServer\\\\SuperSocketNetLib\\\\Tcp\\\\IPAll\").TcpPort",
          "        }",
          "        else {",
          "            $PortInfo += (Get-ItemProperty \"HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instname\\\\MSSQLServer\\\\SuperSocketNetLib\\\\Tcp\\\\IPAll\").TcpDynamicPorts",
          "        }",
          "",
          "        if (Test-Path -Path \"HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instname\\\\Cluster\") {",
          "            $ClusterInfo += (Get-ItemProperty \"HKLM:\\\\SOFTWARE\\\\Microsoft\\\\Microsoft SQL Server\\\\$instname\\\\Cluster\").ClusterName",
          "        }",
          "        else {",
          "            $ClusterInfo += '-'",
          "        }",
          "    }",
          "    [array[]]$SqlClusterInfo = $InstanceInfo, $RegInfo, $PortInfo, $ClusterInfo",
          "",
          "    return $SqlClusterInfo",
          "}",
          "",
          "",
          "# Main function",
          "function Main {",
          "    Try {",
          "",
          "        # Check if the log file directory exists",
          "        if (-Not (Test-Path $Logfile)) {",
          "            New-Item -ItemType \"directory\" -Path \"$Logpath\" | Out-Null",
          "            New-Item -ItemType \"file\" -Path $Logfile | Out-Null",
          "        }",
          "        # Check if the log file is too large. Remove if it is too large",
          "        if ((Get-Item $Logfile).Length -gt 5kb) {",
          "            Remove-Item $Logfile",
          "        }",
          "",
          "        # Check if the registry key exists",
          "        if (Test-Path 'HKLM:\\\\Software\\\\Microsoft\\\\Microsoft SQL Server') {",
          "            # Get all the SQL Server instances on the EC2 instance",
          "            $SqlServerInstances = (Get-ItemProperty 'HKLM:\\\\Software\\\\Microsoft\\\\Microsoft SQL Server') | Select-Object -ExpandProperty InstalledInstances",
          "        } else {",
          "            Print-Error-Exit \"SQL is not installed. Registry key can not be found\"",
          "        }",
          "",
          "        $server = $env:computername",
          "        $SqlClusterInfo = SQL-Cluster-Check",
          "        $SqlStatusMap = Get-Sql-Instance-Status",
          "",
          "        $sqluser = Get-SECSecretValue -SecretId sqluser -Select SecretString",
          "        $sqlpass = Get-SECSecretValue -SecretId sqlpass -Select SecretString",
          "        $bucketurl = Get-SECSecretValue -SecretId bucketurl -Select SecretString",
          "        $sqlcredential = Get-SECSecretValue -SecretId sqlcredential -Select SecretString",
          "",
          "        $sqlquery = \"DECLARE @tsql NVARCHAR(4000);",
          "",
          "        IF CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '16%'",
          "            BEGIN",
          "                BEGIN TRY",
          "                    BEGIN TRANSACTION",
          "                        IF exists (select * from sys.credentials where name = 's3://$bucketurl')",
          "                        BEGIN",
          "                            SET @tsql = 'ALTER CREDENTIAL [s3://$bucketurl] WITH IDENTITY = '+''''+'S3 Access Key'+''''+', SECRET = '+''''+'\"+$sqlcredential+\"'+''''+';' ;",
          "                            EXEC(@tsql)",
          "                        END",
          "                    ELSE",
          "                        BEGIN",
          "                            SET @tsql = 'CREATE CREDENTIAL [s3://$bucketurl] WITH IDENTITY = '+''''+'S3 Access Key'+''''+', SECRET = '+''''+'\"+$sqlcredential+\"'+''''+';' ;",
          "                            EXEC(@tsql);",
          "                        END",
          "                    COMMIT TRAN",
          "                END TRY",
          "                BEGIN CATCH",
          "                    IF @@TRANCOUNT > 0",
          "                        ROLLBACK TRAN ;",
          "",
          "                    DECLARE @ErrorMessage NVARCHAR(4000);",
          "                    DECLARE @ErrorSeverity INT;",
          "                    DECLARE @ErrorState INT;",
          "",
          "                    SELECT",
          "                        @ErrorMessage = ERROR_MESSAGE(),",
          "                        @ErrorSeverity = ERROR_SEVERITY(),",
          "                        @ErrorState = ERROR_STATE();",
          "                    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);",
          "                END CATCH",
          "            END\"",
          "",
          "        foreach ($SqlServerInstance in $SqlServerInstances) {",
          "            $item = Is-Sql-Running $SqlServerInstance $SqlStatusMap",
          "",
          "            $i = 0",
          "            foreach ($value in $SqlClusterInfo[0]) {",
          "                if ($SqlClusterInfo[2][$i] -eq 0) { $portString = \"\" } else { if ($SqlClusterInfo[2][$i] -eq \"\") { $portString = \"\" } else { $portString = ',' + $SqlClusterInfo[2][$i] } }",
          "",
          "                if ($value -eq $SqlServerInstance) {",
          "                   if ($SqlClusterInfo[3][$i] -eq '-') {",
          "                       $SqlServer = 'localhost\\\\' +  $SqlClusterInfo[0][$i] + $portString",
          "                   }",
          "                   else {",
          "                       if ($SqlClusterInfo[0][$i] -ne '') {",
          "                           $SqlServer = $SqlClusterInfo[3][$i] + '\\\\' + $SqlClusterInfo[0][$i] + $portString",
          "                    }",
          "                    else {",
          "                       $SqlServer = $SqlClusterInfo[3][$i] + $portString",
          "                    }",
          "                   }",
          "                }",
          "                $i ++",
          "            }",
          "",
          "            if (($item.Status -eq \"Running\")) {",
          "                try{",
          "                    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection",
          "                    $SqlConnection.ConnectionString = \"Server = $SqlServer; Database = master; uid=$sqluser; pwd=$sqlpass;  Integrated Security = False; Connection Timeout = 30;\"",
          "                    $SqlConnection.Open()",
          "",
          "                    $Command = New-Object System.Data.SQLClient.SQLCommand",
          "                    $Command.Connection = $SqlConnection",
          "                    $Command.CommandText = $sqlquery",
          "                    $command_output = $Command.ExecuteNonQuery();",
          "                } catch {",
          "                    Print-Error-Exit $_.Exception.Message",
          "                } finally {",
          "                    $SqlConnection.Close()",
          "                    Log-Write \"Cred rotated on $server : $SqlServer\"",
          "                }",
          "            }",
          "        }",
          "    }",
          "    catch {",
          "        # All the possible exceptions will be caught here",
          "        Print-Error-Exit $_.Exception.Message",
          "    }",
          "}",
          "",
          "main",
          "" 
        ],
        "workingDirectory": "",
        "timeoutSeconds": "60"
      }
    }
  ]
}

Figure 3. SQL-Credentials-UpdateCreds json

1.3. Create IAM role

We will first need to create an IAM role to attach to the Lambda functions. For more information, refer to AWS Lambda execution role. We will first create a role named SQL-Credentials-Lambda-role and attach the policy and trust relationship.

a. Create the policy named SQL-Credentials-Lambda-policy using the script presented in Figure 4:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "ec2:DescribeInstances",
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "ssm:SendCommand",
                "iam:DeleteAccessKey",
                "secretsmanager:GetSecretValue",
                "iam:CreateAccessKey",
                "iam:ListAccessKeys",
                "secretsmanager:UpdateSecret"
            ],
            "Resource": [
                "arn:aws:iam::102228307451:user/sql-backups-user",
                "arn:aws:ec2:*:102228307451:instance/*",
                "arn:aws:ssm:*:102228307451:document/SQL-Credentials-UpdateCreds",
                "arn:aws:secretsmanager:*:102228307451:secret:*"
            ]
        }
    ]
}

Figure 4. SQL-Credentials-Lambda-policy json

b. The trust relationship will be pre-configured during creation. Make sure it matches the JSON in Figure 5 and manually update if needed.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "lambda.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Figure 5. trust relationship json

1.4. Create AWS Lambda function: SQL-Credentials-UpdateCreds

We will now create the Lambda function that will use these elements. In our example, we will create a function named SQL-Credentials-UpdateCreds with the following configuration:

a. Use runtime Python 3.11.

b. Change the default execution role to use an existing role created in step 1.3, SQL-Credentials-Lambda-role.

c. After you create the function, edit the lambda_function by pasting into the code section the code presented in Figure 6, and click Deploy.

import boto3

def lambda_handler(event, context):
    ssm = boto3.client("ssm")
    ec2 = boto3.resource(service_name="ec2")

    Instances = ec2.instances.filter(
      Filters=[
        {
          'Name': 'instance-state-name',
          'Values': [
            'running'
          ]
        }
      ]
    )
    
    for Instance in Instances:
      InstanceId = Instance.id
      for tags in Instance.tags:
        if tags["Key"] == 'SQL' and tags["Value"] == '2022':
          response = ssm.send_command(
            InstanceIds=[InstanceId],
            DocumentName="SQL-Credentials-UpdateCreds", )

Figure 6. SQL-Credentials-Lambda-role script

d. On the Configuration tab, choose Edit and adjust the Timeout value to 15 minutes.

e. This function will execute the System Manager document SQL-Credentials-UpdateCreds against any Amazon EC2 instances tagged with SQL:2022. To add tags to your environment, follow this guidance on tagging your Amazon EC2 resources.

1.5. Create AWS Lambda function: SQL-Credentials-Rotate

We also need a second function named SQL-Credentials-RotateCreds using the same configuration as above and the code presented in Figure 7, and click Deploy.

import boto3
def lambda_handler(event, context):
    iam = boto3.client('iam')
    ssm = boto3.client("ssm")
    sm =  boto3.client("secretsmanager")
    
    get_secret_value_response = sm.get_secret_value(SecretId='iamuser')
    
    iamuser = get_secret_value_response['SecretString']
    Keys = iam.list_access_keys(UserName=iamuser)

    c = 0
    for keyValue in Keys['AccessKeyMetadata']:
        c+=1

    if c > 1:
        if Keys['AccessKeyMetadata'][0]['CreateDate'] > Keys['AccessKeyMetadata'][1]['CreateDate']:
            response = iam.delete_access_key(UserName=iamuser,AccessKeyId=Keys['AccessKeyMetadata'][1]['AccessKeyId'])
        else:
            response = iam.delete_access_key(UserName=iamuser,AccessKeyId=Keys['AccessKeyMetadata'][0]['AccessKeyId'])
            
    NewKey = iam.create_access_key(UserName=iamuser)

    KeyValidated = False

    while KeyValidated == False:
        if ':' in NewKey['AccessKey']['AccessKeyId'] or ':' in NewKey['AccessKey']['SecretAccessKey']:
            response = iam.delete_access_key(UserName=iamuser,AccessKeyId=NewKey['AccessKey']['AccessKeyId'])
            NewKey = iam.create_access_key(UserName=iamuser)
        else:
            KeyValidated = True
    sqlcredential = NewKey['AccessKey']['AccessKeyId']+':'+NewKey['AccessKey']['SecretAccessKey']

    response = sm.update_secret(SecretId='sqlcredential', SecretString=sqlcredential)

Figure 7. SQL-Credentials-RotateCreds script

a. On the Configuration tab, choose Edit and adjust the Timeout value to 15 minutes.

b. This function will rotate the IAM credentials for the user sql-backups-user and update the AWS Secrets Manager secret sqlcredential with the concatenated key needed for the SQL Server credential configuration.

1.6. Create an IAM Automation role: AmazonSSMAutomationRole

We will need to create an IAM Automation role named AmazonSSMAutomationRole that will be used with the automation document, as described in Use IAM to configure roles for Automation. Along with the predefined policy AmazonSSMAutomationRole, we will also add an inline policy named SSM-lambda-InvokeFunction using the JSON presented in Figure 8.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "lambda:InvokeFunction",
            "Resource": "*"
        }
    ]
}

Figure 8. SSM-lambda-InvokeFunction script

1.7. Create an AWS Systems Manager automation document

We will also create a Systems Manager automation document:

a. The YAML code presented in Figure 9 will configure the Systems Manager Automation document to be named SQL-Credentials-Automation. Note the highlighted account number 000000000000; it will need to be edited for your account.

description: |
  *SSM Automation document for SQL-Credentials-Rotation.* 
schemaVersion: '0.3'
assumeRole: 'arn:aws:iam::000000000000:role/AmazonSSMAutomationRole'
mainSteps:
  - name: Step1_RotateCreds
    action: 'aws:invokeLambdaFunction'
    inputs:
      InvocationType: RequestResponse
      FunctionName: SQL-Credentials-RotateCreds
  - name: Step2_UpdateCreds
    action: 'aws:invokeLambdaFunction'
    inputs:
      InvocationType: RequestResponse
      FunctionName: SQL-Credentials-UpdateCreds

Figure 9. SQL-Credentials-Automation script

b. This automation document will be scheduled to run every 30 days. Follow the guidance in Using AWS Lambda with Amazon EventBridge to create the schedule.

c. This automation document has two steps:

i. Step one will execute the Lambda function SQL-Credentials-RotateCreds to rotate the IAM credentials.

ii. Step two will execute the Lambda function SQL-Credentials-UpdateCreds to identify any Amazon EC2 instances running with a tag key of SQL and a value of 2022. It will then execute the Systems Manager document SQL-Credentials-UpdateCreds against each server to identify any SQL Server instances running on the server. Finally, it will alter the SQL Server credentials on each SQL Server instance using the value stored in Secrets Manager secret sqlcredential.

1.8 Create SQL Server login

Connect to a SQL Server database engine using SQL Server Management Studio (SSMS) and create the SQL Server login using the T-SQL presented in Figure 10. Ensure SQL authentication is set to Mixed Mode.

USE master;  
CREATE LOGIN [cred-rotation-user] WITH PASSWORD=N'cred-rotation-user', DEFAULT_DATABASE=[master], CHECK_POLICY=OFF
GO
GRANT CONTROL SERVER TO [cred-rotation-user];  
GO

Figure 10. SQL Server login script

1.9. Tag Amazon EC2 resources

We will use a tag filter for this example. We will need to add a tag key SQL and tag value 2022 to the Amazon EC2 instance. For additional guidance, review tagging your AWS resources.

2. Testing solution

2.1. To manually rotate the IAM keys and the SQL Server credential, follow these steps:

a. Open AWS Systems Manager in the AWS Management Console.

b. Select Shared Resources > Documents.

c. Select Owned by me and select SQL-Credentials-Automation.

d. Choose Execute automation, take the default option Simple execution, and click Execute.

e. The Status column will show success for both steps (Figure 11).

Automation document output showing success

Figure 11. Automation document output showing success

f. Open SQL Server Management Studio (SSMS) on the SQL Server 2022 instance and run the T-SQL command presented in Figure 12. Update the Amazon S3 location based on your environment.

BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Figure 12. T-SQL backup command

g. The expected result will be a “BACKUP DATABASE successfully processed” message.

h. The value of the Secrets Manager Secret sqlcredential will also change to reflect the new Access Key.

3. Cleanup resources

When you’re finished using the AWS resources in this post, delete the resources to avoid incurring unwanted charges.
a. Delete the AWS Systems Manager documents

b. On the Lambda console, choose Functions in the navigation pane.

c. Select the function you created and on the Actions menu, choose Delete.

d. Choose Delete again to confirm.

e. Delete the EventBridge rule.

f. Delete the AWS Secrets Manager secrets.

g. Remove the SQL Server login:

i. DROP LOGIN [cred-rotation-user];

h. Delete the IAM policies.

i. Delete the IAM roles.

4. Conclusion

Security is a critical part of all workflows and this post showed how requirements like credential rotation can be accomplished.

We looked at how to automate the rotation of the IAM credentials used by SQL Server in writing native SQL Server backups to Amazon S3. This was accomplished through a combination of Lambda functions, AWS Systems Manager documents, and a Systems Manager Automation document to manage the workflow of each.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

Microsoft Workloads on AWS