Azure SQL Managed Instance Connector

Azure SQL Managed Instance Connector is a Relational Database Management System (RDBMS) based on Azure SQL Managed Instance. It allows users to organize data by handling, storing, modifying, and deleting it.

OvalEdge uses Java Database Connectivity (JDBC) to connect to Azure SQL Managed Instance, which allows it to crawl and profile data objects (Tables, Table Columns, etc.) and build Lineage.

The connector currently supports the following versions of Azure SQL Managed Instance:

Connector Capabilities

The following is the list of objects and data types supported by the Azure SQL Managed Instance 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

Notes:

• The JDBC driver is provided by default. If you need to change it, add Azure SQL Managed Instance client drivers to the OvalEdge Jar path to communicate with the Azure SQL Managed Instance 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.
  The .dll file must be copied to Apache Tomcat, /Catalina/. OvalEdge will provide this file during installation.

Service Account with Minimum Permission

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

Establish Environment Variables (Optional)

This section describes the settings or instructions you should know before 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.

For consistency, you might want to consider crawling the same schema in both stage and production environments. 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 connection

Complete the following steps to connect to the Azure SQL Managed Instance using the OvalEdge application.

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 Azure SQL Managed Instance. The Add Connector with Azure SQL Managed Instance-specific details pop-up window will then appear.

   Field Name	Description
   Connector Type	By default, the selected connection type is displayed as Azure SQL Managed Instance. The drop-down list allows you to change the connector type if necessary. The fields associated with the selected connection type are displayed based on the selection.
   Authentication*	Select the Authentication type from the drop-down list. (Azure SQL Managed Instance 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: 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. 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. By default, all the connectors will have a Base Connector License, which allows you to crawl and profile to obtain 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.
   Connector Name*	Enter the connection name; the connection name specified in the Connection Name textbox will refer to the Azure SQL Managed Instance connection in the OvalEdge application. Example: Azure SQL Managed Instance 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 understand that the new connector is established in an environment available at 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 Azure SQL Managed Instance.  Example: xxxx-sqlserver.xxxx4ijtzasl.xx-south-1.rds.amazonaws.com
   Port*	By default, the port number related to the Azure SQL Managed Instance 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 Azure SQL Managed Instance are displayed and not editable. com.microsoft.sqlserver.jdbc.SQLServerDriver
   Username*	To connect to the Azure SQL Managed Instance, you need a username. Enter the Service Account Name established to access the Azure SQL Managed Instance environment.  Note: Sometimes, the web browser auto-fills this field with the current OvalEdge user login. Please enter the name of the Azure SQL Managed Instance Service Account. Example: sampleuser
   Password*	Enter the Service Account password.
   Connection String	A connection string configures the Azure SQL Managed Instance connection object. Key-value pairs specify Azure SQL Managed Instance connections in a connection string.  Set the Connection string toggle button to get the details from the credentials provided automatically. 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 of the plugin that is running.
   Default Governance Roles*	You can select a specific user or a team from the governance roles (Steward, Custodian, Owner) that are assigned to manage the data asset.  Note: The drop-down list displays all the configurable roles (for a single user or a team) according to 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 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, then click 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 the connector's roles and 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 to 4, the connection is crawled. It will provide the last 4 changes that occurred in the connector's remote/source. 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 managing data movement between any source and destination easy. 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. 

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

Error Validation Details 

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

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

Connector Settings

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

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

The Crawling of Schema(s)

A Crawl/Profile option allows you to select the specific schemas for the following operations: Crawl, Crawl & Profile, Profile, or Profile Unprofiled. The defined run date and time are displayed to set for any scheduled crawlers and profilers.

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 the 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

Select Authentication as Azure Active Directory - 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 Azure SQL Managed Instance is available at no additional charge.
3. Can I use the driver to access Azure SQL Managed Instance from a Linux computer?
   Yes! You can use the driver to access Azure SQL Managed Instance from Linux, Unix, and other non-Windows platforms. To have Azure SQL Managed Instance 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 Azure SQL Managed Instance?
   The table below lists available authentication options.

Copyright © 2024, OvalEdge LLC, Peachtree Corners, GA, USA.