Using DPAPI for SQL connection string

This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of Steven Ayers Steven Ayers 1 year, 2 months ago.

  • Author
  • #28400
    Profile photo of Steven Ayers
    Steven Ayers

    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
    Profile photo of Don Jones
    Don Jones

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


  • #28402
    Profile photo of Daniel Krebs
    Daniel Krebs

    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
    Profile photo of Steven Ayers
    Steven Ayers

    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
    Profile photo of Daniel Krebs
    Daniel Krebs

    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
    Profile photo of Steven Ayers
    Steven Ayers

    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,

You must be logged in to reply to this topic.