Invoke-DbaDbDecryptObject

Synopsis

Decrypts encrypted stored procedures, functions, views, and triggers

Description

Recovers the original source code from encrypted database objects when the original scripts have been lost or are unavailable. WITH ENCRYPTION stores the source text combined with a keystream using XOR rather than really encrypting it, so the original T-SQL can be recovered.

This is particularly useful in disaster recovery scenarios where you need to recreate objects but only have access to the encrypted versions in the database. The function can decrypt stored procedures, user-defined functions (scalar, inline, table-valued), views, and triggers.

The command outputs results to the console by default, with an option to export all decrypted objects to organized .sql files in a folder structure.

Two methods are available and are selected with DataPages.

By default the command uses a Dedicated Admin Connection (DAC) to read the binary definition from sys.sysobjvalues. It then alters the object to a known placeholder inside a transaction that is rolled back, which produces a known plaintext together with its matching ciphertext, and recovers the original text from those three values.

With -DataPages no DAC is used and the object is never altered. The binary definition is read straight from the raw data pages with DBCC PAGE and the keystream is rebuilt from database and object metadata, so nothing is written to the database at any point. This method needs sysadmin, and it returns the definition exactly as it is stored, so object definitions that contain Unicode characters come back intact.

Both methods need SQL Server 2008 or later. Encrypted definitions have been stored this way since SQL Server 2005, but SMO cannot work against 2005 at all, so no dbatools command reaches that version.

The following paragraphs only apply to the default method that uses the DAC.

To connect to a remote SQL instance, the remote dedicated administrator connection option must be configured. The binary versions of encrypted objects can only be retrieved using a DAC connection.
You can check the remote DAC connection with:
‘Get-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled’
The ConfiguredValue should be 1.

The local DAC connection is enabled by default.

To enable remote DAC connections, use:
‘Set-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled -Value 1’
In some cases you may need to restart the SQL Server instance after enabling this setting.

Syntax

Invoke-DbaDbDecryptObject
    [-SqlInstance] <DbaInstanceParameter>
    [[-SqlCredential] <PSCredential>]
    [-Database] <Object[]>
    [[-ObjectName] <String[]>]
    [[-EncodingType] <String>]
    [[-ExportDestination] <String>]
    [-DataPages]
    [-EnableException]
    [<CommonParameters>]

Examples

Example 1: Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the user

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1

Example 2: Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the folder "C:\temp\decrypt"

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1 -ExportDestination C:\temp\decrypt

Example 3: Decrypt all objects in DB1 of instance SQLDB1 and output the data to the folder "C:\temp\decrypt"

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ExportDestination C:\temp\decrypt

Example 4: Decrypt objects "Function1" and "Function2" and output the data to the user

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1, Function2

Example 5: Decrypt objects "Function1" and "Function2" and output the data to the user using a pipeline for the instance

PS C:\> "SQLDB1" | Invoke-DbaDbDecryptObject -Database DB1 -ObjectName Function1, Function2

Example 6: Decrypt all objects in DB1 of instance SQLDB1 without using a dedicated admin connection and without altering...

PS C:\> Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -DataPages

Decrypt all objects in DB1 of instance SQLDB1 without using a dedicated admin connection and without altering any of the objects.

Required Parameters

-SqlInstance

The target SQL Server instance

-Database

Specifies which databases contain the encrypted objects you want to decrypt. Accepts multiple database names.
Use this to target specific databases instead of searching across all databases on the instance.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-ObjectName

Specifies the names of encrypted objects to decrypt (stored procedures, functions, views, or triggers). Accepts multiple object names.
When omitted, all encrypted objects in the specified databases will be decrypted. Use this to target specific objects when you only need a few items recovered.

-EncodingType

Determines the text encoding used during the XOR decryption process to convert binary data back to readable T-SQL code. Defaults to ASCII.
Use UTF8 when dealing with databases that contain Unicode characters in object definitions or when ASCII decryption produces garbled text.
Only applies to the default method that uses the DAC. With -DataPages the definition is decoded with the encoding that SQL Server actually stores it in, so this parameter is not used.

-ExportDestination

Specifies the folder path where decrypted T-SQL scripts will be saved as individual .sql files.
When specified, creates an organized folder structure by instance, database, and object type (e.g., C:\temp\decrypt\SQLDB1\DB1\StoredProcedure). When omitted, results are displayed in the console
only.

-DataPages

Reads the binary definition from the raw data pages with DBCC PAGE instead of through a Dedicated Admin Connection. No DAC is needed, the instance does not have to allow remote DAC connections, only
one connection is used, and no object is ever altered, so no write happens against the database. This needs sysadmin.
Leave it off to keep the original behaviour of the command, where a Dedicated Admin Connection reads sys.sysobjvalues and each object is briefly altered inside a transaction that is rolled back to
obtain a known plaintext.
Two cases need -DataPages rather than merely preferring it, and both come from the default method having to alter the object.
An INSTEAD OF trigger defined on a view cannot be decrypted by the default method at all, because that method obtains its known plaintext by rewriting the object as an AFTER trigger and a view only
accepts INSTEAD OF. Reading the data pages has no such restriction.
A read-only database is the other. A database snapshot and an availability group readable secondary can both be read with -DataPages, while the default method fails against either because it cannot
alter anything there.
Not available on Azure SQL Database or Azure SQL Managed Instance, neither of which supports DBCC PAGE. The command refuses -DataPages on both rather than failing part way through reading the pages.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per encrypted database object that is successfully decrypted. If no encrypted objects are found, nothing is returned.

Properties:

• ComputerName: The name of the computer hosting the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance format)
• Database: The name of the database containing the encrypted object
• Type: The type of database object (StoredProcedure, UserDefinedFunction, View, or Trigger)
• Schema: The schema name containing the object
• Name: The object name
• FullName: The fully qualified name in schema.name format
• Script: The decrypted T-SQL source code of the object
• OutputFile: The file path where the script was exported; null if -ExportDestination was not specified and results were displayed in console only