Extracting Data Between Characters

In these examples I’ll use \ as the character.

/*test table*/
DECLARE @t1 TABLE (c1 nvarchar(MAX));
INSERT INTO @t1(c1) VALUES ('D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

/*data after first \ */
SELECT RIGHT(c1, LEN(c1) - 
       CHARINDEX('\',(c1))
	   ) AS after_first
FROM @t1;

/*data after second \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 )
	 ) AS after_second
FROM @t1;

/*data after third \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 )
	 ) AS after_third
FROM @t1;

/*data after fourth \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 )
	 ) AS after_fourth
FROM @t1;

/*data after last \ */
SELECT RIGHT(c1, CHARINDEX('\',REVERSE(c1))-1) AS after_last
FROM @t1;

/*data before last \ */
SELECT LEFT(c1, LEN(c1) - CHARINDEX('\',REVERSE(c1))) AS before_last
FROM @t1;

/*data before second last \ */
SELECT LEFT(c1,LEN(c1) - CHARINDEX('\', REVERSE(c1), CHARINDEX('\', REVERSE(c1), 0)+1)) AS before_second_last
FROM @t1;

/* data before fourth \ */
SELECT LEFT(c1,  
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 ) -1
	 ) AS before_fourth
FROM @t1;

/*data before third \ */
SELECT LEFT(c1, 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 ) -1
	 ) AS before_third
FROM @t1;

/*data before second \ */
SELECT LEFT(c1,
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 ) -1
	 ) AS before_second
FROM @t1;

/*data before first \ */
SELECT LEFT(c1, 
       CHARINDEX('\',(c1)) -1
	   ) AS before_first 
FROM @t1;

/*between first and second*/
SELECT SUBSTRING(c1
,CHARINDEX('\',(c1))+1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) -1) -
CHARINDEX('\',(c1))
) AS between_first_and_second
FROM @t1;

/*between second and third*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) - 
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1))
) AS between_second_and_third
FROM @t1;

/*between third and fourth*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))-2) -
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))
) AS between_third_and_fourth
FROM @t1;

Obfuscate File Data

If you need to copy a file but don’t want the data contained to be easily read and encryption/3rd party applications aren’t available, this PowerShell solution may help. 

Here I have a file with some data I would like to obfuscate.

Below is the PowerShell steps which:

  1. Prepares some variable to point to the 1.txt file and other files which do not exist
  2.  Here we simply convert the text file to Base64 so it is unreadable to humans and create a file called obfuscatedwithaddeddata
  3.  We add a salt of 6 (this could be anything and anywhere in the file, but used here for simplicity) to the end of the file text. This essentially corrupts the file.
  4.  Here is an attempt to try convert from Base64 to text and it will fail
  5.  Here we remove the 6 (salt removed) and save it as a file called obfuscatedwithoutaddeddata
  6.  Finally we can convert the file obfuscatedwithoutaddeddata to file called 2.txt

All these files were created here so you can review the contents through the process.

#1. files
$source_file = “C:\temp\obfuscate\1.txt”
$obfuscated_file = “C:\temp\obfuscate\obfuscatedwithaddeddata”
$unobfuscated_file = “C:\temp\obfuscate\obfuscatedwithoutaddeddata”
$dest_file = “C:\temp\obfuscate\2.txt”

#2. text -> Base64
[io.file]::WriteAllText($obfuscated_file, [Convert]::ToBase64String([io.file]::ReadAllBytes($source_file)))

#3. obfuscate by adding 6 to end of file
Add-Content -Path $obfuscated_file -Value 6

#4. attempt to reverse
# Base64 -> text
[io.file]::WriteAllBytes($dest_file, [System.Convert]::FromBase64String([io.file]::ReadAllText($obfuscated_file)))

#5. remove 6
$unobfuscated_data = Get-Content $obfuscated_file
$unobfuscated_data = $unobfuscated_data.Substring(0,$unobfuscated_data.Length-1)
Set-Content -Path $unobfuscated_file -Value $unobfuscated_data

#6. Base64 -> text
[io.file]::WriteAllBytes($dest_file, [System.Convert]::FromBase64String([io.file]::ReadAllText($unobfuscated_file)))

GitLab Default Branch Missing

If you create a GitLab project and it has no branches, you can try use the below to add one.

Notes:

  • This is run from a Windows cmd prompt.
  • The default branch preference is to now call it main rather than master.
c:
cd \
md temp
cd temp
md gitlab
cd gitlab
git clone https://..../yourgitlabproject
cd yourgitlabproject
echo readme > README.md
git add .
git checkout -b main
git update-ref HEAD main
git push --set-upstream origin main

Replacement for sys.fn_varbintohexstr

If you are on SQL Server 2005, probably just continue to use it.

If you are on a newer version of SQL Server you can use CONVERT(VARCHAR(MAX), [binarydata], 1) e.g.

SELECT
 sys.fn_varbintohexstr([sid]) AS old_way
,CONVERT(VARCHAR(MAX), [sid], 1) AS new_way
FROM master.sys.sql_logins;

Azure Service Principals & Windows Groups

You can’t currently add an Azure Service Principal (SPN) to an on-premises Windows security group.

A work-around is to create an Azure Active Directory security group and then add both the Windows security group and the SPN.

This helps with scenarios like granting multiple access to the Activity Directory admin of PaaS SQL servers.

Azure SQL Database Extended Events Ring_Buffer

Using the ring_buffer for viewing Azure SQL Database extended events is pretty challenging.

Below is my attempt to automate the process.

Simply add the extended event session name you are wanting to view.

Make sure the extended event session is using the ring_buffer output, and is running, and then run the generated sql_command shown.

If you have any comments on improvements i.e. showing deadlock xml, feel free to comment.

/* extended event session name */
DECLARE @session_name nvarchar(128);
/*only update this line with the name of the session to view*/
SET @session_name = 'your extended event session name';

/* temporary table variable */
DECLARE @sqlcmd_table TABLE (row_order int IDENTITY(1,1), row_text varchar(MAX));

/* select start */
INSERT INTO @sqlcmd_table (row_text) SELECT 'DECLARE @t1 TABLE (target_data xml);';
INSERT INTO @sqlcmd_table (row_text) SELECT 'INSERT INTO @t1 (target_data)';
INSERT INTO @sqlcmd_table (row_text) SELECT 'SELECT CAST(target_data AS xml) AS target_data';
INSERT INTO @sqlcmd_table (row_text) SELECT 'FROM sys.dm_xe_database_sessions a, sys.dm_xe_database_session_targets b';
INSERT INTO @sqlcmd_table (row_text) SELECT 'WHERE 1=1';
INSERT INTO @sqlcmd_table (row_text) SELECT 'AND a.[address] = b.event_session_address';
INSERT INTO @sqlcmd_table (row_text) SELECT 'AND b.target_name = ''ring_buffer''';
INSERT INTO @sqlcmd_table (row_text) SELECT 'AND a.[name]= '''+@session_name+''';';
INSERT INTO @sqlcmd_table (row_text) SELECT '';
INSERT INTO @sqlcmd_table (row_text) SELECT 'SELECT'
INSERT INTO @sqlcmd_table (row_text) SELECT ' c.value(''(@timestamp)[1]'',''datetime2'') AT TIME ZONE ''UTC'' AT TIME ZONE ''New Zealand Standard Time'' AS timestamp_nz';
INSERT INTO @sqlcmd_table (row_text) SELECT ',c.value(''(@name)[1]'',''nvarchar(128)'') AS event_name';
INSERT INTO @sqlcmd_table (row_text) SELECT ',c.value(''(@package)[1]'',''nvarchar(128)'') AS package_name';

/* action columns */
INSERT INTO @sqlcmd_table (row_text)
SELECT
',c.value(''(action[@name='''''+c.action_name+''''']/value)[1]'','''+ CASE WHEN d.[type_name] = 'activity_id' THEN ''
WHEN d.[type_name] = 'activity_id_xfer' THEN ''
WHEN d.[type_name] = 'ansi_string' THEN 'varchar(MAX)'
WHEN d.[type_name] = 'ansi_string_ptr' THEN 'varchar(MAX)'
WHEN d.[type_name] = 'binary_data' THEN 'varbinary(MAX)'
WHEN d.[type_name] = 'boolean' THEN 'bit'
WHEN d.[type_name] = 'callstack' THEN 'nvarchar(MAX)'
WHEN d.[type_name] = 'char' THEN 'char(1)'
WHEN d.[type_name] = 'cpu_cycle' THEN 'bigint'
WHEN d.[type_name] = 'filetime' THEN 'datetime'
WHEN d.[type_name] = 'float32' THEN 'float(24)'
WHEN d.[type_name] = 'float64' THEN 'float(53)'
WHEN d.[type_name] = 'guid' THEN 'nvarchar(MAX)'
WHEN d.[type_name] = 'guid_ptr' THEN 'bigint'
WHEN d.[type_name] = 'int16' THEN 'smallint'
WHEN d.[type_name] = 'int32' THEN 'int'
WHEN d.[type_name] = 'int64' THEN 'bigint'
WHEN d.[type_name] = 'int8' THEN 'tinyint'
WHEN d.[type_name] = 'null' THEN 'null'
WHEN d.[type_name] = 'ptr' THEN 'bigint'
WHEN d.[type_name] = 'uint16' THEN 'smallint'
WHEN d.[type_name] = 'uint32' THEN 'int'
WHEN d.[type_name] = 'uint64' THEN 'bigint'
WHEN d.[type_name] = 'uint8' THEN 'tinyint'
WHEN d.[type_name] = 'unicode_string' THEN 'nvarchar(MAX)'
WHEN d.[type_name] = 'unicode_string_ptr' THEN 'nvarchar(MAX)'
WHEN d.[type_name] = 'wchar' THEN 'nchar(2)'
WHEN d.[type_name] = 'xml' THEN 'xml'
ELSE 'nvarchar(MAX)' END + ''') AS ' + c.action_name
FROM
 sys.dm_xe_database_session_events b
,sys.dm_xe_database_session_event_actions c
,sys.dm_xe_objects d
WHERE 1=1
AND b.event_session_address = c.event_session_address
AND b.event_name = c.event_name
AND b.event_package_guid = d.package_guid
AND c.action_name = d.[name]
AND d.object_type = 'action'
ORDER BY b.event_name, c.action_name

/* event columns */
INSERT INTO @sqlcmd_table (row_text)
SELECT
',c.value(''(data[@name='''''+a.[name]+''''']/value)[1]'',''nvarchar(MAX)'') AS '+a.[name]
FROM 
sys.dm_xe_object_columns a JOIN sys.dm_xe_database_session_events b
 ON a.object_package_guid = b.event_package_guid
 AND a.[object_name] = b.[event_name]
LEFT OUTER JOIN sys.dm_xe_database_session_object_columns c
 ON c.object_package_guid = a.object_package_guid
 AND c.event_session_address = b.event_session_address
 AND c.[object_name] = a.[object_name]
  AND c.column_name = a.[name]
WHERE 1=1
AND a.column_type <> 'readonly'
AND (c.column_value IS NULL OR c.column_value = 'true') /* remove not selected */
ORDER BY b.event_name, a.column_id;

/* select end */
INSERT INTO @sqlcmd_table (row_text) SELECT 'FROM @t1 a' + CHAR(13) + CHAR(10) ;
INSERT INTO @sqlcmd_table (row_text) SELECT 'CROSS APPLY target_data.nodes(''RingBufferTarget/event'') AS b(c)'
INSERT INTO @sqlcmd_table (row_text) SELECT 'ORDER BY c.value(''(@timestamp)[1]'',''datetime2'') DESC;';

/* view sql command */
SELECT row_text AS sql_command
FROM @sqlcmd_table
ORDER BY row_order;

/* Make sure the extended event session is running and you run the the generated sql_command in the Azure SQL Database */