Adding a SQL Server Connection¶

Prerequisites¶

• A user with sufficient permissions is required to establish a connection with Microsoft SQL Server.
• Zeenea traffic flows towards SQL Server must be open.
• The only authentication mode supported by this connector requires the user's username and password.

Supported Versions¶

The SQL Server connector was tested with the SQL Server 2019 and is compatible with all versions of the software. It is compatible with the Azure versions of the Microsoft service as well as with the RDS versions of the Amazon Cloud Service.

Installing the Plugin¶

From version 54 of the scanner, the SQL Server connector is presented as a plugin.

The plugin can be downloaded here and requires a scanner version 64 or later: Zeenea Connector Downloads.

For more information on how to install a plugin, please refer to the following article: Installing and Configuring Connectors as a Plugin.

Version 65 and later¶

Integrated authentication is available for Windows. It requires configuration, which is carried out automatically when the Windows service is declared, provided the plugin has been installed beforehand.

Each time the scanner or plugin is updated, you must update the service using the same installation procedure.

Declaring the Connection¶

Connectors are created and configured through a dedicated configuration file located in the /connections folder of the relevant scanner.

For more information about managing connections, see Managing Connections.

To establish a connection with an SQL Server instance, fill in the following parameters in the dedicated configuration file:

User Permissions¶

To collect metadata, the technical account must have permissions to access and read databases that need to be cataloged.

The connector uses system views sys.*. To extract metadata, the user must have read-only access to the following tables and views:

• sys.objects
• sys.schemas
• sys.columns
• sys.types
• sys.extended_properties

Information about primary and foreign keys is collected using the following procedures:

• sp_pkeys
• sp_fkeys

To collect this information, the Zeenea user defined in the connection configuration must be able to connect to the selected databases and must be granted the following permission:

grant VIEW ANY DEFINITION to zeenea;

If the lineage feature is enabled, the user must have read access to the following tables:

• sys.sql_expression_dependencies
• sys.objects

If the data profiling feature is enabled, the user must have read access to impacted tables. Otherwise, this permission is not required.

Rich Filters¶

Since version 47 of the scanner, the SQL Server connector supports rich filter feature in its configuration.

For more information, see Filters.

Data Extraction¶

To extract information, the connector runs requests on views from the sys schema.

Collected Metadata¶

Inventory¶

Will collect the list of tables and views accessible by the user.

Lineage¶

From version 47 of the scanner, the connector has the lineage feature for views. This feature allows you to automatically recreate the lineage in your catalog of the tables that were used to build the view.

Dataset¶

A dataset can be a table or a view.

• Name
• Source Description
• Technical Data:
  • Schema
  • Table
  • Rows
  • Type:
    • USER_TABLE
    • VIEW

Fields¶

Table or view field.

• Name
• Source Description: Not supported
• Type
• Can be null: Depending on the field settings
• Multivalued: Not supported. Default value FALSE.
• Primary Key: Depending on the field "Primary Key" attribute
• Technical Data:
  • Technical Name
  • Native type: Field native type

Data Process¶

A data process represents the request to build a view.

• Name: CREATEVIEW "view-name"

Data Profiling¶

The statistical profiles feature, also known as Data Profiling, is available for this connector. The impact of this feature must be evaluated before activating it on any of your connections. For more information about the resulting statistics, see Data Profiling.

To activate this feature, read access to the target tables is required. For SQL Server technologies, the connector executes the following request to get a data sample:

SELECT COUNT(*) AS result FROM tableName

The request above defines the number of rows in the table tableName.

SELECT
   field1, field2
   FROM tableName
   TABLESAMPLE (linesPercentage)

The request above collects a data sample for each field where the feature is activated through the studio (field1, field2). The limit is 10.000 lines (linesPercentage parameter) deduced from a calculation with the number of rows set in the previous request.

These requests will be executed, whether manually, in case of user action directly on the admin portal, or periodically according to the parameter collect-fingerprint from the application.conf file, as described in Zeenea Scanner Setup.

Unique Identifier Keys¶

Each object in the catalog is associated with a unique identifier key. When the object is imported from an external system, the key is generated and provided by the connector.

For more information about identifier keys, see Identification Keys.