Prerequisites

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Before you install the SQL Server OCF connector, ensure that you have performed the following:

Configure Network Connectivity

Open outbound TCP port 1433 to SQL Server (default).

Note

SQL Server instances can listen on non-standard ports. If you connect Alation to a SQL Server instance, then open the appropriate outbound TCP ports from the Alation server.

Create a Service Account

Alation supports both SQL Server authentication and Windows authentication.

For Windows authentication, SQL Server must be configured for Kerberos, SQL Server default, or instance SPNs must be registered with Active Directory. The following information is required during configuration if SQL Server uses Windows authentication:

  • Active Directory realm

  • Active Directory server IP or DNS name

To verify that the SQL Server is configured for Kerberos, run the following command from a Windows CMD prompt. The command must be run on a computer in the same realm as SQL Server:

setspn -L [SQL-server-hostname]

The following screenshot is an example output of the setspn command showing SPNs for a SQL Server default instance:

../../../_images/DS_SQLServer02.png

We recommend using an Active Directory service account. This enables Alation to read log files generated by extended events. A SQL Server username and password also reads log files generated by extended events.

Grant Required Permissions

The service account you want to use requires a specific set of permissions on SQL Server.

Grant Permissions for Metadata Extraction

Grant Access to Databases, Schemas, and Tables

Grant the service account access to the database(s), schema(s), and table(s) that you want to be cataloged in Alation after metadata extraction:

Grant Permissions on Database(s)

  • Grants the permissions to see the definitions of structures in the database, including tables, views, or stored procedures.

    • Use the following query to grant server-level permission to view databases within the server:

      USE MASTER;
GRANT VIEW ANY DATABASE TO <LOGIN>;

    • Use the following query to set the user mapping required in the database for extraction:

      USE <DATABASE>;
CREATE USER <USER> FOR LOGIN <LOGIN>;

    • Use the following query to grant permission to view definitions of objects within the database:

      For every database in an instance -

      USE <DATABASE>
GRANT VIEW DEFINITION TO <USER>;

      For specific schemas present in an instance within the database -

      USE <DATABASE>;
GRANT VIEW DEFINITION ON SCHEMA::<SCHEMA> TO <USER>;

    • Use the following query to grant VIEW access to the user for accessible databases at the server-level:

      USE MASTER
GRANT VIEW ANY DEFINITION TO <LOGIN>;
Grant Permissions on Schemas

  • Use the following query to grant access to system schemas:

    USE <DATABASE>;
GRANT SELECT ON SCHEMA::sys to <USER> OR <ROLE>;

    USE MASTER;
GRANT SELECT ON SCHEMA::INFORMATION_SCHEMA to <USER> OR <ROLE>;
Grant Permissions on Views

To grant access to specific views, refer the following information:

Metadata Type

Query To Use

Metadata Type: Catalog

System Views: sys.databases

 
USE MASTER;
GRANT SELECT ON sys.databases to <USER> OR <ROLE>;

Metadata Type: Schema

System Views:

  • sys.schemas

  • sys.extended_properties

  • INFORMATION_SCHEMA .SCHEMATA

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

USE MASTER;
GRANT SELECT ON [INFORMATION_SCHEMA].[object] to
 <USER> OR <ROLE>;

Metadata Type: Catalog

System Views:

  • sys.indexes

  • sys.partitions

  • sys.allocation_units

  • sys.schemas

  • sys.extended_properties

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Metadata Type: View

System Views:

  • sys.views

  • sys.extended_properties

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Metadata Type: Column

System Views:

  • sys.columns

  • sys.extended_properties

  • sys.views

  • sys.tables

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Metadata Type: Primary Key

System Views:

  • INFORMATION_SCHEMA. TABLE_CONSTRAINTS

  • INFORMATION_SCHEMA. KEY_COLUMN_USAGE

 
USE MASTER;
GRANT SELECT ON [INFORMATION_SCHEMA].[object] to
 <USER> OR <ROLE>;

Metadata Type: Foreign Key

System Views:

  • INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS

  • INFORMATION_SCHEMA. KEY_COLUMN_USAGE

  • INFORMATION_SCHEMA. TABLE_CONSTRAINTS

 
USE MASTER;
GRANT SELECT ON [INFORMATION_SCHEMA].[object] to
 <USER> OR <ROLE>;

Metadata Type: Index

System Views:

  • sys.indexes

  • sys.index_columns

  • sys.objects

 
USE MASTER;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Metadata Type: Function

System Views:

  • sys.sql_modules

  • sys.extended_properties

  • INFORMATION_SCHEMA. ROUTINES

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

USE MASTER;
GRANT SELECT ON [INFORMATION_SCHEMA].[object] to
 <USER> OR <ROLE>;

Metadata Type: Function Definition

System Views:

  • sys.objects

  • INFORMATION_SCHEMA. ROUTINES

  • INFORMATION_SCHEMA. PARAMETERS

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

USE MASTER;
GRANT SELECT ON [INFORMATION_SCHEMA].[object] to
 <USER> OR <ROLE>;

Metadata Type: Synonym

System Views:

  • sys.synonyms

  • sys.extended_properties

 
USE MASTER;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Metadata Type: Synonym Column

System Views:

  • sys.tables

  • sys.types

  • sys.extended_properties

  • sys.synonyms

  • sys.databases

  • sys.views

 
USE <DATABASE>;
GRANT SELECT ON [sys].[object] to <USER> OR <ROLE>;

Grant Permissions for Table Profiling

GRANT SELECT at Database or Schema level to alation (service account)

To grant SELECT at database level:

USE <DATABASE>
GRANT SELECT TO <USER>

To grant SELECT at schema level:

USE <DATABASE>
GRANT SELECT ON SCHEMA::[schema_name] TO <USER>

Grant Permissions for Query Log Ingestion

Grant Permissions for SQL Server Audit

The result of an SQL Server Audit is either view or a table. The service account requires the following permissions:

  • The SELECT permission on view or table:

    GRANT SELECT on <table/view> TO <USER>

  • CONTROL SERVER permission:

    GRANT CONTROL SERVER TO <USER>

Grant Permissions for SQL Profiler Trace

Grant SELECT permission on table or on view, if the view is created from a table.

Grant Permissions for XEvents

  • Grant EXECUTE permission on xp_dirtree stored procedure to list XEL files.

  • Grant VIEW SERVER STATE permission to read XEL file using sys.fn_xe_file_target_read_file function. For details, see sys.fn_xe_file_target_read_file.

  • Grant system administrator (sysadmin) role to provide file access to the service account.