Using DPAPI for SQL connection string

PowerShell Help
1

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: (:slight_smile: , 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;"
        $SQLConnection.Open()
2

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

$($SQLCred.Username)
$(SQLCred.Password)

3

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,

Best,
Daniel

4

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].database.windows.net is the credentials I intend to use in my connection string.

Many Thanks,
Steven

5

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.

6

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:
Password=$(($SQLCred.GetNetworkCredential()).Password)

I definitely need to brush up on my methods, you’ve saved me hours of research - I should have come here sooner :slight_smile:

Many Thanks,
Steven