Export-DbaCredential

Synopsis

Exports SQL Server credentials to executable T-SQL CREATE CREDENTIAL scripts

Description

Exports SQL Server credentials to T-SQL files containing CREATE CREDENTIAL statements that can recreate the credentials on another instance. By default, this includes decrypted passwords, making it perfect for migration scenarios where you need to move credentials between servers.

The function generates executable T-SQL scripts that DBAs can run to recreate credentials during migrations, disaster recovery, or when setting up new environments.

Decrypting the stored passwords requires sysadmin privileges, DAC access to the instance and Windows administrator access to its host. On SQL Server Express, the DAC is not available unless the instance is started with trace flag 7806.

The command opens or reuses the dedicated admin connection (DAC) from the machine it runs on. When that machine is different from the host of the instance, enable remote admin connections on the instance (Set-DbaSpConfigure -SqlInstance -Name RemoteDacConnectionsEnabled -Value 1) and make the DAC TCP listener reachable from that machine. SQL Server listens for the DAC on TCP port 1434 when that port is available; otherwise it assigns a port during startup. Check the SQL Server error log for the active DAC port.

The service master key is read and unprotected on the Windows host of the instance. When that host is remote, this uses PowerShell remoting. When the command runs directly on the host, everything runs locally, so remote admin connections and PowerShell remoting are not required.

Use the ExcludePassword parameter to export credential definitions without sensitive data for documentation or security-conscious scenarios; no DAC is opened and the Windows host is not accessed then.

Syntax

Export-DbaCredential
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Credential] <PSCredential>]
    [[-Path] <String>]
    [[-FilePath] <String>]
    [[-Identity] <String[]>]
    [-ExcludePassword]
    [-Append]
    [-Passthru]
    [-EnableException]
    [<CommonParameters>]

Examples

Example 1: Exports credentials, including passwords, from sql2017 to the file C:\temp\cred.sql

PS C:\> Export-DbaCredential -SqlInstance sql2017 -Path C:\temp\cred.sql

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-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.

-Credential

Login to the target OS using alternative credentials. Accepts credential objects (Get-Credential)
Only used when passwords are being exported, as it requires access to the Windows OS via PowerShell remoting to decrypt the passwords.

-Path

Specifies the directory where the exported T-SQL script file will be saved. Defaults to the configured DbatoolsExport path.
Use this when you want to control where credential scripts are stored for organization or compliance requirements.

-FilePath

Specifies the complete file path and name for the exported T-SQL script. Overrides the Path parameter when specified.
Use this when you need precise control over the output file name and location, especially for automated processes.

-Identity

Specifies which credential names to export by filtering on the Identity property. Accepts an array of credential names.
Use this to export specific credentials instead of all credentials, particularly useful when migrating only certain application or service accounts.

-ExcludePassword

Exports credential definitions without the actual password values, replacing them with placeholder text.
Use this for documentation purposes or when you need credential structure without sensitive data for security reviews.
Also use it when the dedicated admin connection or PowerShell remoting described above is not available, because the export then needs neither.

-Append

Adds the exported credential scripts to an existing file instead of overwriting it.
Use this when consolidating credentials from multiple instances into a single deployment script.

-Passthru

Returns the generated T-SQL script to the PowerShell pipeline instead of saving to file.
Use this to capture the script in a variable, pipe to other commands, or display directly in the console.

-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

System.IO.FileInfo

Returns a file object representing the exported T-SQL script file(s) containing the CREATE CREDENTIAL statements. One file is returned for each SQL Server instance from which credentials were exported.

Properties:

• FullName: The complete path to the exported script file
• Name: The name of the exported script file
• Length: The size of the exported file in bytes
• LastWriteTime: The date and time the file was created or last modified
• Directory: The directory containing the exported file