2

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 ?

Laurent Moya_etc
  • 31
  • 1
  • 5
  • 1
    Is there a particular reason why you are making such a complicated mix of .bat and .ps1 and not just starting the sqlcmd directly from the .ps1 script? – Gerald Schneider Sep 11 '20 at 13:09
  • @GeraldSchneider, all the code you can see is in the batch file, there isn't .ps1 file. Actually, most of the computers where the batch is executed don't accept PowerShell scripts execution, whereas the batch is allowed. – Laurent Moya_etc Sep 11 '20 at 13:18
  • Do you have to use hard-coded Username and password? Can you not run sqlcmd under an account that has permissions in the target database and use the "-E" (Trusted Connection) option instead? – Phill W. Sep 11 '20 at 13:41
  • @PhillW. Yes, I do… To be honest, I didn't think about setting a "trusted connection", running the sqlcmd under an allowed account (with right permissions in the DB). This is very interesting, and it deserves to be tested deeper. – Laurent Moya_etc Sep 14 '20 at 16:10

1 Answers1

1

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 !

Laurent Moya_etc
  • 31
  • 1
  • 5