Azure SQL (via Collector method) - v3.0.0

About Collectors

Pre-requisites

Collector server minimum requirements

SQL Server Requirements

Setting up SQL Server for metadata extraction is a 2 step process.

Step 1: Establish SQLServer Access

Apply in MASTER using an Azure SQL Admin user

CREATE LOGIN kadauser WITH password='PASSWORD';
CREATE USER kadauser FROM LOGIN kadauser;

Apply per database in scope for metadata collection.

CREATE USER kadauser FROM LOGIN kadauser;
GRANT VIEW DEFINITION TO kadauser;
GRANT VIEW DATABASE STATE to kadauser;
GRANT CONTROL to kadauser;  -- required for extended events sys.fn_xe_file_target_read_file

The following table should also be available to SELECT by the user created in each database

INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
sys.foreign_key_columns
sys.objects
sys.tables
sys.schemas
sys.columns
sys.databases

Step 2: Setup Extended Event Logging

Extended Events Setup is in pilot for Azure SQL

An Azure SQL Admin will need to setup an extended events process to capture Query Execution in SQLServer.

First create or reuse an existing Azure Storage Account. Then create a blob in the example the blob is called extended-events

Run the following script to setup Extended Events logging.

Apply per database in scope for metadata collection.

SQL

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<REPLACE with your key: abc1234>';

CREATE DATABASE SCOPED CREDENTIAL [https://your.blob.core.windows.net/extended-events]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '< REPLACE WITH YOUR SAS TOKEN: sp=racwdl ...>';


-- Make sure this file name is unique per database
CREATE EVENT SESSION [KADA] ON DATABASE
	ADD EVENT sqlserver.sp_statement_completed (
		ACTION(package0.event_sequence, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.transaction_id, sqlserver.username) WHERE (
			(
				[statement] LIKE '%CREATE %'
				OR [statement] LIKE '%DROP %'
				OR [statement] LIKE '%MERGE %'
				OR [statement] LIKE '%FROM %'
				)
			AND [sqlserver].[is_system] = (0)
			AND NOT [statement] LIKE 'Insert into % Values %'
			AND [sqlserver].[Query_hash] <> (0)
			)
		), 
	ADD EVENT sqlserver.sql_statement_completed (
	SET collect_statement = (1) ACTION(package0.event_sequence, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.transaction_id, sqlserver.username) WHERE (
		(
			[statement] LIKE '%CREATE %'
			OR [statement] LIKE '%DROP %'
			OR [statement] LIKE '%MERGE %'
			OR [statement] LIKE '%FROM %'
			)
		AND [sqlserver].[is_system] = (0)
		AND NOT [statement] LIKE 'Insert into % Values %'
		AND [sqlserver].[Query_hash] <> (0)
		)
	) ADD TARGET package0.event_file (SET filename = N'https://your.blob.core.windows.net/extended-events/<REPLACE with your db name: database1>.xel')
	WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = ON)
GO

Step 1: Create the Source in K

Create a source in K

Go to Settings, Select Sources and click Add Source
Select "Load from File" option
Give the source a Name - e.g. SQLServer Azure Production
Add the Host name for the SQLServer Azure Instance
Click Next & Finish Setup

Step 2: Getting Access to the Source Landing Directory

Step 3: Install the Collector

You can download the Latest Core Library and Azure SQL whl via Platform Settings → Sources → Download Collectors

Run the following command to install the collector

pip install kada_collectors_extractors_sqlserver_azure-x.x.x-py3-none-any.whl

You will also need to install the corresponding common library kada_collectors_lib-x.x.x for this collector to function properly.

pip install kada_collectors_lib-x.x.x-py3-none-any.whl

Note that you will also need an ODBC package installed at the OS level for pyodbc to use as well as a SQLServer ODBC driver, refer to https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

Step 4: Configure the Collector

FIELD	FIELD TYPE	DESCRIPTION	EXAMPLE
server	string	SQLServer Azure server	"mydatabase.database.windows.net"
host	string	The onboarded host value in K	"mydatabase.database.windows.net"
username	string	Username to log into the SQLServer Azure account	"myuser"
password	string	Password to log into the SQLServer Azure account
databases	list<string>	A list of databases to extract from SQLServer Azure	["dwh", "adw"]
driver	string	This is the ODBC driver	"ODBC Driver 17 for SQL Server"
meta_only	boolean	Extract metadata only without extended events	true
output_path	string	Absolute path to the output location	"/tmp/output"
mask	boolean	To enable masking or not	true
compress	boolean	To gzip the output or not	true
events_name	string	The created extended event session name	KADA

kada_sqlserver_azure_extractor_config.json

JSON

{
    "server": "",
    "username": "",
    "password": "",
    "databases": [""],
    "driver": "ODBC Driver 17 for SQL Server",
    "output_path": "/tmp/output",
    "mask": true,
    "compress": true,
    "meta_only": true,
    "host": "",
    "events_name": "KADA"
}

Step 5: Run the Collector

This code sample uses the kada_sqlserver_azure_extractor.py for handling the configuration details

Python

import os
import argparse
from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger
from kada_collectors.extractors.sqlserver_azure import Extractor

get_generic_logger('root')

_type = 'sqlserver_azure'
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type))

parser = argparse.ArgumentParser(description='KADA SqlServer Azure Extractor.')
parser.add_argument('--config', '-c', dest='config', default=filename)
parser.add_argument('--name', '-n', dest='name', default=_type)
args = parser.parse_args()

start_hwm, end_hwm = get_hwm(args.name)

ext = Extractor(**load_config(args.config))
ext.test_connection()
ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm})

publish_hwm(args.name, end_hwm)

In some scenarios, you may receive an error message about the SSL settings. This error can be resolved via the Open SSL settings. Refer to: https://github.com/mkleehammer/pyodbc/issues/610#issuecomment-534920201

Edited /etc/ssl/openssl.cnf 

# Change or add

MinProtocol = TLSv1.0

CipherString = DEFAULT@SECLEVEL=1

Step 6: Check the Collector Outputs

K Extracts

A set of files (eg metadata, databaselog, linkages, events etc) will be generated in the output_path directory.

High Water Mark File

A high water mark file is created called sqlserver_azure_hwm.txt.

Step 7: Push the Extracts to K

Once the files have been validated, you can push the files to the K landing directory.