LogoLogo
About UsCustomersResourcesGet Started for Free
  • What is Select Star?
  • 🏁Getting Started
    • 1. Data Source Setup
    • 2. Mark Service Accounts
    • 3. Hide Unwanted Datasets
    • 4. Invite Owners
    • 5. Add Documentation
    • Next Steps
  • 🔄Integrations
    • Snowflake
      • Using Key Pair Authentication
      • Using Password Authentication
      • Snowflake Tag Sync
      • Snowflake Key Pair Rotation
    • Databricks
      • Databricks on AWS
      • Databricks on Azure
    • BigQuery
    • AWS Redshift
      • Manual setup
    • Microsoft SQL Server / MS SQL (beta)
      • Query Logs
    • MySQL (beta)
      • Query Logs
    • Oracle (beta)
      • Query Logs
    • Salesforce (beta)
    • DB2 (beta)
    • PostgreSQL
      • AWS Aurora PostgreSQL
      • AWS RDS PostgreSQL
      • PostgreSQL on-prem
    • AWS Glue (beta)
    • dbt
      • dbt Cloud
      • dbt Core (open source)
      • dbt Tags
      • dbt Tests
      • dbt docs Sync
        • Github dbt docs Sync
        • Bitbucket dbt docs Sync
      • dbt Impact Report
      • dbt Project Dependencies
    • Apache Airflow (beta)
    • Tableau
      • Tableau Cloud
      • Tableau Server
    • PowerBI
    • Looker
    • Metabase
    • Fivetran (beta)
    • Mode
    • Sigma Computing
    • Sisense / Periscope (beta)
    • Looker Studio (beta)
    • ThoughtSpot
    • QuickSight (beta)
      • Event Logs
    • Hex (beta)
    • Slack
    • Monte Carlo
    • Private Network
    • Request an Integration
  • ✨Features
    • Search
    • Table Page
    • Database Page
    • Dashboard Page
    • Data Lineage
    • Entity Relationship Diagram (ERD)
    • Queries & Joins
    • Tags
    • Teams
    • Discussion
    • Downstream Notifications
    • Documentation
      • Pages
      • Metrics
        • Metrics Generation
      • Glossary
    • Automated Documentation
    • User Analytics
    • Chrome Extension
      • Organization-wide install
    • Source Tables
    • Cost Analysis
    • Schema Change Detection
    • AI Features & Settings
      • Ask AI Chatbot
    • Request a Feature
  • 🧭Data Discovery
    • Where's my data?
    • Where's my dashboard?
    • How can I get the full context of this data?
    • My dashboard looks off
    • Change management
    • I'm new to the team
    • I have a data question
  • 🗃️Data Management
    • Add Documentation
      • CSV Metadata Upload
    • Collections
    • Tags
    • Data Ownership
    • Sensitive / PII Data
    • Automated PII Detection
  • 📚Learning Data
    • Getting Started: Looker
    • Getting Started: Mode
    • Getting Started: Tableau
    • Getting Started: Snowflake
    • Getting Started: Databricks
    • Getting Started: Data Warehouse
    • Getting Started: BigQuery
      • Nested Fields
    • Getting Started: Sigma
    • Getting Started: ThoughtSpot
  • 🛠️Data Source Management
    • Manage Data Sources
    • Connect Data Source Users to Select Star
    • Custom Attributes
    • Recent Queries
  • 👥User Management
    • Invite Users
    • Roles & Permissions
    • SAML SSO
    • Importing Roles and Teams (Okta)
    • Policy Based Access Control
    • Account and User Settings
  • 💻Select Star API
    • Overview
    • API Token
    • Getting Started
    • Rich Text Descriptions via API
    • Troubleshooting
    • API Examples
    • API Reference
  • 🔓Security & Compliance
  • ❓FAQ
    • Icon Map
  • 📰Changelog
    • May 20, 2025 - Chrome Extension, Notifications, and More!
    • April 16, 2025 - Semantic Models, AI Metrics, and More!
    • March 12, 2025 - Fivetran Integration, Tableau Updates and More!
    • February 6, 2025 - Collections, Slack App Published, Salesforce Formula Lineage and more!
    • December 10, 2024 - Hex Integration, Impact Score & Snowflake Key Pair Authentication!
    • November 13, 2024 - New Navigation, Airflow and More!
    • September 30, 2024 - Upstream Data Quality Issue Tracking & 5 New Integrations!
    • August 30, 2024 - Monte Carlo, dbt Cross-Project Lineage
    • July 31, 2024 - Glossary Import, Lineage Updates & more!
    • July 9, 2024 - Lineage Explorer 2.0, Slack AI and Notifications
    • February 29, 2024 - AI Chat, Schema Change Notifications
    • February 23, 2024 - Manual Lineage Creation
    • November 23, 2023 - Bulk AI Documentation
    • October 19, 2023 - Downstream Notifications
    • October 16, 2023 - New Homepage
    • October 13, 2023 - dbt Impact Report
    • Historical Changelogs
  • Security & Compliance
  • System Status
Powered by GitBook
On this page
  • Database Activity Streams on an Amazon RDS instance
  • Step 1: Create a Database Audit Specification
  • Step 2: Sign in to the AWS Management Console
  • Step 3: Navigate to the RDS Dashboard
  • Step 4: Select Your RDS Instance
  • Step 5: Start the Database Activity Stream
  • Step 6: Configure the Database Activity Stream
  • Step 7: Start the Activity Stream
  • Step 8: Locate AWS KMS key and AWS Kinesis stream
  • Step 9. Create CloudFormation stack
  • Step 10. Confirm authorization

Was this helpful?

  1. Integrations
  2. Microsoft SQL Server / MS SQL (beta)

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. 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.

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.

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.

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".

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.

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.

PreviousMicrosoft SQL Server / MS SQL (beta)NextMySQL (beta)

Last updated 8 months ago

Was this helpful?

Open the AWS Management Console at .

Verify DB instance class used by instance with

You can refer to AWS documentation about for details.

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

🔄
https://console.aws.amazon.com/rds/
supported DB instance classes for database activity streams
starting a database activity stream
selectstar/cloudformation-templates
Screenshot shows the "Capabilities" section in "Quick create stack" form.
Screenshot shows the "State" value in "Stack info" tab.