RDBMS

SQL Server Connector

SQL Server is a Relational Database Management System (RDBMS) based on Structured Query Language (SQL), which allows users to handle, store, modify, and delete data in an organized way.

OvalEdge uses Java Database Connectivity (JDBC) to connect to SQL Server that allows to crawl and profile data objects (Tables, Table Columns, etc.) and build Lineage. 

The connectivity to SQL Server is via the JDBC driver, which is included in the platform. 

SQLServerJDBC_arch

The connectivity to SQL Server is via the JDBC driver, which is included in the platform. 

The connector currently supports the following versions of SQL Server:

Edition

Version

Comments

Express

15.x

Supported

Web

 

Supported

Standard

 

Supported

Enterprise

 

Verified

Connector Capabilities

The following is the list of objects and data types supported by the SQL Server JDBC connector.

Functionality Supported Data Objects
Crawler Tables, Table Column, Views, Function, Stored 
procedures, Functions, Trigger
Roles, Users, Permissions, and Usage 
Statistics
Profiler

Table Profiling
Views Profiling
Column Profiling
Full Profiling
Sample Profiling

Supported Data types:
Bigint, Char, Date, Datetime, Datetime2, Decimal (P, S),
Float, Int, Money, Nchar, Ntext, Numeric (P, S), Nvarchar,
Real, Smalldatetime, Smallint, Smallmoney, Time, Tinyint,
Varchar

Lineage Table lineage
Column lineage
Lineage Sources
Query Insert
Update
Delete
Joins within database
Joins outside database
Aggregations
Group By
Order By

Prerequisites

The following are the prerequisites required for establishing a connection. 

  1. Driver Details 

  2. Service Account Permission

  3. Configure Environment Variables (Optional)

Driver Details

Drivers

version

Remark

Microsoft 

JDBC driver

7.4

Is JDBC 4.2 compliant, uses JDK 8.0

Download - JDBC Driver for SQL Server | Microsoft Docs

Note: The latest version is 9.2.

JTDS driver

1.3.1

jTDS - SQL Server and Sybase JDBC driver - Browse Files at SourceForge.net

This driver is used for supporting SQL Server JDBC Authentication on a Linux environment.

Note:

  • JDBC driver is provided by default. In case it needs to be changed, add SQL server client drivers into the OvalEdge Jar path to communicate to the SQL Server database. Check the Configuration section for further details on how to add the drivers to the jar path. 
  • Install supporting ‘.dll’ for Microsoft JDBC driver. 
    .dll file must be copied in the apache tomcat, /Catalina/. This file will be provided by OvalEdge during installation.

Service Account with Minimum Permission


The following are the minimum privileges required for you to crawl the database objects and profile sample data. 

Operation 

Access Permission

Connection Validation

SELECT

Crawl Schemas

SELECT

Crawl Tables

SELECT

Profile Schemas, Tables

SELECT

Lineage Building 

SELECT

View Source Code

View Definition

Establish Environment Variables (Optional)

This section describes the settings or instructions that you should be aware of prior to establishing a connection. If your environments have been configured, skip this step.

Configure Environment Names

The Environment Names allow you to select the environment configured for the specific connector from the dropdown list in the Add Connector pop-up window.
You might want to consider crawling the same schema in both stage and production environments for consistency. The typical environments for crawling are PROD, STG, or Temporary, and may also include QA or other environments. Additionally, crawling a temporary environment can be useful for schema comparisons, which can later be deleted, especially during application upgrade assistance. 

Steps to Configure the Environment
  1. Navigate to Administration System Settings
  2. Select the Connector tab.
  3. Find the Key name “connector.environment”.
  4. Enter the desired environment values (PROD, STG) in the value column. 
  5. Click ✔ to save. 

Establish a connector

