Powershell – How to secure the SQL credentials in a batch file (PowerShell inside) to run a script using sqlcmd

batchcredentialspowershell

My goal is to create a batch file which performs a SQL (express) DB backup every time it's executed. But I don't want to store a clear password in that batch.

Using PowerShell, I successfully created a secure txt file which stores the plain text encrypted SQL needed password :

'MyPassword' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "C:\TEST\dbSecure.txt"

I easily set the %SERVER% and %USER% variables in my batch

SET SERVER=localhost\instance_name
SET USER=toto

My problem is that I want to get back clear text from securestring (using PowerShell command) and then set it as the %PASSWORD% variable in my batch :
Following advises and similar questions on internet, I tried this

REM Powershell to create a $Password variable - its secure value is stored in dbSecure.txt
powershell -command " $SecurePassword = Get-Content 'C:\TEST\dbSecure.txt' | ConvertTo-SecureString "

and then

FOR /F "tokens=*" %%F IN (powershell -noninteractive -command " ($UnsecurePassword = (New-Object PSCredential "user",$SecurePassword).GetNetworkCredential().Password) ") DO (
    SET PASSWORD=%%F
)

…but it doesn't work

So I tried this way

FOR /F "tokens=* USEBACKQ" %%F IN (`powershell -command " ([System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::
SecureStringToBSTR($Password)^)^) " `) DO (
    SET PASSWORD=%%F
)

… but it doesn't work neither.

The end of my batch is logically

sqlcmd -I -S %SERVER% -U %USER% -P %PASSWORD% -i "C:\TEST\backup_database.sql"

Sure, I messed something up in the code or in the understanding.
Does anybody know how could I handle this please ?

Best Answer

I DID IT !!! I share what I found (and I'm gonna use this code until I get a better way, like @PhilW advised in his comment). It can be helpful for you guys :

On a PowerShell console, type

'MyP@ssW0rd' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "C:\TEST\dbSecure.txt"

Now clear the PowerShell commands history with

[Microsoft.PowerShell.PSConsoleReadLine]::ClearHistory()

You can set the dbSecure.txt as hidden file. In the batch you use for the sqlcmd backup execution, I used an existing question/answer from @Papa smurf (Jul. 2018), and customized it, as below :

FOR /F "tokens=*" %%P IN (' Powershell -command " (New-Object PSCredential "toto",(Get-Content C:\TEST\dbSecure.txt | ConvertTo-SecureString)).GetNetworkCredential().Password"') DO SET PASSWORD=%%P

Then, in the batch script, you can use the following code :

@ECHO OFF
SET SERVER=localhost\my_instance
SET USER=toto

FOR /F "tokens=*" %%P IN (' Powershell -command " (New-Object PSCredential "toto",(Get-Content C:\TEST\dbSecure.txt | ConvertTo-SecureString)).GetNetworkCredential().Password"') DO SET PASSWORD=%%P

sqlcmd -I -S %SERVER% -U %USER% -P %PASSWORD% -i "C:\TEST\backup_db.sql"

Hope it will be helpful for you !

Related Topic