Microsoft SQL Server Connector

Overview

Used for OneTrust Data Discovery, the 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 via a SQL Server of AD user (NTLMv2 authentication support required and is enabled by default on most implementations of Windows Server).

More information here: Network security: LAN Manager authentication level

Note:

To read data from Microsoft SQL Server 2008 R2, ensure the following:

  • Operating on a version that supports TLS 1.2, as this is OneTrust's security standard cited in the Data Discovery Security Guide. For more information on TLS 1.2 support, click here.

  • Server OS supports TLS 1.2 and set as the default protocol. For more information on setting this protocol, click here.

NTLMv2

Enabling NTLMv2 support for your worker node in your vault credential:

JSON 
{
    "connectionUrl": "jdbc:sqlserver://mssql-02.database.windows.net:1433;database=mssql-
     02;encrypt=false;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;",
    "password": "XXXXXXXXXX",
    "user": "XXXXXXXXXX"
}

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.

Note:

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.

  • Maximum number of rows per table. Limits the number of rows per table.

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