How to remove encryption from all objects in SQL Server?

Question

I have more than a hundred encrypted procedures and functions that I want to decrypt (I am trying a bacpac file export but it fails due to procedures being encrypted). I tried using dbforge sql decryptor decryption wizard for in place alter but I get the error:

Definition is invalid. Can't find CREATE keyword.

When I try to see the DDL script of a stored procedure(using dbforge sql decryptor), I get the error:

A definition for the object dbo.pt_blocks cannot be shown because it is encrypted by a third party tool

I can not find a resolution to this. Are there any solutions or other tools available for this?

Edit: I found this resource which mentions

take the source code and issue an ALTER command without the encryption option. Just take the source code and remove the WITH ENCRYPTION

How could I achieve this?

EDIT: I have enabled remote DAC. How can I decrypt everything? The accepted answer from this question has a broken link.

Edit: The problem has been solved by uninstalling a third party tool which was creating encrypted procedures.

Answer 1

Below is a PowerShell example that creates a script file of all encrypted objects, gleaned from Paul White's The Internals of WITH ENCRYPTION article. Change the data source and initial catalog in the 2 connection strings to the desired server and database as well as script file path.

A DAC connection is used to retrieve values from system tables so sysadmin server role membership is required. If run remotely, the SQL Server remote admin connections option must be enabled and TCP port 1434 allowed through the firewall.

The script can be run from the PowerShell ISE or from a command prompt after customization. Example command-line invocation, assuming script was saved to file "Decrypt-Objects.ps1".

powershell -ExecutionPolicy RemoteSigned -File C:\PowershellScripts\Decrypt-Objects.ps1

PowerShell script:

# PowerShell implementation of T-SQL code from https://sqlperformance.com/2016/05/sql-performance/the-internals-of-with-encryption

Function Get-DecryptedString($pwd, $data) {

    $key = [System.Array]::CreateInstance([int], 256)
    $box = [System.Array]::CreateInstance([int], 256)
    $cipher = [System.Array]::CreateInstance([byte], $data.Length)

    for ($i = 0; $i -lt 256; ++$i) {
      $key[$i] = $pwd[$i % $pwd.Length]
      $box[$i] = $i
    }

    for ($j = $i = 0; $i -lt 256; ++$i) {
      $j = ($j + $box[$i] + $key[$i]) % 256
      $tmp = $box[$i]
      $box[$i] = $box[$j]
      $box[$j] = $tmp
    }

    for ($a = $j = $i = 0; $i -lt $data.Length; ++$i) {
      ++$a
      $a %= 256
      $j += $box[$a]
      $j %= 256
      $tmp = $box[$a]
      $box[$a] = $box[$j]
      $box[$j] = $tmp
      $k = $box[(($box[$a] + $box[$j]) % 256)]
      $cipher[$i] = ($data[$i] -bxor $k)
    }

    $decryptedString = [System.Text.Encoding]::Unicode.GetString($cipher)

    return $decryptedString

}


Function Get-ClearObjectText($connectionString, $objectName) {

$getRc4KeyQuery = @"
DECLARE
    @objectid integer = OBJECT_ID(@ObjectName),
    @family_guid binary(16),
    @objid binary(4),
    @subobjid binary(2);
 
-- Find the database family GUID
SELECT @family_guid = CONVERT(binary(16), DRS.family_guid)
FROM sys.database_recovery_status AS DRS
WHERE DRS.database_id = DB_ID();
 
-- Convert object ID to little-endian binary(4)
SET @objid = CONVERT(binary(4), REVERSE(CONVERT(binary(4), @objectid)));
 
SELECT
    -- Read the encrypted value
    @imageval = SOV.imageval,
    -- Get the subobjid and convert to little-endian binary
    @subobjid = CONVERT(binary(2), REVERSE(CONVERT(binary(2), SOV.subobjid)))
FROM sys.sysobjvalues AS SOV
WHERE 
    SOV.[objid] = @objectid
    AND SOV.valclass = 1;
 
-- Compute the RC4 initialization key
SELECT @RC4key = HASHBYTES('SHA1', @family_guid + @objid + @subobjid);
"@

    $connection = New-Object System.Data.SqlClient.SqlConnection($dacConnectionString)
    $connection.Open()
    
    $command = New-Object System.Data.SqlClient.SqlCommand($getRc4KeyQuery, $connection)
    ($command.Parameters.Add("@ObjectName", [System.Data.SqlDbType]::NVarChar, 261)).Value = $objectName
    ($command.Parameters.Add("@imageval", [System.Data.SqlDbType]::VarBinary, -1)).Direction = [System.Data.ParameterDirection]::Output
    ($command.Parameters.Add("@RC4key", [System.Data.SqlDbType]::Binary, 20)).Direction = [System.Data.ParameterDirection]::Output
    [void]$command.ExecuteNonQuery()
    $imageval = $command.Parameters["@imageval"].Value
    $RC4key = $command.Parameters["@RC4key"].Value
    $connection.Close()

    $decryptedString = Get-DecryptedString -pwd $RC4key -data $imageval

    Return $decryptedString


}

# ############
# ### MAIN ###
# ############

# DAC connection string for decryption
$dacConnectionString = "Data Source=admin:YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"

# normal connection string for encrypted object list
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"

# target file path for clear encrypted objects DDL
$scriptFilePath = "C:\Scripts\EncryptedObjects.sql"
[void](New-Item -Path "C:\Scripts\EncryptedObjects.sql" -ItemType file -Force) # create directory (if needed) and empty script file

$EncryptedObjectQuery = @"
SELECT
     QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name) AS QualifiedObjectName
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1;
"@

try {

    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($EncryptedObjectQuery, $connection)
    $connection.Open()

    $reader = $command.ExecuteReader()

    while ($reader.Read()) {

        $createObjectScript = Get-ClearObjectText -connectionString $dacConnectionString -objectName $reader["QualifiedObjectName"]

        $createObjectScript | Out-File -FilePath $scriptFilePath -Append
        "GO" | Out-File -FilePath $scriptFilePath -Append

    }

    $connection.Close()

}
catch {

    throw

}