SQL PERFORMANCE MONITOR – PART 4: AWS BUCKET & IAM USER SET UP

AWS CONFIGURE S3 BUCKETS

[1] CREATE BUCKET on each AWS account where files will be copied/written/stored.

BUCKET NAME:  company-performance-monitor

[2] EDIT OWNERSHIP: Select BUCKET OWNER PREFERRED with ACLs ENABLED

[3] EDIT BUCKET POLICY on the DESTINATION BUCKET at main (headquarter) Company A.

ADD the other AWS ACCOUNT IAM Users that will have permission.

Complete creating the AWS IAM USER steps in all the other AWS accounts for Company B and C (shown in a section below), then come back and apply those users to this section.

{
    "Version": "2012-10-17",
    "Id": "Policy1611277539797",
    "Statement": [
        {
            "Sid": "Stmt1611277535086a",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::2222222222:user/company-b-service-user"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::company-a-performance-monitor/*",
            "Condition": {
                "StringEquals": {
                    "s3:x-amz-acl": "bucket-owner-full-control"
                }
            }
        },
        {
            "Sid": "Stmt1611277877767a",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::2222222222:user/company-b-service-user"
            },
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::company-a-performance-monitor"
        },
        {
            "Sid": "Stmt1611277535086b",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::3333333333:user/company-c-service-user"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::company-a-performance-monitor/*",
            "Condition": {
                "StringEquals": {
                    "s3:x-amz-acl": "bucket-owner-full-control"
                }
            }
        },
        {
            "Sid": "Stmt1611277877767b",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::3333333333:user/company-c-service-user"
            },
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::company-a-performance-monitor"
        }
    ]
}

This will define the AWS ACCOUNT NUMBER from the OTHER sub company(s) and its IAM user(s). This allows access for the OTHER account(s) to PUT objects in the bucket(s).

Your bucket should have a subfolder: PerfCounter with three (3) more sub folders: FileArchive, FileDrop, and FileError.  It may look like this:

Remember to add a Lifecycle Configuration to your bucket (Management tab) to clean up old files in the archive.

AWS CONFIGURE IAM USERS AND POLICY PERMISSIONS

[1] CREATE AN SSIS POLICY with the permissions similar to these, using your own bucket names.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "PerfCounter20220801",
            "Effect": "Allow",
            "Action": [
                "s3:*",
                "s3-object-lambda:*"
            ],
            "Resource": [
                "arn:aws:s3:::MYCOMPANY-database/ssis/*",
                "arn:aws:s3::: MYCOMPANY-performance-monitor/*"
            ]
        },
        {
            "Sid": " PerfCounter20220802",
            "Action": [
                "s3:ListBucket",
                "s3:GetObject",
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3::: MYCOMPANY-database",
                "arn:aws:s3::: MYCOMPANY-database/*",
                "arn:aws:s3::: MYCOMPANY-performance-monitor",
                "arn:aws:s3::: MYCOMPANY-performance-monitor/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*"
        }
    ]
}

This policy allows the IAM user in your account, control on the bucket in same account.

[2] CREATE AN IAM USER and ATTACH the policy.

Be sure to create a SERVICE ACCOUNT type IAM USER without a password, and one that uses an ACCESS KEY to authenticate.

ALTERNATIVELY, you can create a much more liberal, less restrictive policy on the SOURCE bucket for IAM users, by using AWS MANAGED POLICIES, as in this example:

NOTE 1:  that this IAM user has been granted permission on RDS and to our custom KMS Encryption keys. If you are not using the default AWS Keys, you will need to configure additional permissions to use your custom keys. 

AWS KMS CUSTOMER MANAGED KEY CONFIGURATION

Remember to add access to additional ACCOUNT IAM USERS on any CUSTOM KMS KEYS that encrypt your buckets.

{
            "Sid": "AllowProductionAccess",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::111222333444:user/MYCOMPANY-service-user"
            },
            "Action": [
                "kms:Decrypt",
                "kms:GenerateDataKey"
            ],
            "Resource": "*"
        }

WIRE AWS IAM USER to MS AD SERVICE USER for CLI and PowerShell USE

In order for PowerShell to copy files from the local EC2 server up to S3, we need to attach the credentials form the IAM user to the AD SERVICE ACCOUNT which SQL will login and use via the PROXY CREDENTIALS we created in an earlier step.

SECURITY NOTE: It is important the AWS IAM USER only has S3 Access (and KMS Key access, if needed) to the single S3 bucket used by PowerShell.

[1] First, we need windows to create a USER account on the server for our MS AD User. There are a number of ways to accomplish this. Here is one:

#PowerShell code to create SERVICE USER on the server.

$username = "DOMAIN\SqlSsisUserr"
$password = ConvertTo-SecureString 'MyPASSWORD' -AsPlainText -Force
$credentialVar = new-object -typename System.Management.Automation.PSCredential `
         -argumentlist $username, $password
$computerVar = "MyEC2SERVERNAME"

Invoke-Command -ComputerName $computerVar -ScriptBlock {exit} -Credential $credentialVar 

[2] Assuming you have an individual AWS account with CLI, already configured on the server, you can copy your AWS folder into the new user (SqlSsisUser) directory and then edit the files with the correct AWS KEYS and AWS SECRET for the IAM User that DOMAIN\SqlSsisUser needs. Alternatively you can create the .aws folder and files.

NEXT: PART 5: CAPTURING METRICS AND OUTPUTTING TO CSV FILE