MS SQL Server Integration Requirements

The following are the prerequisites necessary for IBM Automatic Data Lineage to connect to this third-party system, which you may choose to do at your sole discretion. Note that while these are usually sufficient to connect to this third-party system, we cannot guarantee that the connection or integration will be successful since we have no control, liability, or responsibility for third-party products or services, including for their performance.

Product support
- SQL Server: version 2012 and older (remote) - no longer supported from R42.10.10 due to JDBC driver update
- SQL Server: version 2005 - no longer supported from R42.10.10 due to JDBC driver update
- Analytic Platform System (formerly known as Parallel Data Warehouse)
- Azure SQL Database
- Azure SQL Managed Instance
- Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse)
  - The SQL data warehouse component is supported.
  - Extraction from the "serverless SQL pool" is not fully supported and may produce errors. There may be cases of lineage producing results through TABLE COLUMNS UNKNOWN or an inaccurate view or table column list.
- Amazon RDS for SQL Server
  - For successful validation and extraction, the following databases have to be added to the "Excluded databases/schemas" list (mssql.excludedDbsSchemas property): model, master, rdsadmin, and msdb.
- SAP ASE
User having the following rights:
- CONNECT SQL—to connect to the server
  - Azure Synapse Analytics specific—only CONNECT SQL is required, no other rights are needed
- VIEW ANY DATABASE—to extract the list of existing databases
- ALTER ANY LINKED SERVER—to extract linked server definitions (no write is actually performed, but there's no read-only permission for this metadata)
- In each extracted database:
  - VIEW DEFINITION (or VIEW ANY DEFINITION on the server level)—to extract object definitions
  - MSSQL = SQL Server (2008 and newer)
    - SELECT on sys.sql_expression_dependencies—to extract object dependencies
    - SELECT on the following views in INFORMATION_SCHEMA:
      - COLUMNS
      - TABLES
      - ROUTINES
      - VIEWS
    - SELECT on the following tables in sys:
      - synonyms
      - triggers
      - server_triggers
      - server_sql_modules
      - extended_properties
      - servers
      - all_objects
      - tables
      - index_columns
      - indexes
      - foreign_key_columns
      - sequences
      - table_types
  - MSSQL2005 = SQL Server 2005 and older
    - SELECT on sql_dependencies—to extract object dependencies
    - SELECT on the following views in INFORMATION_SCHEMA:
      - COLUMNS
      - TABLES
      - ROUTINES
      - VIEWS
    - SELECT on the following tables in sys:
      - synonyms
      - triggers
      - server_triggers
      - server_sql_modules
      - extended_properties
      - servers
      - tables
      - index_columns
      - indexes
      - foreign_key_columns
  - PDW = Parallel Data Warehouse
    - SELECT on sys.sql_expression_dependencies—to extract object dependencies
    - SELECT on the following tables in INFORMATION_SCHEMA:
      - TABLES
      - VIEWS
      - COLUMNS
      - ROUTINES
    - SELECT on the following tables in sys:
      - synonyms
      - extended_properties
      - tables
      - index_columns
      - indexes
      - foreign_key_columns
  - SYBASE = Sybase ASE / SAP ASE
    - SELECT on sysdepends—to extract object dependencies
    - SELECT on:
      - sysobjects
      - sysusers
      - syscolumns
      - systypes
      - syscomments
      - sysdepends
  - AZURE_SQL_DB = Azure SQL Database
    - see MSSQL
  - AZURE_MANAGED = Azure SQL Managed Instance
    - see MSSQL
  - AZURE_SYNAPSE = Azure Synapse Analytics (Azure SQL Data Warehouse / SQL Shared pool)
    - see PDW
  - AMAZON_RDS_SQL_SERVER = Amazon RDS for SQL Server
    - see MSSQL
Connection parameters:
- Name or IP address of the SQL Server database
- Port on which the SQL Server database listens for JDBC connections
- Mixed mode authentication or Windows authentication
- User name
- User password
- Automatic Data Lineage supports these authentication types for MS SQL server: MS SQL Server Authentication Types
Database must be accessible via network
Linked servers for the OPENQUERY operator
- In most cases, the linked server used will be extracted from the database.
- If one of following events is logged, it has to be configured according to mssql.connections.file (the requested connection will be part of the log event):
  - ERROR LINKED_SERVER_CONNECTION_NOT_FOUND
  - ERROR MISSING_LINKED_SERVER_CONNECTIONS
  - WARNING NO_MAPPING_FOR_CONNECTION—if the logged linked server data is correct, then this could even be caused by the fact that Automatic Data Lineage has not extracted the targeted linked server
  - WARNING NO_SUBDIALECT_MAPPING_FOR_CONNECTION—if the logged linked server data is correct, then this could even be caused by the fact that Automatic Data Lineage has not extracted the targeted linked server

Known Unsupported Features

Automatic Data Lineage does not support the following MS SQL features. This list includes all of the features that IBM is aware are unsupported, but it might not be comprehensive.

Lineage through dynamically executed code through EXECUTE IMMEDIATE
Lineage through INSERT into, UPDATE of an OPENQUERY
Lineage through OPENXML
Lineage through OPENROWSET
Lineage through ODBC scalar functions; use regular SQL functions instead
Objects renamed using the sp_rename procedure; do not show the new object name (limitation of the sp_rename procedure)
Lineage extraction for encrypted objects - stored procedures (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16#Encrypt), views (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16#b-using-with-encryption), etc.
Authentication using both standalone and group managed service accounts