< Back
Invoke-DbaDbDecryptObject
Post
NAME Invoke-DbaDbDecryptObject
SYNOPSIS
Invoke-DbaDbDecryptObject returns the decrypted version of an object
SYNTAX
Invoke-DbaDbDecryptObject [-SqlInstance] <DbaInstanceParameter> [[-SqlCredential] <Pscredential>] [-Database]
<System.Object[]> [[-ObjectName] <System.String[]>] [[-EncodingType] <String>] [[-ExportDestination] <String>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
When a procedure or a function is created with encryption and you lost the code you're in trouble.
You cannot alter the object or view the definition.
With this command you can search for the object and decrypt the it.
The command will output the results to the console.
There is an option to export all the results to a folder creating .sql files.
Make sure the instance allowed dedicated administrator connections (DAC).
The binary versions of the objects can only be retrieved using a DAC connection.
You can check the DAC connection with:
'Get-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled'
It should say 1 in the ConfiguredValue.
To change the configurations you can use the Set-DbaSpConfigure command:
'Set-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled -Value 1'
In some cases you may need to reboot the instance.
PARAMETERS
-Database [<System.Object[]>]
Database to look through for the object.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EncodingType [<String>]
The encoding that's used to decrypt and encrypt values.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExportDestination [<String>]
Used for exporting the results to.
The destiation will use the instance name, database name and object type i.e.:
C:\\temp\\decrypt\\SQLDB1\\DB1\\StoredProcedure
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ObjectName [<System.String[]>]
The name of the object to search for in the database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<DbaInstanceParameter>]
The target SQL Server instance
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Encryption, Decrypt, Database
Author: Sander Stad (@sqlstad), sqlstad.nl
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1
Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the user.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1 -ExportDestination
C:\\temp\\decrypt
Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the folder "C:\\temp\\decrypt".
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ExportDestination C:\\temp\\decrypt
Decrypt all objects in DB1 of instance SQLDB1 and output the data to the folder "C:\\temp\\decrypt"
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1, Function2
Decrypt objects "Function1" and "Function2" and output the data to the user.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>"SQLDB1" | Invoke-DbaDbDecryptObject -Database DB1 -ObjectName Function1, Function2
Decrypt objects "Function1" and "Function2" and output the data to the user using a pipeline for the instance.
RELATED LINKS
https://dbatools.io/Invoke-DbaDbDecryptObject
SYNOPSIS
Invoke-DbaDbDecryptObject returns the decrypted version of an object
SYNTAX
Invoke-DbaDbDecryptObject [-SqlInstance] <DbaInstanceParameter> [[-SqlCredential] <Pscredential>] [-Database]
<System.Object[]> [[-ObjectName] <System.String[]>] [[-EncodingType] <String>] [[-ExportDestination] <String>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
When a procedure or a function is created with encryption and you lost the code you're in trouble.
You cannot alter the object or view the definition.
With this command you can search for the object and decrypt the it.
The command will output the results to the console.
There is an option to export all the results to a folder creating .sql files.
Make sure the instance allowed dedicated administrator connections (DAC).
The binary versions of the objects can only be retrieved using a DAC connection.
You can check the DAC connection with:
'Get-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled'
It should say 1 in the ConfiguredValue.
To change the configurations you can use the Set-DbaSpConfigure command:
'Set-DbaSpConfigure -SqlInstance [yourinstance] -ConfigName RemoteDacConnectionsEnabled -Value 1'
In some cases you may need to reboot the instance.
PARAMETERS
-Database [<System.Object[]>]
Database to look through for the object.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EncodingType [<String>]
The encoding that's used to decrypt and encrypt values.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExportDestination [<String>]
Used for exporting the results to.
The destiation will use the instance name, database name and object type i.e.:
C:\\temp\\decrypt\\SQLDB1\\DB1\\StoredProcedure
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ObjectName [<System.String[]>]
The name of the object to search for in the database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<DbaInstanceParameter>]
The target SQL Server instance
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Encryption, Decrypt, Database
Author: Sander Stad (@sqlstad), sqlstad.nl
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1
Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the user.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1 -ExportDestination
C:\\temp\\decrypt
Decrypt object "Function1" in DB1 of instance SQLDB1 and output the data to the folder "C:\\temp\\decrypt".
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ExportDestination C:\\temp\\decrypt
Decrypt all objects in DB1 of instance SQLDB1 and output the data to the folder "C:\\temp\\decrypt"
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ObjectName Function1, Function2
Decrypt objects "Function1" and "Function2" and output the data to the user.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>"SQLDB1" | Invoke-DbaDbDecryptObject -Database DB1 -ObjectName Function1, Function2
Decrypt objects "Function1" and "Function2" and output the data to the user using a pipeline for the instance.
RELATED LINKS
https://dbatools.io/Invoke-DbaDbDecryptObject