Configure Connection to Data Source

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

After you install the SQL Server OCF connector, you must configure the connection to the SQL Server data source.

The various steps involved in configuring the SQL Server data source connection setting are:

Provide Access

Go to the Access tab on the Settings page of your SQL Server data source, set the data source visibility using these options:

  • Public Data Source — The data source is visible to all users of the catalog.

  • Private Data Source — The data source is visible to the users allowed access to the data source by Data Source Admins.

You can add new Data Source Admin users in the Data Source Admins section.

Connect to Data Source

To connect to the data source, you must perform these steps:

Important

The Alation user interface displays standard configuration settings for credentials and connection information stored in the Alation database. If your organization has configured Azure KeyVault or AWS Secrets Manager to hold such information, the user interface will change to include the following buttons adjacent to the respective fields:

../../../_images/SnowflakeOCF_New_Vault_Button.png

By default, you see the user interface for Standard. In the case of Vault, instead of the actual credential information, you must select the source and provide the corresponding key. For details, see Configure Secrets for OCF Connector Settings.

Provide the JDBC URI

JDBC URI

Depending on the type of your SQL Server deployment, Alation may support the following authentication types:

  • Basic authentication

  • NTLM authentication

  • Kerberos authentication

  • SSL authentication

Refer to Overview for details on which authentication methods are supported.

When building the URI, include the required components depending on the authentication method.

Note

Starting in version 1.1.4.1957, the connector uses a SQL Server driver that assumes that the SSL mode is enabled by default unless the value encrypt=false is explicitly passed in the JDBC URI.

The only exception is the Azure SQL Managed Instance data source, where basic authentication will work with or without explicitly including the parameter encrypt=false into the URI.

JDBC URI for Basic/NTLM Authentication

URI Parameters

Parameter

Description

Hostname

Specify the host name or IP address of the instance.

Port

Specify the port number to use. If not specified, Alation uses the default port: 1433

Database Name (databaseName)

Optional. Specify the database name. If included, connection is initiated using the database name. Alation extracts from the specified database. If not specified, Alation extracts from all the databases that the service account has access to.

Encrypt

Set to false for an unencrypted connection. If not specified, the default value true is used indicating an SSL-enabled connection.

Instance Name (instanceName)

Specify if instance name. This parameter only applies when using SQL Server instances
Format
sqlserver://<hostname>:<port>;instanceName=<instance_name>;databaseName=<db_name>;encrypt=false
Example
sqlserver://10.13.47.231:1433;instanceName=EDWSQL;databaseName=SQL;encrypt=false

Note

For an Azure Managed Instance data source, both formats will work: sqlserver://<hostname or ip>:<port>;encrypt=false or sqlserver://<hostname or ip>:<port>.

JDBC URI for Kerberos Authentication

URI Parameters

Parameter

Description

Hostname

Specify the host name or IP address of the instance .

Port

Specify the port number to use. If not specified, Alation uses the default port: 1433

Server SPN (ServerSpn)

Optional. Specify the Service Principal Name. Provide the Server SPN value if you are using JavaKerberos for Kerberos as the authentication scheme.

Encrypt

Set to false for an unencrypted connection. If not specified, the default value true is used indicating an encrypted (SSL) connection. For example, encrypt=false.

Integrated Security (integratedSecurity)

Specify if the property is enabled for Microsoft Windows credentials. Set the value to true for NTLM or Kerberos authentication. For example, integratedSecurity=true.

Authentication Scheme (authenticationScheme)

Specify the type of authentication (integrated security) to use. For example, NTLM, JavaKerberos for Kerberos
Format
sqlserver://<hostname>:<port>;ServerSpn=<Server_SPN>;integratedSecurity=true;authenticationScheme=JavaKerberos;encrypt=false
Example
sqlserver://10.13.47.231:1433;ServerSpn=MSSQLSvc/EC2DRTS-7FA6452.al94278.test.local;integratedSecurity=true;authenticationScheme=JavaKerberos;encrypt=false

JDBC URI for SSL Authentication

URI Parameters

Parameter

Description

Hostname

Specify the host name or IP address of the instance .

Port

Specify the port number to use. If not specified, Alation uses the default port: 1433

Encrypt

Set to false for an unencrypted connection. If not specified, the default value true is used indicating an encrypted (SSL) connection

Host Name In Certificate

(hostNameInCertificate)

Specify host name in certificate if the certificate’s Common Name (CN) or Subject Alternative Name (SAN) is not same as Server Name. Additionally, If SSL is enabled and you are using AWS PrivateLink on the Alation instance, use hostNameInCertificate=CN

Note

To obtain the CN, run the below command and use the value after CN=:

openssl x509 -noout -subject -in <certificate_name>.crt

