Azure SQL

This is a Logstash Azure SQL filter plug-in for IBM Guardium® Data Protection Universal Connector.

Before you begin

See the Supported data source matrix to get information about the supported Guardium Data Protection and Guardium Data Security Center versions, database version, and environment needed for the plug-in.

AzureSQL-Guardium Logstash filter plug-in is automatically available with Guardium Data Protection versions 12.x releases.

Download the mssql-jdbc-7.4.1.jre8 - mssql-jdbc JAR file with all dependencies.

Limitations:
  • The azureSQL plug-in does not support IPV6.
  • The azureSQL auditing does not audit authentication failure(Login Failed) operations.
  • AzureSQL audit-record does not audit serverIP so that serverIp is hardcoded to "0.0.0.0".
  • The following important field might not be mapped with AzureSQL audit logs. - OS USER: Not Available with audit logs.
  • create user with password" operation is not audited by this plug-in.
  • AzureSQL auditing does not audit operations perform by "Beekeeper Studio Tools".

Enabling and viewing audit logs

Procedure

  1. Click Main menu > Resource groups and click a Resource Group.
  2. Select your SQL Server.
  3. Click Show Firewall setting, then click Add Client IP and enter the Client IP address.
  4. Add the Public IP address of the Guardium system where it is required to capture traffic.
    Use the following command to get the Public IP address of the Guardium system.
    `curl ipinfo.io/ip`
  5. Click Save.
  6. To enable auditing, complete the following steps.
    1. Select your SQL Database.
    2. In the search bar, type auditing.
    3. In the Auditing page, click Enable Azure SQL Auditing.
    4. Select Storage in the Audit log destination Check box.
    5. Select your Storage account.
    6. Click Advanced properties > Retention (Days) .
    7. Click Save.
  7. To view the audit logs, use the following query and update your storage-account-name, server_instance_name, DB-NAME in Query. 
    SELECT event_time,succeeded,session_id,database_name,client_ip,server_principal_name,application_name,statement,server_instance_name,host_name,DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) AS updatedeventtime,additional_information  
FROM sys.fn_get_audit_file('https://<storage-account-name>.blob.core.windows.net/sqldbauditlogs/<server_instance_name>/<DB-NAME>', DEFAULT, DEFAULT) where action_id='BCM' and statement not like '%xproc%' and statement not like '%SPID%' and statement not like '%DEADLOCK_PRIORITY%' and application_name not like '%Microsoft SQL Server Management Studio - Transact-SQL IntelliSense%' and DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) > 0;

Connecting to AzureSQL Database

Procedure

  1. Start the SQL Server Management Studio and enter the connection details.
  2. Enter the server name, username, and the primary password that you used when creating the SQL database.
  3. Click Connect.

Searching for the Enrollment ID

About this task

Complete the following steps to get the Enrollment ID.

Procedure

  1. Log in to your Azure Enterprise account at https://ea.azure.com.
  2. You can see your enrollment ID on the upper left corner of the screen.
  3. Copy the Enrollment ID and save it for later use.
    Tip: You must have the role of an Enterprise Administrator.

Getting the JDBC Connection String.

Procedure

  1. Click Database.
  2. In the search bar, type connection string.
  3. Click JDBC and copy the string.
  4. Add the Connection String value inside the JDBC Input plug-in jdbc_connection_string parameter.

JDBC Load Balancing Configuration

About this task

In the AzureSQL JDBC input plug-in, the load between two system is distributed based on Even and Odd session_id.

Procedure

  1. On one Guardium system, in the input section for JDBC plug-in, update the "statement" field as follows. 
    SELECT event_time,succeeded,session_id,database_name,client_ip,server_principal_name,application_name,statement,server_instance_name,host_name,DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) AS updatedeventtime,additional_information  FROM sys.fn_get_audit_file('https://<storage-account-name>.blob.core.windows.net/sqldbauditlogs/<server_instance_name>/<DB-NAME>', DEFAULT, DEFAULT) where action_id='BCM' and statement not like '%xproc%' and statement not like '%SPID%' and statement not like '%DEADLOCK_PRIORITY%' and application_name not like '%Microsoft SQL Server Management Studio - Transact-SQL IntelliSense%' and session_id%2 = 0 and DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) > :sql_last_value order by event_time;
  2. On another Guardium system, in input section for JDBC plug-in, update the "statement" field as follows. 
    SELECT event_time,succeeded,session_id,database_name,client_ip,server_principal_name,application_name,statement,server_instance_name,host_name,DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) AS updatedeventtime,additional_information  FROM sys.fn_get_audit_file('https://<storage-account-name>.blob.core.windows.net/sqldbauditlogs/<server_instance_name>/<DB-NAME>', DEFAULT, DEFAULT) where action_id='BCM' and statement not like '%xproc%' and statement not like '%SPID%' and statement not like '%DEADLOCK_PRIORITY%' and application_name not like '%Microsoft SQL Server Management Studio - Transact-SQL IntelliSense%' and session_id%2 = 1 and DATEDIFF_BIG(ns, '1970-01-01 00:00:00.00000', event_time) > :sql_last_value order by event_time;

What to do next

Configure the universal connector by either using the legacy workflow or the Central Manager workflow. The Central Manager workflow is only available from Guardium Data Protection version 12.1 and later.

To configure the plug-in by using the legacy workflow, see Configuring a universal connector by using the legacy workflow.

12.1 and later To configure the plug-in by using the new flow, see Configuring a universal connector by using a Central Manager.