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. 

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:

Connector Capabilities

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

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: 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.

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 Type	Select the Authentication type from the drop-down menu. (SQL server authentication)
   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
5. Click on the Validate button to validate the connection details.
6. 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. 

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

Connection Settings

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

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.

Additional Information 

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.

 

Copyright © 2023, OvalEdge LLC, Peachtree Corners GA USA