Format
sqlserver://<hostname>:<port>;encrypt=true;hostNameInCertificate=<Server_Name>

Note

NTLM + SSL Authentication is supported from connector version 1.3.7.7492

Applies from connector version 1.1.4:

Append the parameter encrypt=true to the URI.

Example
sqlserver://10.13.82.165:1433;encrypt=true;hostNameInCertificate=abc.corp.net

Provide the JDBC URI in Alation

To provide to the JDBC URI in the Alation UI, perform these steps:

From Alation version 2024.1 and connector version 1.5.0

  1. On the Settings page of your SQL Server data source, go to the General Settings tab.

  2. In the Provide the JDBC URI section, based on the type of authentication you want to use, enter the JDBC URI. For details, see Provide the JDBC URI .

    Note

    Click the View history icon to view the history of recent URI values provided by users, if any. The History: Recent URI Values list displays 100 URI entries. You can copy a URI from the list to reuse.

  3. Click Save.

Configure Authentication

Alation supports the following authentication types for the SQL Server data source:

  • Basic authentication (database username and password)

  • Kerberos authentication

  • LDAP authentication

  • SSL authentication

Basic Authentication

Basic authentication requires a service account username and password.

Configure Basic Authentication

From Alation version 2024.1 and connector version 1.5.0

  1. On the Settings page of SQL Server data source, go to the General Settings tab.

  2. In the Configure authentication step, click on the Basic tab.

  3. Provide the service account username and password.

  4. To use SSL with Basic authentication, turn on the Enable SSL toggle, provide the Truststore password, and upload the SSL certificate.

    Note

    • To use SSL authentication, ensure that you have provided the JDBC URI format for SSL authentication. See JDBC URI for SSL Authentication.

    • The SQL OCF connector supports .crt and .cer files types for SSL certificate.

  5. Click Save.

Kerberos Authentication

Alation supports Kerberos authentication with password.

From Alation version 2024.1 and connector version 1.5.0

  1. On the Settings page of SQL data source, go to the General Settings tab.

  2. In the Configure authentication step, click on the Kerberos with password tab.

  3. Provide the service account username, password, and upload the krb5.conf file using the Upload krb5.conf link.

  4. To use SSL with Basic authentication, turn on the Enable SSL toggle, provide the Truststore password, and upload the SSL certificate.

    Note

    • To use SSL authentication, ensure that you have provided the JDBC URI format for SSL authentication. See JDBC URI for SSL Authentication.

    • The SQL OCF connector supports .crt and .cer files types for SSL certificate.

  5. Click Save.

Test the Connection

The connection test checks database connectivity. Alation uses the JDBC URI to connect to the database and to confirm when the connection is established.

After specifying the JDBC URI and configuring authentication, test the connection.

To validate the network connectivity, go to General Settings > Test Connection on the Settings page of your SQL Server data source and click Test.

A dialog box appears confirming the status of the connection test.

Configure Additional Connection Settings

Apart from the mandatory configurations that you perform to connect to the data source in the General Settings tab, you can configure the following additional settings:

  • Configure Additional Data Source Connections

  • Disable Obfuscate Literals

  • Disable automatic lineage generation

Configure Additional Data Source Connections

Alation can associate objects in a data source with objects in another source in the catalog through lineage. For example, you can show lineage between your data source and BI sources that use its data.

Provide additional connection information for the data source to see lineage across multiple sources on the Lineage chart.

From Alation version 2024.1 and connector version 1.5.0.

To enter additional data source connection details, go to General Settings > Advanced settings of the Settings page of your SQL Server connector and enter the connection URL.

Use the following format: <host>:<port>

You can provide multiple values as a comma-separated list:

<host1>:<port1>,<host2>:<port2>

For example:

10.13.71.216:1541,sever.com:1542

For more details, see Configure Cross-Source Lineage.

Enable or Disable Obfuscate Literals

You can hide literal values from queries ingested with query log ingestion and displayed on the Queries tab of a schema and table catalog objects.

From Alation version 2024.1 and connector version 1.5.0.

Go to the General Settings tab and turn on or off the Obfuscate literals toggle under the Advanced settings section.

When enabled, literal values are substituted with placeholder values. Disable this option when you want literal values in queries to be visible to users.

By default, this option is disabled.

Configure Logging

To set the logging level for your SQL Server data source logs, perform these steps:

From Alation version 2024.1 and connector version 1.5.0.

  1. On the Settings page of your SQL Server data source, go to General Settings > Connector logs.

  2. Select a logging level for the connector logs and click Save.

    The available log levels are based on the Log4j framework.

You can view the connector logs in Admin Settings > Server Admin > Manage Connectors > SQL Server OCF connector.