MS SQL Lineage Scanner

The MS SQL lineage scanner supports two types of metadata ingestion:

Online (JDBC) extraction and configuration
File-based extraction and configuration

Supported statement types and SQL syntax

Supported Statements Types:
- CREATE TABLE AS SELECT
- CREATE VIEW
- INSERT
- MERGE
- SELECT
- UPDATE
Supported Database Objects for Design Time Lineage:
- Views
- Materialized Views
- Lineage for Stored Procedures
- Local and Global Temporary Table (created within Stored Procedures)
Not yet supported SQL constructs:
- Statements containing these SQL constructs will not be included in the lineage diagram:
  - Table Value Constructor
  - Table Functions
  - References to Linked Servers objects
Lineage is not supported for:
- Jobs
  - Jobs documentation: learn.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-ver16
- Dynamic SQL
- Lineage for computed columns

Note: Most of the unsupported syntax and MS SQL artifacts are planned to be supported in the upcoming versions of the scanner, based on prioritization.

Common extraction prerequisites (JDBC & file)

To ensure proper access to the necessary system views, the following database privileges must be granted:

Server-Level Privileges

VIEW ANY DATABASE: This permission allows the user to view metadata about all databases on the SQL Server instance. It is required for querying the master.sys.databases view.
```
GRANT VIEW ANY DATABASE TO [YourUserOrRole];
```

Database-Level Privileges

The scanner’s database user needs to be able to query these system views for each scanned database:

INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.ROUTINES
SYS.VIEWS
SYS.SCHEMAS
SYS.SQL_MODULES

The user must have the following permissions:

CONNECT: This permission allows the user to connect to the database.
```
USE [YourDatabaseName];
GRANT CONNECT TO [YourUserOrRole];
```
VIEW DEFINITION: This permission allows the user to view the metadata of database objects. It is essential for accessing the schema and system views listed above.
```
USE [YourDatabaseName];
GRANT VIEW DEFINITION TO [YourUserOrRole];
```

Online (JDBC) extraction and configuration

Online (JDBC) based extraction connects to MS SQL and extracts the necessary metadata.

Scope: Design time views and stored procedures.

Limitations: Currently, only username/password authentication is supported (other types are planned).

Property Description

Property	Description
`*name`	Unique name of the scanner job
`*sourceType`	Must contain MSSQL
`*description`	Human readable description
`oneConnections`	List of Ataccama ONE connection names for future automatic pairing
`*jdbcUrl`	Full JDBC connection string
`*username`	MSSQL username
`*password`	MSSQL password, can be encrypted
`includeDatabases`	List of databases to include in lineage extraction
`excludeDatabases`	List of databases to exclude from lineage extraction
`skipTableColumnsExtraction`	If set to true, data dictionary metadata is not extracted. It should be set to true only in case of troubleshooting, testing or in specific POC use cases. Default: false
`skipViewsExtraction`	If set to true, views metadata is not extracted. Default: false
`skipProceduresExtraction`	If set to true, procedures metadata is not extracted. Default: false

*name

Unique name of the scanner job

*sourceType

Must contain MSSQL

*description

Human readable description

oneConnections

List of Ataccama ONE connection names for future automatic pairing

*jdbcUrl

Full JDBC connection string

*username

MSSQL username

*password

MSSQL password, can be encrypted

includeDatabases

List of databases to include in lineage extraction

excludeDatabases

List of databases to exclude from lineage extraction

skipTableColumnsExtraction

If set to true, data dictionary metadata is not extracted. It should be set to true only in case of troubleshooting, testing or in specific POC use cases. Default: false

skipViewsExtraction

If set to true, views metadata is not extracted. Default: false

skipProceduresExtraction

If set to true, procedures metadata is not extracted. Default: false

Legend: *mandatory

Example configuration

{
  "scannerConfigs": [
    {
      "name": "mssql-lineage-poc",
      "sourceType": "MSSQL",
      "description": "Scan MSSQL using JDBC",
      "includeDatabases": ["_DemoDB"],
      "excludeDatabases": ["SANDBOX_DB", "OTHER_NON_PROD_DB"],
      "connection": {
        "jdbcUrl": "jdbc:sqlserver://localhost;encrypt=true;trustServerCertificate=true",
        "username": "ATA_LINEAGE_EXTRACTION_USER",
        "password": "@@ref:ata:[MSSQL_PASSWORD]"
      }
    }
  ]
}

File-based extraction and configuration

File-based extraction is not yet available.

Scanner special features

MS SQL scanner supports the same SQL "anomaly" detection checks as the Snowflake scanner. See the SQL "DQ" (Anomaly) detections for more details.

Was this page useful?