Using DPAPI for SQL connection string

Welcome Forums General PowerShell Q&A Using DPAPI for SQL connection string

This topic contains 5 replies, has 3 voices, and was last updated by

3 years, 7 months ago.

  • Author
  • #28400

    Points: 0
    Rank: Member

    Hi guys,

    Now, I'm currently using Powershell to write to a database regularly, and like most, I can't be storing my passwords in scripts! Now, I use DPAPI to encrypt my password which I use to connect to MSOnline in my scripts, and I'm trying to apply this to an SQL Connection string, however this is not working, I get this error:

    Exception calling "Open" with "0" argument(s): "Login failed for user '[USERNAME]'."
    At line:20 char:9
    + $SQLConnection.Open()
    + ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    I have also tried using $SQLUsername and $SQLPassword in the connection string, but still nothing!

    I'm assuming this is because im trying to use a powershell method in an SQL string... Is there any way I can make this work??

    Many Thanks.

            $DPAPI = "C:\DPAPI"
                If (!(Test-Path $DPAPI)) {
                    md $DPAPI
                    Write-host "Creating DPAPI Directory..." -foregroundcolor yellow
                else {
                    Write-Host "DPAPI Directory Already Exists." -foregroundcolor green
            $SQLUserName = "[DATABASE USERNAME]"
            $key = "$DPAPI\SQLkey.txt"
                if (!(Test-Path -Path $key)) {
                    $SQLDPAPICreds = Get-Credential -Message "Azure SQL Database Credentials" -UserName $SQLUserName
                    $SQLDPAPICreds.Password | ConvertFrom-SecureString | Out-File $key
                Get-Content $Key
            $SQLPassword = Get-Content $key | ConvertTo-SecureString
            $SQLCred =New-object -TypeName System.Management.Automation.PSCredential -ArgumentList $SQLUserName, $SQLPassword
            $SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
            $SQLConnection.ConnectionString = "Server=[SERVER NAME];Database=[DB NAME];User ID=$SQLCred.Username; Password=$SQLCred.Password;"
  • #28401

    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    The variable replacement trick in double quotes only works with variables, not properties or methods. You need to enclose them in a subexpression.


  • #28402

    Points: 24
    Team Member
    Rank: Member

    Hi Steven,

    Using a subexpression $() for $SQLCred.Username and $SQLCred.Password should fix it.

    $SQLConnection.ConnectionString = "Server=[SERVER NAME];Database=[DB NAME];User ID=$($SQLCred.Username); Password=$($SQLCred.Password);"

    I hope above helps,


  • #28405

    Points: 0
    Rank: Member

    Thanks for the quick responses both of you – actually using the SQL connection from your CBT series Don!

    However this hasn't helped, which is strange as I know capturing the data like that has worked in the command – I tried this before. this is an attempt to connect to an Azure SQL, or I would have been simply using windows authentication.

    Is it worth trying to use System.Data.SQLClient.SQLCredential instead of System.Management.Automation.PSCredential? If so, I will have to find out how to apply this data type...

    I am also fairly new to Azure SQL, but as far as I know, [Username]@[random server name] is the credentials I intend to use in my connection string.

    Many Thanks,

  • #28407

    Points: 24
    Team Member
    Rank: Member

    I have just tested your code on my machine without the SQL connection bit. The password property of $SQLCred does not contain the plain text password but the secure string of the password. You need to use the method GetNetworkCredential of the PSCredential object instance to access the plain text password.

    $SQLConnection.ConnectionString = "Server=[SERVER NAME];Database=[DB NAME];User ID=$($SQLCred.Username); Password=$($SQLCred.GetNetworkCredential().Password);"

    I hope that works for you now.

  • #28412

    Points: 0
    Rank: Member

    Hi Daniel,

    This method looks like it has worked, I will test that everything is working with my module when I have time – thank you very much.

    Just had to stick $SQLCred.GetNetworkCredential() in its own parentheses:

    I definitely need to brush up on my methods, you've saved me hours of research – I should have come here sooner 🙂

    Many Thanks,

The topic ‘Using DPAPI for SQL connection string’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort