Versions Compared

Old Version 15

changes.mady.by.user Ben

Saved on

compared with

New Version 16

changes.mady.by.user Ben

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Scroll ignore
scroll-viewporttrue
scroll-pdftrue
scroll-officetrue
scroll-chmtrue
scroll-docbooktrue
scroll-eclipsehelptrue
scroll-htmltrue
scroll-epubtrue

Open in new tab

About Collectors

Insert excerpt
Collector Method
Collector Method
nameabout

...

  • Python 3.6 - 3.10

  • Access to the KADA Collector repository that contains the SQL Server whl

    • The repository is currently hosted in KADA’s Azure Blob Storage. You will be given a SAS token to access the repository. Reach out to KADA Support (support@kada.ai) if you do not have access.

    • Download the SQL Server whl (e.g. kada_collectors_extractors_sqlserver_azure-#.#.#-py3-none-any.whl)

  • Access to K landing directory

  • Access to SQL Server (see section below)

  • Check the SQLServer instance port

    • Run the following query and note the local tcp port.

      Code Block
      SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

SQL Server Access

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

Step 1: Establish SQLServer Access

Note

CREATE LOGIN in master via the Apply in MASTER using an Azure SQL Admin user

Code Block
CREATE LOGIN kadauser WITH password='PASSWORD';

CREATE USER kadauser FROM LOGIN kadauser;

Note

...

Apply per database in scope for metadata collection.

Code Block
CREATE USER kadauser FROM LOGIN kadauser;

GRANT VIEW DEFINITION TO kadauser;

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

...

Run the following script to setup Extended Events logging.

Note

Apply the following statement in each databaseper database in scope for metadata collection.

Code Block
breakoutModewide
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: ADD TARGET package0.event_file (SET filename = N'...'
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].[server_principal_name] <> N'USERS_TO_EXCLUDE'
			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].[server_principal_name] <> N'N'USERS_TO_EXCLUDE'
		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

...