RDBMS

Azure SQL Managed Instance Connector

The Azure SQL Managed Instance connector is used to pull the metadata existing in the Azure SQL Managed Instance database and helps the users to crawl the metadata and profile the sample data and build lineage to view the movement of the crawled data displaying the relationship between the objects and its profile statistics.

Crawling:  Crawling is a process of collecting information about data from various data sources like on-premise and cloud databases, Hadoop, visualization software, and file systems. When an OvalEdge crawler connects to a data source, it collects and catalogs all the data elements (i.e., metadata) and stores it in the OvalEdge data repository. OvalEdge crawlers can be scheduled to scan the databases regularly.

Data Sources: OvalEdge crawler integrates with various data sources to help the users to extract metadata and build a data catalog. In this document, you can see how to make a connection to your Azure SQL Manager Instance and crawl the Views, Stored Procedures, and Functions.

Connect to the Data: Before you can crawl and build a data catalog, you must first connect to your data. OvalEdge requires users to configure a separate connection for each data source type. The users must enter the source credentials for each type of connectivity. Once a data connection is made, a simple click of the Crawl button starts the crawling process.       

Prerequisites

The following are prerequisites for connecting to the Azure SQL Managed Instance.

The APIs/ drivers used by the connector are given below:

Driver / API

Version

Details

Drivers

Microsoft JDBC driver 8.4.1

Is JDBC 4.2 compliant, uses JDK 8.0

User Permission

An admin/service account for crawling and building lineage. The minimum privileges required are:

Operation

Access Permission

Connection validate

Read

Crawl datasets

Read

 

Technical Specifications

The following are the connector capabilities mentioned below:

Crawling

Feature Supported Objects Remarks
Crawling Tables -
Table columns

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

Views -
Stored procedures -
Functions -

Profiling

Feature Supported Objects Remarks
Table Profiling Row count, Columns count, View sample data -
View Profiling Row count, Columns count, View sample data View is treated as a table for profiling purposes
Column Profiling Min, Max, Null count, distinct, top 50 values -
Full Profiling Supported -
Sample Profiling Supported -

Lineage Building

Lineage Entities Details
Table lineage Supported
Column lineage Supported
Lineage Sources Stored procedures, functions, triggers, views, SQL queries (from Query Sheet)

Querying

Operation Details
Select Supported
Insert Not supported, by default.
Update Not supported, by default.
Delete Not supported, by default.
Joins within database Supported
Joins outside database Not supported
Aggregations Supported
Group By Supported
Order By Supported

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.

Connection Details

To connect to the Azure SQL Manager Instance using the OvalEdge application, complete the following steps.

  1. Log in to the OvalEdge application.
  2. Navigate to Administration > Connectors.
  3. Click on the + icon, and the Add Connection with Search Connector pop-up window is displayed. 
  4. Select the connection type as Azure SQL Managed Instance. The Add Connector with Azure SQL Managed Instance specific details pop-up window is displayed.



    Field Name

    Description

    Connector Type

    Azure SQL Managed Instance

    Authentication

    You can choose SQL Server Authentication, Windows Authentication or Microsoft Entra ID (previously, Azure Active Directory) - Password

    Note: However, it's important to note that if you opt for Windows Authentication, you need to select the Environment as Linux/Unix or windows based on the requirement

    Credential Manager

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

    OE Credential Manager: Azure SQL Managed Instance connection is configured with the basic Username and Password of the service account in real-time when OvalEdge establishes a connection to the Azure SQL Managed Instance database. Users need to add the credentials manually if the OE Credential Manager 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.

    For more information refer to Azure Key Vault

    For more information on Credential Manager, refer to Credential Manager

    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.

    Connector Name*

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

    Connector Environment

    The environment drop-down menu allows you to select the environment configured for the connector from the drop-down 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 are explained in the prerequisite section.

    Server*

    Database instance URL (on-premises/cloud-based)

    Example: 34.54.23.43

    Port*

    1433

    Database*

    Name of the database to connect.

    Driver

    JDBC driver name for Azure SQL Managed Instance server. It will be auto-populated.

    Username*

    User/Service account login credential

    Password*

    Password

    Connection String

    Azure SQL Managed Instance 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.

    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*

    Users 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 Archived Objects*

    The number of archive objects indicates the number of recent metadata modifications made to a dataset at a remote/source location. By default, the archive objects feature is deactivated. However, users may enable it by clicking the Archive toggle button and specifying the number of objects they 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.

    For more information, refer to Bridge Overview

Connection Settings

    Crawler

    Property

    Description

    Remarks

    Crawler Options

    Tables, views & columns

    By default, it is selected true

    Relationships

    Need to select for crawl relationships

    Crawler rules

    Default includes tables regex has  regex = (.*), which can crawl all the tables

     

     Profiler

    Property

    Description

    Remarks

    Profile Options

    Tables and columns

    By default, it is selected true

    Profile Rules

    Views and columns

    By default, it is selected true

    Default includes tables regex has  regex = (.*), which can profile all the tables

    Profile Rules