SQL Server

Integration model

Metadata extraction

For the extraction of metadata of an object, the methods offered by the JDBC driver are used, through which schema and table definitions are accessed.

It extracts the following attributes, which must be named the same in the attribute_definition table, in the name field, for them to appear in the template.

• catalog with the catalog value in the database

• schema with the schema value in the database

• physicalName and name with the same value, the name of the table

• path with the concatenation of the catalog, schema, and table values

• infrastructure with the selected value

• technology with the selected value

• zone with the selected value

When extracting metadata to create a dataset, the following attributes related to the fields of the requested resource will also be extracted to fill in its structure information:

• physicalName and name with the same value, the name of the field

• defaultValue with the default value set for the field

• fieldDataType with the data type assigned to the field, if set

• length with the length of the field, if set.

• incrementalField

• position with the position value occupied by the field

• precision with the precision value of the field, if set

• nullable indicating whether the field is nullable or not (boolean value)

• pk indicating whether the field is a primary key (boolean value)

• description the description of the dataset-field

The attributes to be created in Anjana must have the following types:

The plugin is capable of performing metadata extraction on the following types of elements:

Database table

Tables with special characters in the name

This technology allows characters such as “/” in names; if they are being used, the path-separator must be configured with a character other than “/”. See File extraction for more details.

Data sampling

Using the JDBC driver, a query with a record limit is executed on the fields defined in the dataset; additionally, the values of sensitive fields are replaced with asterisks.

Fields that are modified after the object is created in Anjana (that is, defined in the metadata but not yet incorporated into the physical structure) will appear as unavailable in the sampling.

Structure creation

The plugin allows creating physical structures provided the object is governed. When this occurs and the associated workflow is validated, the structure will be created at the indicated path of the dataset. Once created, it will not be modified even if new versions of the dataset are generated, unless a new path is specified.

Access management

The plugin allows managing access to the structures that are governed, through the use of roles and associating SELECT permissions on the structures to the role.

Object editing

The plugin allows managing the activation or deactivation of non-native entities, so that when a non-native entity is activated, the corresponding permissions will be granted on the tables, and when deactivated, the permissions will be removed.

Supported versions

Support from SQL Server 2016 to SQL Server 2019.

Required credentials

Metadata extraction

User or role with VIEW DEFINITION permissions on the tables or views from which the metadata is to be extracted.

It can also be applied directly to schemas or databases, and it will apply to everything they contain.

Data sampling

User or role with SELECT permissions on the tables or views for which a data sample is to be obtained.

It can also be applied directly to schemas or databases, and it will apply to everything they contain.

Structure creation

User with the following required permissions/roles on the catalogs, schemas, and tables to be governed.

CREATE TABLE

The names used to create resources in SQL Server are subject to the restrictions imposed by SQL Server itself for each of them.

Access management

User with the following required permissions on the catalogs, schemas, and tables to be governed.

• CREATE ROLE

• ALTER ANY ROLE

• ALTER ANY LOGIN (required at the master level)

• CONTROL (optional if the role ownership is transferred to a third party)

• SELECT ON OBJECT

ALTER ROLE anjana_service_rol ADD MEMBER anjana_service_user;

Access management via Azure AD

If active governance is to be performed with Azure AD, the following steps must be completed:

Create an APP in Azure AD to act as a service principal and generate a secret

Enable the “System assigned managed identity” option in the SQL server Identity settings

Allow Azure services to access the SQL server

Grant the SQL server identity permission to access the Azure AD directory

In SQL, grant permissions to the service principal of the APP used in the Azure AD plugin

-- LOGIN
CREATE USER [<app-name>] FROM EXTERNAL PROVIDER;

-- ADD PERMISSIONS TO [<app-name>]
ALTER ROLE anjana_service_rol ADD MEMBER [<app-name>];
GO

Object editing

When a non-native entity is activated or deactivated in Anjana, the plugin will grant or remove the permissions on the corresponding tables.

The connection user must have the following permissions on the catalogs, schemas, and tables to be governed.

