Code snippets to read encrypted objects from an MS SQL DB And decrypt them Works only in Powershell 3 with SQL 2005-2012 Does not work in Powershell 1,2 does not work with SQL 2000 Requires SQLPS (install SSMS or google how to install standalone)

DecryptSqlObjects.ps1

# Code snippets to read encrypted objects from an MS SQL DB
# And decrypt them
# Works only in Powershell 3 with SQL 2005-2012
# Does not work in Powershell 1,2 does not work with SQL 2000
# Requires SQLPS (install SSMS or google how to install standalone)
# Requires remote Dedicated Administrator Connection to be enabled 
# if you are not conntecting to a local instance
#
# This code realies on global variables this is BAD (tm). 
# Sorry about that. I'll try and improve it if I have time

# define your database name and server instance here
$db = "Test"
$srv = "(local)"

# below are the decoding part
$familyGuidPattern = "dbi_familyGUID\s*=\s*([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12})"

$listEncryptedQuery = @"
-- Stored Procedures, Functions, Views, Table triggers
SELECT 
  SCHEMA_NAME(obj.schema_id) [sch], 
	obj.name, RTRIM(obj.type) [type], 
	obj.object_id [objid], 
	(CASE WHEN RTRIM(obj.type) IN ('P','RF') THEN 1 ELSE 0 END) [spNum], 
	cast(mod.uses_quoted_identifier as bit)  [QI], 
	cast(mod.uses_ansi_nulls as bit) [AN], 
	(CASE WHEN trig.is_disabled IS NULL THEN 0 ELSE trig.is_disabled END) [dis]
FROM sys.objects obj WITH (NOLOCK) 
INNER JOIN sys.sql_modules mod WITH (NOLOCK) 
ON mod.definition IS NULL AND obj.object_id = mod.object_id 
LEFT JOIN sys.triggers trig WITH (NOLOCK) 
ON obj.object_id = trig.object_id

union all

-- Numbered Stored Procedures
SELECT 
	SCHEMA_NAME(obj.schema_id), 
	obj.name, 
	'P', 
	obj.object_id, 
	np.procedure_number, 
	cast(mod.uses_quoted_identifier as bit)  [usesQuotedIdentifier], 
	cast(mod.uses_ansi_nulls as bit) [usesAnsiNulls], 
	cast(0 as bit) 
FROM sys.objects obj WITH (NOLOCK) 
INNER JOIN sys.sql_modules mod WITH (NOLOCK) 
ON obj.object_id = mod.object_id 
INNER JOIN sys.numbered_procedures np WITH (NOLOCK) 
ON obj.object_id = np.object_id 
WHERE np.definition IS NULL

union all

-- Database triggers
SELECT 
	'' , 
	obj.name, 
	'DTR', 
	obj.object_id, 
	0, 
	cast(mod.uses_quoted_identifier as bit)  [usesQuotedIdentifier], 
	cast(mod.uses_ansi_nulls as bit) [usesAnsiNulls], 
	obj.is_disabled 
FROM sys.triggers obj WITH (NOLOCK) 
INNER JOIN sys.sql_modules mod WITH (NOLOCK) 
ON obj.parent_class = 0 
	AND mod.definition IS NULL 
	AND obj.object_id = mod.object_id

union all

-- Server triggers
SELECT
	'' , 
	obj.name, 
	'STR', 
	obj.object_id, 
	0, 
	cast(mod.uses_quoted_identifier as bit)  [usesQuotedIdentifier], 
	cast(mod.uses_ansi_nulls as bit) [usesAnsiNulls], 
	obj.is_disabled 
FROM sys.server_triggers obj WITH (NOLOCK) 
INNER JOIN sys.server_sql_modules mod WITH (NOLOCK) 
ON mod.definition IS NULL 
	AND obj.object_id = mod.object_id
order by type, name
"@

function Decrypt([byte[]] $data, [guid] $familyGuid, [int] $objectId, [int16] $storedProcedureNumber = 0) {
    $key = $familyGuid.ToByteArray() + 
    @([byte]($objectId -band 0xff), [byte]($objectId -shr 8 -band 0xff),
    [byte]($objectId -shr 16 -band 0xff),[byte]($objectId -shr 24 -band 0xff),
    [byte]($storedProcedureNumber), [byte]($storedProcedureNumber -shr 8 -band 0xff))
    $key = (New-Object System.Security.Cryptography.SHA1Managed).ComputeHash($key)
    $b = 0..0xff
    $keySeed = ($key * [math]::Ceiling(0xff / $key.Length))[$b]
    0..0xff | %{ $i=0 } { $i = ($i + $b[$_] + $keySeed[$_]) -band 0xff;$b[$_],$b[$i] = $b[$i],$b[$_] }
    $result = $data | %{ $i=$j=0 } {
        $i = ($i + 1) -band 0xff
        $j = ($j + $b[$i]) -band 0xff
        $b[$j],$b[$i] = $b[$i],$b[$j]
        $_ -bxor $b[($b[$i] + $b[$j]) -band 0xff]
    }
    [System.Text.Encoding]::Unicode.GetString($result)
}

