Email blocked due to attachment content or type

If you find that an email attachment is blocked due to its content or type, which is very common when attempting to send some code or a zip file, the following PowerShell may help:

#create file to send
$source_zip = 'C:\temp\yourfile.zip'
$file_to_send = 'C:\temp\yourfile.txt'
[convert]::ToBase64String((Get-Content -path $source_zip -Encoding byte)) | Out-File -FilePath $file_to_send


#extract zip
$file_received = 'C:\temp\yourfile.txt'
$extract_zip = 'C:\temp\yourfile.zip'
[System.Convert]::FromBase64String((Get-Content $file_received)) | Set-Content $extract_zip -Encoding Byte

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.