Azure SQL

In the Data Sources window, click the Create Data Source button and select the Azure SQL option to create a data source. The user can either Create New Connection, or Use Existing Connection to connect to the Azure SQL data source.

Create New Connection

Microsoft JDBC driver is used for SQL server to connect to Azure SQL database. To create a new connection, enter the following parameters:

Click Test Connection to check if the connection created is working.

Note: Whenever you create an Azure data source connection in Torch, the IP address of the Torch host machine must be added in the firewall rules of Azure cloud, i.e., the IP address must be Whitelisted. If the IP address is not whitelisted then, the following error message is displayed:

Use Secret Manager

AWS Secrets Manager is a secrets management service that helps you protect access to your applications, services, and IT resources. This service enables you to easily rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle. We are enabling torch to make use of AWS Secrets Manager to store your secrets. Acceldata provides the option to store integration credentials in your AWS account using Secrets Manager.

Follow the below steps to create secrets in AWS:

1. Login to your AWS account.
2. Search for Secrets Manager.
3. Click Store a new secret.

4. Select Other type of secrets and add a key with a value representing the actual secret value like a password of a database user and click Next.

5. Give the secret a meaningful name, description and add any tags. Then click Next.

How do I enable Torch to read from AWS Secret Manager?

While Deploying Data Plane or Complete Installation, you are provided with an option to configure secrets manager configuration

1. Under Secret Keys Configuration > Click the Enable Key Management checkbox.

2. Upload the following secret manager configuration file in JSON format:

[

​

  {

​

    "name": "<Name to identify the secret manager to use in torch>",

​

    "type": "AWS_SECRETS_MANAGER",

​

    "details": {

​

      "secretName": "<Secret Name which is created in AWS Secret Manager>",

​

      "accessKey": "<AWS access Key>",

​

      "secretKey": "<AWS Secret Key>",

​

      "region": "<AWS Region where secret is created>"

​

    }

​

  }

​

]

The above configuration file is a JSON array, where each element represents a secret configuration. There are 2 ways in which Torch can authenticate itself to AWS Secrets Manager.

i) Providing the accessKey details of an IAM User who has the permission to read the Secret

ii) Leaving the accessKey and secretKey fields as empty and Torch assumes that there is a IAM Role attached to the NodeGroup of EKS Cluster which has the permission to read the Secret(EC2InstanceProfile).

Refer to the following document for IAM Policies to be attached to the NodeGroups https://docs.aws.amazon.com/mediaconnect/latest/ug/iam-policy-examples-asm-secrets.html.

Use Existing Connection

To use an existing connection, click Use Existing Connection tab to select an existing connection from the drop-down list.

Data Source Configuration

• Specify a name and description for the data source configuration.
• Enter the name of database to crawl.
• Enter the name of the Schema to crawl. Click , to enter more schema.
• Click Next.

Crawler Execution Schedule

Click Use automated execution checkbox to schedule a time for Torch to run metadata crawlers to fetch meta information of the data source. To schedule, select any tag like minute, hour, day, week, month, or year.

Click Save button. The data source is saved.

User Permissions

For Microsoft SQL Server, there are few additional permissions to be granted to non-admin user for enabling Torch to crawl metadata, fetch query logs as well as run Profiling, Data Quality, Reconciliation jobs on assets.

Note: For admin user, there are no additional steps to be performed with respect to permissions.

To enable the user to crawl metadata, you must grant the following additional permissions :

GRANT SELECT on SCHEMA::<schema name> to <user>;

For enabling Torch Query Analyzer to fetch query logs from a SQL Server on Amazon RDS, first the user must configure the SQL Server instance for storing the query logs.

Configuring SQL Server for Query log generation

1. For RDS based instances, additional steps are required from RDS console for enabling auditing. The steps are listed here.
2. On the SQL Server instance side audit policies must be configured to capture SQL statements generated on the data source side. The auditing can be configured in the following ways:

Create Server audit specification -

USE master

GO

CREATE SERVER AUDIT [Torch-Demo-DDL]

TO FILE ( FILEPATH ='D:\rdsdbdata\SQLAudit' , -- location is fixed for RDS instance

MAXSIZE =  3 MB );  -- file size can vary from

Create a Database level audit specification for the database concerned -

USE <Database Name>

GO

CREATE DATABASE AUDIT SPECIFICATION [DBSpecificationDDL]

FOR SERVER AUDIT [Torch-Demo-DDL]

ADD (SCHEMA_OBJECT_CHANGE_GROUP)

WITH (STATE = ON)

GO

Enable the server audit specification -

USE master

GO

ALTER SERVER AUDIT [Torch-Demo-DDL] 

WITH (STATE = ON) ;  

GO

Create Server audit specification -

USE master

GO

CREATE SERVER AUDIT [Torch-Demo-DML-DB]

TO FILE ( FILEPATH ='D:\rdsdbdata\SQLAudit\' ,

MAXSIZE =  3 MB );

Create a Database level audit specification for the database concerned -

USE <Database Name>

GO

CREATE DATABASE AUDIT SPECIFICATION [DbSpecificationDML-DB]

FOR SERVER AUDIT [Torch-Demo-DML-DB]

ADD (INSERT ON DATABASE::[<Database Name>] BY [public]),

ADD (UPDATE ON DATABASE::[<Database Name>] BY [public]),

ADD (DELETE ON DATABASE::[<Database Name>] BY [public]),

ADD (EXECUTE ON DATABASE::[<Database Name>] BY [public]),

ADD (SELECT ON DATABASE::[<Database Name>] BY [public]),

ADD (RECEIVE ON DATABASE::[<Database Name>] BY [public]),

ADD (REFERENCES ON DATABASE::[<Database Name>] BY [public])

WITH (STATE = ON)

GO

Enable the server audit specification -

USE master

GO

ALTER SERVER AUDIT [Torch-Demo-DML-DB] 

WITH (STATE = ON) ;  

GO

Additional Permissions

USE [msdb]

GO

grant select on msdb.dbo.rds_fn_get_audit_file to Acceldata; -- Acceldata is the login of the user