Amazon RDS for Microsoft SQL Server Connector

Overview

Used for OneTrust Data Discovery, the Amazon RDS for Microsoft SQL Server connector connects to Microsoft’s cloud file sharing system and can be used to scan file system information directly from SQL systems.

Pod Residency

rdbms-scanner

Supported Scan Actions

Scan Type

Description

Catalog

Only the metadata in the database like details of schema, table, or column are scanned.

Catalog & Scan

Scans both metadata and data under the tables and returns the appropriate labels.

Permissions

OneTrust recommends to adhere to your organization's security and infrastructure policy and include system engineer resources or other qualified subject matter experts to set up credentials on data sources. For specific instructions for how to enable these credentials, refer to your data source's implementation documentation, knowledge base and/or support resources.

This connector requires Read Access to each database which needs to be scanned.

Connection URL

JSON 
{
  "connectionUrl": "jdbc:sqlserver://your_instance.us-east-1.rds.amazonaws.com:1433;databaseName=your_db_name",
  "user": "your_user",
  "password": "your_password"
} 

To configure credentials in SQL Management Studio

CAUTION:

This should be done by a system admin with full knowledge of company security and system policies.

  1. Log in to SQL Management Studio as an admin.
  2. In SQL Management Studio, ensure your server is enabled for remote connection.
    Note:

    Enable remote connections to the server, if not already established for your organization.

  3. Right click on your server and go to Properties. As with all connectors, make sure your firewall rules safelist the scanner IP.
  4. Under the Security page, select SQL Server and Windows Authentication (Or just Windows Authentication if you are supporting NTLMv2 on your server)
  5. Click the OK button. You will need to restart SQL server.
  6. Create a read only SQL User or add your AD user to the database.
  7. You may set the password to expire following your security best practice.
  8. Ensure your user is configured on each database by going to User Mapping and ensuring db_datareader is selected.

Scan Profile Support

Note:

For additional details on scan profile configuration and management, see Setting Up Scan Profiles.

Important:

The database name is case sensitive, so ensure you are entering the exact naming convention when entering the database connection string or adding/updating the name on the scan profile.

Limits

  • Maximum number of databases. Limits the number of databases.

  • Maximum number of schemas per database. Limits the number of schemas per database.

  • Maximum number of tables per schema. Limits the number of tables per schema.

  • Limitation method for scanning rows per table.

    • First with Limit

    • Random Percentage

    • Random Percentage with Limit

Include Path Details

  • Include/Exclude support at database, schema, and table level.

Wildcard

Wildcards are not supported for column inclusion or exclusion.

  • Example: *Name* will not work to include/exclude both firstName and lastName columns

  • Instead, you must specify the exact column names separated by commas (e.g. firstName,lastName).

Important:

Columns Excluded in the Data Dictionary

When a column is excluded, it is still cataloged by the Data Dictionary. This means:

  • Basic metadata for the column is retrieved.

  • During extraction, the actual column content is skipped based on the configuration.

Include/Exclude Path Configuration Fields

  • Database

  • Schema

  • Table

  • Column

Collected Objects & Fields

Objects

Fields

Databases

  • Name

  • Filed Type (Columns)

  • Owner

  • Product Version

  • Product Name

  • Product Family

Tables

  • Name

  • Comment

  • Rows Estimate

  • Size Estimate

Columns

  • Name

  • Column Index (Order)

  • Comment

  • Nullable (T/F)

  • Size Estimate

  • Comment

Keys

  • Primary Key Name

  • Primary Key Columns

  • Foreign Key Name

  • Foreign Key Columns