function GetFamilyGuid {
    $getFamilyGuidQuery = @"
DBCC TRACEON (3604) WITH NO_INFOMSGS;
DBCC DBINFO ('$db') WITH NO_INFOMSGS;
DBCC TRACEOFF(3604) WITH NO_INFOMSGS
"@
    $var = Invoke-Sqlcmd -Server $srv -Verbose $getFamilyGuidQuery 4>&1
    $a = $var | ?{ $_ -match  $familyGuidPattern }
    [guid]$Matches[1]
}

function GetEncryptedObjects {
    Invoke-Sqlcmd $listEncryptedQuery -Database $db -Server $srv
}

function GetEncryptedObjectText([int] $objid, [int] $spNum = 0) {
    $getEncryptedObjectsTextQuery = "select cast('' as xml).value('xs:hexBinary(sql:column(`"imageval`"))', 'varchar(max)') imageval from sys.sysobjvalues with (nolock) where objid=$objid and subobjid=$spNum"
    $d = Invoke-Sqlcmd $getEncryptedObjectsTextQuery -Database $db -DedicatedAdministratorConnection -Server $srv -MaxCharLength ([int]::MaxValue)
    0..($d.imageval.Length/2-1) | %{([convert]::tobyte($d.imageval.substring($_*2,2),16))}
}


# From now on an example of usage

# This is how to get the list of the decrypter objects
# types are listed here: http://msdn.microsoft.com/en-us/library/ms190324.aspx
# columns:
# ==========
# Schema name
# Object name
# Type as above STR - server trigger, DTR - database trigger
# Object Id
# Quoted Identifier
# Ansi Nulls
# Numbered Stored Procedure Number
# Trigger Disabled
# ==========
# Quoted Identifier, Ansi Nulls, Trigger Disabled
# are required if you want to generate scripts that you can later on apply back to the database
# (which you will have to implement yourself)
# this is opposed to simply reading the text of the object
$rec = GetEncryptedObjects
$rec | ft -auto

# Now let's select a particular object
# we beed objid and spnum to retreive the text
# spnum is zero from all of the objects except for numbered
# stored procedures
$name = "vw_upload_contact_groups"
$rec1 = @($rec |?{ $_.name -eq $name})[0]
$objectId = $rec1.objid
"ObjId: $objectId" | Write-Host
$spnum =  $rec1.spnum
"SpNum: $spnum" | Write-Host
$type =  $rec1.type
"Type: $type"  | Write-Host
$db = if ($type -eq "STR") { "master" } else  { $db }

# This is how to get family guid that is required for decrypting 
$guid = GetFamilyGuid
$guid | Write-Host

# getting encrypted text - this requires Dedicate Admin Connection
# if it's not enabled run this on the server:
# exec sp_configure 'show advanced options', 1
# RECONFIGURE WITH OVERRIDE
# exec sp_configure 'remote admin connections', 1
# RECONFIGURE WITH OVERRIDE
$data = GetEncryptedObjectText $objectId $spnum 

#decrypting it
$res = Decrypt $data $guid $objectId $spnum
$res | Write-Host


# This is another example that does not require the database
# it symply decyphers hardcoded data to prove that the
# decryption code works
[guid]$guid = "3fca6b87-8d12-450e-98ed-ae418edbfcbe"
[string]$cypher = "7DD9C1D9796C0265B046289824BB5D50D6396A1F1DE000CBE8E81F27D01E89722B3006784752DD06A3A67B1B8665F7FC1D5C619D6945C229615A8AD9972949FF25249AFBCB99359033208661782D2DD0AAA0A8D724EBBB41FD5B36024AD9B9D6F296B96D8B9A3F7C8C87DEB4C4CD0894046706A67844E551E970712CA53BB2DB5A6FE482B96310FBCCF61618BF54FA7E4432C44D55B4D153C32E2C9710EEADAA362CDE93034CE6114D102ED22F5D02FBCC9287BF48971CB735E23681C0EE7F2D93860EFB972D3949CD41F00E82579FAC399F7C17B12E1CA151F64C1C2B1DAB4D216377156985A165E9F0F9E68F419352A751D0DFF4AA0B8D1DABAB66BE0C98C575E410CEB7FFDDAC909E014BE8F3FFAF59F391FB21C8BCB582B9DC0D5380F570F2D709A68A371343915CE042F8A4C2B6164484BC262A95BCA1DBF9728B54384EA2E6B37107F1FFD7D6336DDF7CD18CED6BCA5212020B28D82F9FFEB71448859AA623B9E1A538FB7FA2700A7F4465B858751048CC844047557FC736EDDF7DEC65D3086F7C9F4694A6B69AD9457D5198C52CF049FCE4F0E7080624207AD0FD266E73D578AFEDDF53273E4A2BB7396CAA6784F51284F43A26230B5A04A08BE16638C92E254406AE8C7FB98A7961E1F60E3BB6086E7EB800E2EAC213C53E500BDEE880FFC58DD005DA580E478B50C7D23B25"
$data = 0..($cypher.Length/2-1) | %{([convert]::tobyte($cypher.substring($_*2,2),16))}
$objectId = 614397358

$res = Decrypt $data $guid $objectId
$res | Write-Host