ODBC SQL Server

SQL Server ODBC Connector is a Relational Database Management system (RDBMS) that consists of a multithreaded SQL server. It supports different back ends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces.

In the OvalEdge application, ODBC Plugin (provided by OvalEdge along with the application) allows users to crawl and profile the datasets such as Tables, Columns, Views, Procedures, and Synonyms and helps build the data catalog and lineage.

ODBCsqlserver_arch

Connector Capabilities

Functionality	Descriptions
Crawling	Table, Table Columns, Views, Function, Procedure, Synonym Supported Data Types: Bigint, Char, Date, Datetime, Datetime2, Decimal (P, S), Float, Int, Money, Nchar, Ntext, Numeric (P, S), Nvarchar, Real, Smalldatetime, Smallint, Small money, Time, Tinyint, Varchar.
Profiling	Tables, Table Columns.
Query Execution	Select

Note: By default, the service account provided for the connector will be used for any query operations. If the service account has write privileges, then Insert / Update / Delete queries can be executed.

Prerequisites

The following are prerequisites required for the connection of the ODBC SQL Server database.

API Details

S. No.	API	Description
1	API	An additional .NET project has to be deployed wherever this connector is given to connect with SQL Server.

Note: Running of .NET Project is mandatory to connect with SQL Server via ODBC APIs.

Service Account with Minimum Read Permissions

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

Operation	Minimum Access Permission
Connection Validation	SELECT
Crawl Table/Table column	SELECT
Profile Table/Table column	SELECT
Query Execution	SELECT

Establish Environment Variables (Optional)

This section describes the settings or instructions that users 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 users 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

Navigate to Administration | Configuration.
Select the Connector tab.
Find the Key name “connector.environment”.
Enter the desired environment values (PROD, STG) in the value column.
Click ✔ to save.

Establish a Connection

Log into the OvalEdge application.
In the left menu, click on the Administration module name, and click on the Connectors sub-module name. The Connectors Information page is displayed.
Click on + New Connector. The Add Connector pop-up window is displayed.
Select the connection type as ODBC SQL Server. The Add Connection with ODBC SQL Server-specific details is displayed.

Fields	Attributes
Connector Type	By default, the selected connection type ‘OdbcSqlServer’ is displayed. If required, the dropdown menu allows you to change the connector type.
License Add-ons*	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 datasource. OvalEdge supports various License Add-Ons based on the connector’s functionality requirements. 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 Remote Data Access Management (RDAM) feature enabled
Connection Name*	Enter a connection name for the ODBC SQL Server connection. You must specify a reference name for the easy identification of the ODBC SQL Server connection in OvalEdge easily. Example: ODBC SQL_Connection_DB1
Environment	The environment dropdown menu allows you to select the environment configured for the connector from the dropdown list. For example, PROD, or STG (based on the configured items in the OvalEdge configuration for the connector.environment). The purpose of the environment field is to help you identify which connector is connecting what type of system environment (Production, STG, or QA). Note: The steps to set up environment variables in explained in the prerequisite section.
Server*	Enter the fully qualified server name or IP address related to the ODBC SQL server. Example: 12.34.56.88
Port*	Port number 80 is the default port used by ODBC SQL Server when connecting to a server over the internet.
Database*	By default, the driver details associated with the ODBC SQL server are displayed and it is not editable.
Driver*	By default, the driver details associated with the ODBC SQL server are displayed and it is not editable.
Username*	Enter the Service Account to access the ODBC SQL Server. Note: By default, the application Username and password will be displayed in place of the Username and Password. However, the user needs to clear the details and enter the service account credentials available in the ODBC SQL server.
Password*	Enter the Service Account to access the ODBC SQL Server.
Connection String	Set the Connection string toggle button to automatic to get the details automatically from the credentials provided. Alternatively, you can manually enter the string. Example: jdbc:sqlserver://{server}:12;database={sid} Example:dbc:SQL Server ODBC://oval.cvbnxxcvvwcraues.us-east-2.SQL Server ODBC.amazonaws.com:3306;database=customer
Plugin Server	Provide the server name if you are running this as a plugin.
Plugin Port	Provide the port number on which the plugin is running.
Default Governance Roles*	The admin will select a specific user or a team from the governance roles (Steward, Custodian, Owner) that get assigned to the data asset. The dropdown list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security>Governance Roles
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 dataset at 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.
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

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.

Note: * (asterisk) indicates the mandatory field required to establish a connection. Once all the parameters are entered, the user can validate the details and save the connection that will get displayed on the Connector Home page.

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

Connection Validation Error

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

S.No.	Error Messages	Description
1	Error while getting Access Token::13.58.: Name or service not known	Invalid Server
2	Error while getting Access Token::connect timed out	Incorrect Port, invalid Database/Username, incorrect password

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

Connector Settings

Once the connection is established successfully, various settings are provided to fetch and analyze the information from the data source.

Connection Settings	Description
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	Profiler is the process of gathering statistics and informative summaries about the connected data source(s). Statistics can help assess the data source's quality before using it in an analysis. Profiling is always optional; crawling can be run without profiling also.
Data Access	Data Access (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 user to access the query sheet and queries in the data catalog. It includes: (i) OvalEdge follows OvalEdge data permissions (ii) OvalEdge follows Remote system permissions
Query Policies	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.
Others	The Send Metadata Changes Notifications option is used to set the change notification about the metadata changes of the data objects. You can use the toggle button to set the Default Governance Roles (Steward, Owner Custodian, etc.) From the drop-down menu, you can select the role and team to receive the notification of metadata changes.

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.

Navigate to the Connectors page, and click on the Crawl/Profile option.
Select the required Schema(s).
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