To connect to the SQL server using the OvalEdge application, complete the following steps.

  1. Login to the OvalEdge application
  2. Navigate to Administration > Connector module.
  3. Click on the + icon(New Connector), and the Add Connector with Search Connector pop-up window is displayed.
  4. Select the connection type as SQL Server. The Add Connector with SQL Server-specific details pop-up window is displayed

    Fields

    Details

    Connector Type

    By default, the selected connection type is displayed as SQL Server.

    If required, the drop-down list allows you to change the connector type and based on the selection of the connection type, the fields associated with the selected connection type are displayed.

    Authentication*

    Select the Authentication type from the drop-down list. (SQL server authentication)

    Note: An SQL Server connection can be authenticated using SQL Server Authentication, Windows Authentication, or Azure Active Directory—Password. 

    The manage Connection pop-up window field will differ depending on the selected authentication type.

    Credential Manager*

    Select the option from the drop-down list, where you want to save your credentials:

    OE Credential Manager: SQL Server connection is configured with the basic Username and Password of the service account in real-time when OvalEdge establishes a connection to the SQL Server. Users need to add the credentials manually if the database option is selected.

    HashiCorp: The credentials are stored in the HashiCorp database server and fetched from HashiCorp to OvalEdge.  

    AWS Secrets Manager: The credentials are stored in the AWS Secrets Manager database server and fetched from the AWS Secrets Manager to OvalEdge.

    Azure Key Vault: Click here to view the complete information

    For more information on Credential Manager, refer to Credential Manager

    License Add-Ons

    OvalEdge supports various License Add-Ons based on the connector’s functionality requirements.

    All the connectors will have a Base Connector License by default that allows you to crawl and profile to obtain the metadata and statistical information from a data source. 

    • Select the Auto Lineage Add-On license that enables the automatic construction of the Lineage of data objects for a connector with the Lineage feature. 

    • Select the Data Quality Add-On license to identify, report, and resolve the data quality issues for a connector whose data supports data quality, using DQ Rules/functions, Anomaly detection, Reports, and more.

    • Select the Data Access Add-On license that will enforce connector access via OvalEdge with the Remote Data Access Management (RDAM) feature enabled

    Connector Name*

    Enter the name of the connection, the connection name specified in the Connection Name textbox will be a reference to the SQL server connection in the OvalEdge application.

    Example: SQL Server Connection1

    Connector Environment

    The environment drop-down list allows you to select the environment configured for the connector from the dropdown list. For example, PROD, or STG.

    The purpose of the environment field is to help you to understand that the new connector is established in an environment available at the  Production, STG, and QA.

    Note: The steps to set up environment variables in explained in the Prerequisites section.

    Server*

    Enter the fully qualified server name or IP address related to the SQL server. 

    Example: xxxx-sqlserver.xxxx4ijtzasl.xx-south-1.rds.amazonaws.com

    Port*

    By default, the port number related to the SQL server is displayed. If needed, the port number can be modified.

    Database*

    Enter the name of the database that you want to crawl.

    Example: abcd2014

    Driver*

    By default, the driver details associated with the SQL server are displayed, and it is not editable.

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    Username*

    A username is required to connect to the SQL server. Enter the Service Account Name established to access the SQL Server environment. 

    Note: Sometimes, this field is autofill filled by the web browser with the current OvalEdge user login. Please enter the SQL Server Service Account name.

    Example: sampleuser

    Password*

    Enter the Service Account password. 

    Connection String

    A connection string configures the SQL Server connection object. Key-value pairs specify SQL Server connections in a connection string. 

    Set the Connection string toggle button to automatically get the details from the credentials provided. Alternatively, you can manually enter the string.

    Format:  jdbc:sqlserver://{server}:1433;database={sid}

    Plugin Server

    Enter the Server Name if the connection is running as the plugin.

    Plugin Port

    Enter the port number on which the plugin is running.

    Default Governance Roles*

    You can select a specific user or a  team from the governance roles (Steward, Custodian, Owner) that get assigned for managing the data asset. 

    Note: The drop-down list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security | Governance Roles section. 

    Admin Roles*

    Select the required admin roles for this connector.

    • To add Integration Admin Roles, search for or select one or more roles from the Integration Admin options, and then click on the Apply button. 

      The responsibility of the Integration Admin includes configuring crawling and profiling settings for the connector, as well as deleting connectors, schemas, or data objects.

    • To add Security and Governance Admin roles, search for or select one or more roles from the list, and then click on the Apply button. 

      The security and Governance Admin is responsible for:

      • Configure role permissions for the connector and its associated data objects.

      • Add admins to set permissions for roles on the connector and its associated data objects.

      • Update governance roles.

      • Create custom fields.

      • Develop Service Request templates for the connector.

      • Create Approval workflows for the templates.

    No of archive objects*

    It is the number of last modifications made in the metadata data of a Remote/source. By default, the number of archive objects is set to disable mode. Click on the Archive toggle button and enter the number of objects you wish to archive.

    For example, if a user updates the count as 4, then the connection is crawled. It will provide the last 4 changes that occurred in the remote/source of the connector. Users can view these changes in the ‘version’ column of the ‘Metadata Changes’ module.

    Select Bridge*

    With the OvalEdge Bridge component, any cloud-hosted server can connect with any on-premise or public cloud data sources without modifying firewall rules. A bridge provides real-time control that makes it easy to manage data movement between any source and destination. For more information, refer to Bridge Overview.

    For more information, refer to Bridge Overview

    1. Click on the Validate button to validate the connection details.
    2. Click on the Save button to save the connection.  Alternatively, you can also directly click on the Save & Configure button that displays the Connection Settings pop-up window to configure the settings for the selected Connector. The Save & Configure button is displayed only for the Connectors for which the settings configuration is required. 

    Note:  You can either save the connection details first, or you can validate the connection first and then save it. 

    Note: An SQL server connection can be authenticated using SQL Server Authentication, Windows Authentication, or Azure Active Directory - Password. The field for the manage connection pop-up window will differ as per the selected authentication type.

    Error Validation Details 

    The following are the possible error messages encountered during the validation. 

    S.No.

    Error Messages

    Description

    1

    Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Login failed for user 'ovaledge'. ClientConnectionId:2d7324ab-51e3-432e-995c-74a000910446)

    Incorrect Password

    2

    Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Login failed for user 'ovaled'. ClientConnectionId:f06bb2e4-da88-4d18-9dc6-222d67cedee5)

    Invalid User Name

    3

    Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host oval-sqlserver.csklygkwz3dx.us-east-1.rd, port 1433 has failed. Error: "oval-sqlserver.csklygkwz3dx.us-east-1.rd. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)

    Incorrect Server


    Note: If you have any issues creating a connection, please contact your assigned OvalEdge Customer Success Management (CSM) team.

    Connection Settings

    Once the connection is validated successfully, various settings are provided to retrieve and display the information from the data source.  

    Connection Settings

    Descriptions

    Crawler Crawler settings are configured to connect to a data source and collect and catalog all the data elements in the form of metadata. 
    Profiler  The process of gathering statistics and informative summaries about the connected data source(s). Statistics can help assess the quality of data sources before using them for analysis. Profiling is always optional; crawling can be run without profiling also. 
    Data Access

    The Data Access Authorization is included in the crawler-specific connector settings to ensure that the right user is accessing the query sheet and queries in the data catalog. Here the system validates the user credentials and allows that particular user to access the query sheet and queries in the data catalog.

    Query Policy

    It restricts the use of the selected query types based on your role.

    Access Instruction

    Access Instruction allows the data owner to instruct others on using the objects in the application.

    Business Glossary Settings The Business Glossary setting provides flexibility and control over how they view and manage term association within the context of a business glossary at the connector level.

    Anomaly Detection Settings

    Anomaly Detection Settings enable users to configure anomaly detection preferences at the connector level. By default, the configuration aligns with global settings set in System Settings and cannot be modified.

    Others

    The Send Metadata Changes Notifications option sets a change notification about the metadata changes of the data objects.

    • Users can use the toggle button to set the Default Governance Roles (Steward, Owner Custodian, etc.) 
    • The user can select the role and team from the drop-down list to receive the notification.

    Note: For more information, refer to the  Connector Settings.

    The Crawling of Schema(s)

    You can use the Crawl/Profile option, which allows you to select the specific schemas for the following operations: crawl, profile, crawl & profile, or profile unprofiled. For any scheduled crawlers and profilers, the defined run date and time are displayed to set. 
    1. Navigate to the Connectors page, and click on the Crawl/Profile option.
    2. Select the required Schema(s).
    3. Click on the Run button that gathers all metadata from the connected source into OvalEdge Data Catalog.

    Note: For more information on Scheduling, refer to Scheduling Connector

    Additional Information 

    If the Authentication type is Windows Authentication, enter the following:

    Select Authentication as Windows Authentication

    Field Name

    Description

    OvalEdge Installed Environment

    Select from the drop-down list:

    • Linux/Unix
    • Windows

                                                        Linux/Unix

    Server*

    Enter the fully qualified server name or IP address related to the SQL Server. 

    Example: xxxx-sqlserver.xxxx4ijtzasl.xx-south-1.rds.amazonaws.com

    Port*

    By default, the port number related to the SQL server is displayed. If needed, the port number can be modified.

    Database*

    Enter the name of the database that you want to crawl.

    Example: abcd2014

    Domain

    Enter domain name

    Driver*

    The driver details associated with the SQL server are displayed and not editable by default.

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    Username*

    A username is required to connect to the SQL server. Enter the Service Account Name established to access the SQL Server environment. 

    Note: Sometimes, the web browser auto-fills this field with the current OvalEdge user login. Please enter the SQL Server Service Account name.

    Example: sampleuser

    Password*

    Enter the Service Account password. 

    Windows

    Server*

    Enter the fully qualified server name or IP address related to the SQL Server. 

    Example: xxxx-sqlserver.xxxx4ijtzasl.xx-south-1.rds.amazonaws.com

    Port*

    By default, the port number related to the SQL server is displayed. However, the port number can be modified if needed.

    Database*

    Enter the name of the database that you want to crawl.

    Example: abcd2014

    Driver*

    The driver details associated with the SQL server are displayed and not editable by default.

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    Select Authentication as Azure Active Directory - Password

    Field Name

    Description

    Server*

    Enter the fully qualified server name or IP address related to the SQL Server. 

    Example: xxxx-sqlserver.xxxx4ijtzasl.xx-south-1.rds.amazonaws.com

    Port*

    By default, the port number related to the SQL server is displayed. If needed, the port number can be modified.

    Database*

    Enter the name of the database that you want to crawl.

    Example: abcd2014

    Driver*

    By default, the driver details associated with the SQL server are displayed and not editable.

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    Username*

    A username is required to connect to the SQL server. Enter the Service Account Name established to access the SQL Server environment. 

    Note: Sometimes, the web browser auto-fills this field with the current OvalEdge user login. Please enter the SQL Server Service Account name.

    Example: sampleuser

    Password*

    Enter the Service Account password. 

    FAQ's

    1.  What should I know when upgrading my driver?
    The Microsoft JDBC Driver 7.4 supports the JDBC 4.2, and 4.3 (partially) specifications and includes three JAR class libraries in the installation package as follows:

    JAR JDBC Specification JDK Version
    mssql-jdbc-7.4.1.jre8.jar JDBC 4.2 JDK 8.0


    2.  How much does the driver cost?

    The Microsoft JDBC Driver for SQL Server is available at no additional charge.

    3.  Can I use the driver to access Microsoft SQL Server from a Linux computer?
    Yes! You can use the driver to access SQL Server from Linux, Unix, and other non-Windows platforms. For having SQL Server JDBC Authentication on a Linux environment, we use jtds 1.3.1 driver (NTLM).

    4.  Which authentication types are supported by the Microsoft JDBC Driver for SQL Server?
    The table below lists available authentication options.

    Platform Authentication
    Non-Windows SQL Server, NTLM, Azure Active Directory Authentication, Pure Java Kerberos 
    Windows SQL Server, Kerberos with NTLM backup, NTLM, Azure Active Directory Authentication, Pure Java Kerberos 

    Copyright © 2024, OvalEdge LLC, Peachtree Corners GA USA