Query Logs

Available query logs sources for Microsoft SQL data sources:

• System tables / views

• Database Activity Streams on an Amazon RDS instance

If you are using a different hosting provider and you want to ingest query logs, please contact our sales team.

Database Activity Streams on an Amazon RDS instance

To enable Database Activity Streams on an Amazon RDS instance running Microsoft SQL Server (MSSQL), follow the step-by-step instructions below using the AWS Management Console.

Step 1: Create a Database Audit Specification

Execute query on database instance to create a database audit specification called RDS_DAS_DB_SELECT_STAR_QUERY_STREAM and associate it with the server audit RDS_DAS_AUDIT, ensuring that all matched events are sent to the Kinesis DAS. The specified policy will capture any of the following command types: SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, and REFERENCES on the olist database, but only for commands submitted by the public principal (public matches all users). The policy will be activated immediately with WITH (STATE = ON).

The query:

CREATE DATABASE AUDIT SPECIFICATION [RDS_DAS_DB_SELECT_STAR_QUERY_STREAM]
FOR SERVER AUDIT [RDS_DAS_AUDIT]
ADD (
  SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES
  ON Database::olist
  BY public
)
WITH (STATE = ON);

To enable query log for multiple databases, execute operation in each database individually. In SQL Server, including the version running on AWS RDS, there is no direct way to log queries across all databases without setting up database-level audit specifications for each database individually. SQL Server auditing is designed to be granular, and the audit specifications must be created at the database level to capture events occurring within that specific database.

Step 2: Sign in to the AWS Management Console

1. Open the AWS Management Console at https://console.aws.amazon.com/rds/.
2. Sign in with your AWS credentials.

Step 3: Navigate to the RDS Dashboard

1. In the AWS Management Console, search for RDS in the top search bar.

2. Click on RDS to open the Amazon RDS Dashboard.

Step 4: Select Your RDS Instance

1. In the RDS Dashboard, click on Databases in the left-hand menu.

2. Locate your Microsoft SQL Server RDS instance in the list of databases.

3. Click on the DB Identifier of the MSSQL instance you wish to enable the Database Activity Stream for.

4. Verify DB instance class used by instance with supported DB instance classes for database activity streams

Step 5: Start the Database Activity Stream

1. On the database details page, click on the Actions dropdown menu located in the top-right corner.

2. Select Start activity stream from the dropdown menu.

Step 6: Configure the Database Activity Stream

1. The Start database activity stream: name window will appear, where name is your RDS instance.

2. Configure the following settings:

   • AWS KMS key: Choose a KMS key from the list of available AWS KMS keys. This key is used to encrypt the key that encrypts the database activity stream data.

   • Database activity events: Check the box to Enable engine-native audit fields.

3. Choose Immediately if you can restart the RDS instance right away and start the database activity stream immediately. If you select During the next maintenance window, the RDS instance will restart during the next scheduled maintenance window, and the activity stream will start at that time.

You can refer to AWS documentation about starting a database activity stream for details.

Entries in the activity stream are encrypted using unique batch keys before being written to Kineses. To facilitate decryption, the session keys is itself encrypted using the selected KMS key and included in the batch. Anyone with access to the KMS key will be able to decrypt the batch keys and use those to access raw audit events from the database.

Step 7: Start the Activity Stream

1. After configuring the settings, click on Start database activity stream.

2. The status of the database will indicate that the activity stream is starting.

Step 8: Locate AWS KMS key and AWS Kinesis stream

1. Once on the database details page, open the Configuration tab.

2. In the Configuration tab, locate in section Database activity stream following:

   • AWS KMS key: This shows the current KMS key used by the Database Activity Stream. Note this key may be different one that one used for the RDS instance.

   • Kinesis data stream: Note the configured Kinesis stream.

Step 9. Create CloudFormation stack

Select Star recommends setting up integration using AWS CloudFormation, which allows you to make necessary changes to the RDS instance environment automatically, transparently, safely, and auditably. It deploys AWS Firehose and AWS Lambda function to store relevant information in AWS S3 bucket and enable us to access the AWS S3 bucket.

The source code of the CloudFormation template, build scripts, and real-time logs of the continuous deployment system are available on our public repository selectstar/cloudformation-templates, hosted on Github, to be freely audited.

You can pass the link to CloudFormation to the infrastructure team to enable the integration to be created.

1. A simple form will be displayed in Select Star. In option Source Type select "RDS Database Activity Stream".

2. Click Open CloudFormation. A new window will open to proceed to the creation of a CloudFormation stack by AWS Management Console. Make sure you are logged into the AWS account in which the RDS instance is hosted.

3. The Create Stack form will be displayed. Some of the values will be filled in by default, under Parameters, enter:

• KinesisStreamARN: The ARN of the Kinesis stream where RDS Database Activity Streams are delivered. Example: arn:aws:kinesis:us-east-2:000000000:stream/aws-rds-das-db-ZQO7M43PGGUXJEZVYSALTO76KA

• KmsKeyARN: The ARN of the KMS key used for encryption of RDS Database Activity Streams. Example: arn:aws:kms:us-east-2:000000000:key/f319545f-a0d4-4bfc-896f-5d37fe921ffb

• RdsResourceId: The ARN of the RDS instance or cluster producing the logs. Example: db-ZQO7M43PGGUXJEZVYSALTO76KA

4. Review the information and under Capabilities choose "I acknowledge that AWS CloudFormation might create IAM resources".

Screenshot shows the "Capabilities" section in "Quick create stack" form.

5. Choose Create stack.

1. Wait until the stack changes its status to "CREATE_COMPLETE" from "CREATE_IN_PROGRESS " in the tab "Stack info." The operation should take up to 5 minutes. You need to refresh the tab to see the progress.

Screenshot shows the "State" value in "Stack info" tab.

7. After completing stack creation, the Role ARN and S3BucketName is available from the "Outputs". Copy and save the RoleArn and S3BucketName for later use.

8. Logs are published in batches, which can be buffered up to 15 minutes. After configuring and executing a database query, new files should appear in the bucket under the processed prefix.

Step 10. Confirm authorization

1. Return to Select Star. You should see a form that allows you to provide "Role ARN" . Fill form in the required information:

• Type: Select "RDS Database Activity Stream"

• AWS region: Identifier of the AWS Region where your MSSQL instance is hosted eg. us-east-2

• Role ARN: Identifier of the AWS IAM Role to use by Select Star. Available in the "Outputs" tab of AWS CloudFormation.

• S3 bucket: The name of the AWS S3 bucket which stores query logs. Available in the "Outputs" tab of AWS CloudFormation.

• S3 object prefix: The name of the AWS S3 prefix which stores query logs. For deployment using AWS CloudFormation, this is processed/ by default.

2. Click Connect.