SQL Server Connector

# Create option group with SQLSERVER_AUDIT option
aws rds create-option-group \
  --option-group-name sqlserver-audit-options \
  --engine-name sqlserver-ee \
  --major-engine-version 15.00 \
  --option-group-description "SQL Server option group with audit enabled"

# Add SQLSERVER_AUDIT option with S3 bucket for audit files
aws rds add-option-to-option-group \
  --option-group-name sqlserver-audit-options \
  --options "OptionName=SQLSERVER_AUDIT,OptionSettings=[{Name=S3_BUCKET_ARN,Value=arn:aws:s3:::my-sqlserver-audit-bucket},{Name=IAM_ROLE_ARN,Value=arn:aws:iam::123456789012:role/rds-sqlserver-audit-role}]"

# Apply option group to your instance
aws rds modify-db-instance \
  --db-instance-identifier my-sqlserver-instance \
  --option-group-name sqlserver-audit-options \
  --apply-immediately

# Create S3 bucket for audit files
aws s3 mb s3://my-sqlserver-audit-bucket --region us-east-1

# Create bucket policy to allow RDS access
cat > bucket-policy.json << 'EOF'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowRDSAuditWrite",
      "Effect": "Allow",
      "Principal": {
        "Service": "rds.amazonaws.com"
      },
      "Action": [
        "s3:PutObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::my-sqlserver-audit-bucket",
        "arn:aws:s3:::my-sqlserver-audit-bucket/*"
      ],
      "Condition": {
        "StringEquals": {
          "aws:SourceAccount": "123456789012"
        },
        "ArnLike": {
          "aws:SourceArn": "arn:aws:rds:us-east-1:123456789012:db:*"
        }
      }
    }
  ]
}
EOF

aws s3api put-bucket-policy \
  --bucket my-sqlserver-audit-bucket \
  --policy file://bucket-policy.json

# Create IAM role for RDS to write to S3
aws iam create-role \
  --role-name rds-sqlserver-audit-role \
  --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"rds.amazonaws.com"},"Action":"sts:AssumeRole"}]}'

# Enable CloudWatch Logs export for agent and error logs
aws rds modify-db-instance \
  --db-instance-identifier my-sqlserver-instance \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["agent","error"]}'

# Create a new instance with CloudWatch logging enabled
aws rds create-db-instance \
  --db-instance-identifier my-sqlserver-instance \
  --db-instance-class db.m5.large \
  --engine sqlserver-ee \
  --engine-version 15.00.4365.2.v1 \
  --master-username admin \
  --manage-master-user-password \
  --allocated-storage 100 \
  --option-group-name sqlserver-audit-options \
  --cloudwatch-logs-export-configuration '["agent","error"]'

# Disable log export (if needed)
aws rds modify-db-instance \
  --db-instance-identifier my-sqlserver-instance \
  --cloudwatch-logs-export-configuration '{"DisableLogTypes":["agent","error"]}'

-- Create server audit using RDS stored procedure
-- RDS SQL Server writes audit files to D:\rdsdbdata\SQLAudit\
EXEC msdb.dbo.rds_enable_server_audit @audit_name = 'DBauditServerAudit';

-- Create server audit specification for login tracking
CREATE SERVER AUDIT SPECIFICATION DBauditServerSpec
FOR SERVER AUDIT DBauditServerAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);

-- Create database audit specification for data access
USE your_database;
CREATE DATABASE AUDIT SPECIFICATION DBauditDatabaseSpec
FOR SERVER AUDIT DBauditServerAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public)
WITH (STATE = ON);

-- Verify audit configuration
SELECT * FROM sys.server_audits;
SELECT * FROM sys.server_audit_specifications;

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "CloudWatchLogsAccess",
      "Effect": "Allow",
      "Action": [
        "logs:DescribeLogGroups",
        "logs:DescribeLogStreams",
        "logs:GetLogEvents",
        "logs:FilterLogEvents"
      ],
      "Resource": [
        "arn:aws:logs:*:*:log-group:/aws/rds/instance/*/agent:*",
        "arn:aws:logs:*:*:log-group:/aws/rds/instance/*/error:*"
      ]
    },
    {
      "Sid": "S3AuditBucketAccess",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::my-sqlserver-audit-bucket",
        "arn:aws:s3:::my-sqlserver-audit-bucket/*"
      ]
    },
    {
      "Sid": "RDSDescribeAccess",
      "Effect": "Allow",
      "Action": [
        "rds:DescribeDBInstances",
        "rds:DescribeDBLogFiles",
        "rds:DownloadDBLogFilePortion"
      ],
      "Resource": "*"
    }
  ]
}

# DB Audit configuration for RDS SQL Server
databases:
  - name: production-rds-sqlserver
    type: sqlserver

    # RDS endpoint
    host: my-sqlserver-instance.xxxx.us-east-1.rds.amazonaws.com
    port: 1433
    database: your_database
    username: dbaudit_reader
    password: ${RDS_SQLSERVER_PASSWORD}

    # SSL is enabled by default for RDS
    encrypt: true
    trust_server_certificate: false

    options:
      # Use S3 as the audit log source
      log_source: s3

      # S3 configuration for audit files
      s3:
        bucket: my-sqlserver-audit-bucket
        prefix: my-sqlserver-instance/
        region: us-east-1

        # AWS credentials (use IAM role if running on EC2/ECS)
        # access_key_id: ${AWS_ACCESS_KEY_ID}
        # secret_access_key: ${AWS_SECRET_ACCESS_KEY}

        # Or use IAM role (recommended)
        use_iam_role: true

      # CloudWatch configuration for agent/error logs
      cloudwatch:
        region: us-east-1
        log_groups:
          - /aws/rds/instance/my-sqlserver-instance/agent
          - /aws/rds/instance/my-sqlserver-instance/error
        use_iam_role: true

      # Polling interval
      polling_interval: 30

# View agent logs in CloudWatch Logs Insights
# Log group: /aws/rds/instance/<instance-id>/agent

# Query for SQL Server Agent job events
fields @timestamp, @message
| filter @message like /Job|Step|Schedule/
| sort @timestamp desc
| limit 100

# View error logs
# Log group: /aws/rds/instance/<instance-id>/error

# Query for failed login attempts
fields @timestamp, @message
| filter @message like /Login failed|Error: 18456/
| sort @timestamp desc
| limit 100

# Query for severity errors
fields @timestamp, @message
| filter @message like /Severity: [1-2][0-9]/
| sort @timestamp desc
| limit 100

# Query S3 audit files with Athena (optional)
# Audit files in: s3://my-sqlserver-audit-bucket/<instance-id>/