Standing Up an Amazon Aurora Database with an Automatically Rotated Password Using AWS Secrets Manager

• Create a new Amazon Aurora MySQL-compatible database
• Use the standard create option
• Leave the engine version as the default
• Select a Dev/Test template
• Use employees-database as the DB cluster identifier
• Use admin as the master username
• Enable using AWS Secrets Manager to manage the master credentials
• Use the default KMS key for encryption of the password (aws/secretsmanager)
• Use an Aurora Standard configuration option
• Select a db.t3.medium burstable class instance type
• Do not create an Aurora replica
• Enable connectivity to Connect to an EC2 compute resource
• Select the DatabaseClient EC2 instance
• Create a new VPC Security Group called database-sg
• Create an initial database named employees_db

• Find and select your RDS credentials secret from the AWS Secrets Manager service (Example: rds!cluster-a5fd421e-64e5-4580-be8e-006919539758)

• Refresh the page to ensure you are using the latest version of the secret. RDS will run an initial rotation once the RDS instance is created!

• Retrieve the secret value from the secret and keep the tab open

• Go back to your DatabaseClient EC2 connection

• Create a new local file called employees.txt within the /home/ssm-user/ directory that contains the soon to be imported data using this code block from the GitHub repo

• Change the directory to the ssm-user home and cat the file to ensure the data was written.

• Confirm the status of the Amazon Aurora database writer endpoint is Available and copy the endpoint name.

• In Session Manager, connect to your RDS instance writer endpoint using the below command. Make sure you change the value for host to the writer endpoint name. The user and password are your Secrets Manager secret that you set in previous steps.

mysql --host=CHANGE_TO_WRITER_ENDPOINT_NAME --user=admin --password employees_db

• Once prompted for the password, use the value retrieved from AWS Secrets Manager. (If you get an error, refresh the page, copy the latest secret value, rerun the command, and enter the new value for the password prompt).

• You should now see the MySQL prompt!

• Create a new table called Employees for later use: GitHub Repo - Create Table

• Load the data using the following SQL query: GitHub Repo - Load Data

• Verify the data was imported by viewing data in the table:

SELECT *
FROM Employees;

• Quit your RDS connection within the EC2 Session Manager terminal and go back to the ssm-user home directory
• Within the AWS Secrets Manager service (console or API/CLI), trigger an immediate rotation of the RDS credential secret
• Ensure the version status has changed before moving on

• After the rotation is completed in AWS Secrets Manager navigate back to your EC2 Session Manager connection
• Attempt to connect to RDS using the same credentials from before (This should fail now)
• Within AWS Secrets Manager, retrieve the update secret value for the RDS credentials
• Re-connect to RDS using the new password value for your Master Username

• Connect to the DatabaseClient EC2 instance using Session Manager
• Verify the MySQL client is installed:

sudo yum list installed | grep mariadb

• If not installed, install it:

sudo yum install -y mysql