• ALTER ANY ROLE

• SELECT ON OBJECT

• CONTROL (optional if the role ownership is transferred to a third party)

Special considerations:

The credential parameters are divided into two blocks and both cannot be defined at the same time:

• Url, user, and password are database connection credentials; they should be used when connecting to a SQL Server.

• ServerName, databaseName, principalId, and principalSecret are credentials for an Azure SQL Server.

For each configurable connection, Anjana interprets the following to know how to communicate with SQLServer:

“using-catalogs” and “using-schemas” determine the level from which the SQL Server is governed and how the paths of the structures to be governed from Anjana are interpreted; if both are false, only the default schema or the one chosen in the connection URL is shown. (EX: using-catalogs false and using-schemas true indicate that you want to govern all schemas you have access to, always within the same catalog or database)

“path-separator” indicates the separator used by Anjana for the path. (EX: If it is “/”, the employees table in the hr schema is expected to arrive from Anjana as hr/employees). The plugin transforms the path into a correct structure for SQL Server, so if it is not specified correctly, erroneous resources will be attempted, producing an error.

“sampleRows” indicates the number of rows retrieved for the data sampling functionality.

“imType” indicates the type of external identity manager used in addition to SQLServer; possible values are:

• EID for EntraID (formerly AzureAD)

• AD for Windows AD

• NONE to use pure SQLServer; this is the default

“imDomain” is used to indicate the domain of the identity manager to work with; it has no default value and is not necessary if “imType” has the value NONE.

When creating new roles in SQL Server to assign permissions on the tables to be governed with that role, “rolePrefix” is used to indicate the prefix to be applied to the role name. If no prefix is desired, the variable must be included in the YAML without a value.

In the process of creating a role and its permissions, there are a series of retries and waiting time between retries; to configure these retries, “azureCountRetry” and “azureWaitRetry” are used.

totplugin:
 sql:
   query-pattern:
     createRole: "CREATE ROLE {0}"
     existRole: "SELECT DATABASE_PRINCIPAL_ID({0})"
     grantSelect: "GRANT SELECT ON {0} TO {1}"
     deleteRole: "DROP ROLE {0}"
     revokeSelect: "REVOKE SELECT ON {0} FROM {1}"
     createFromExternalProvider: "CREATE USER [{0}] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [{1}]"
     deleteUser: "DROP USER IF EXISTS [{0}]"
     addMemberRole: "ALTER ROLE [{0}] ADD MEMBER {1}"
     dropMemberRole: "ALTER ROLE [{0}] DROP MEMBER {1}"

The queries executed during the governance cycle are shown above, with their default values.

• createRole: Used to create the role

• existRole: Checks for role existence for error control

• grantSelect: Grant read permissions on a table to a role

• deleteRole: Delete the role

• revokeSelect: Revoke read permissions on a table from a role

• createFromExternalProvider: Creation of a user with the name of the group in the AD associated with the governed table, which will be the identity assumed when accessing.

• deleteUser: Deletion of the user corresponding to the group name in the associated AD

• addMemberRole: Allows adding the created user to the role that was created.

• dropMemberRole: Removes the user from the role. The reverse operation of addMemberRole.

Encrypted connection

If you wish to connect to the plugin using encryption, the following properties must be added to the connection string: encrypt=true;trustServerCertificate=true

totplugin:
  connection:
    - name: dev
      technology:
        url: jdbc:sqlserver://rdbservice:1433;database=<db>;encrypt=true;trustServerCertificate=true

Depending on the server configuration, it may be necessary to change the values of the mentioned properties. For more information, consult the Microsoft documentation: Connection SQLServer

Available ImAri

• Azure

• Ldap

By default the plugin assumes it is connecting to an Azure AD and links the SQL Server user with Azure AD. If a different AD is to be used, the following configuration must be specified:

totplugin:
 connection:
    - name: dev
      technology:
        sql:
          query-pattern:
     createFromExternalProvider: "CREATE USER [DOMAIN\{0}] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [{1}]"

Where DOMAIN is the AD domain