How to pass SQL credentials to an PowerShell script as command-line arguments

Question

I have the following script in PowerShell:

Clear-Host 
$Machine=Read-Host "Enter the machine name"

$SQLServerTEST = "servername\instance1"  
$SQLTableTEST = "Computer"
$SqlConnectionLANDESK = New-Object System.Data.SqlClient.SqlConnection
$global:dt = new-object System.Data.DataTable

$LONA = ""
$o = 0

function doit() {
$SqlConnectionTEST.ConnectionString =     "Server=$SQLServerTEST;Database=$SQLDBNameTEST;uid=userid;pwd=password"
$SqlConnectionTEST.Open()

$QueryLANDesk = @"

SELECT 
    [Type]
  ,[DeviceName]
  ,[LoginName]
  ,[PrimaryOwner]
FROM $SQLTableTEST
WHERE ([Type] NOT LIKE 'Server' AND [DeviceName] LIKE '%$Machine%' AND     [LoginName] IS NOT NULL )

"@ 

$CommandTEST = new-object System.Data.SqlClient.SqlDataAdapter ($QueryTEST, $SqlConnectionTEST) 
$CommandLANDesk.fill($dt) | out-null

            $dtrc = $dt.Rows.Count
 Write-Host "($i) Searching all cores ($dtrc machines)..."

$SqlConnectionTEST.Close() 
}

foreach ($i in  1..10)
{if ($i -eq 6) {continue}
  $SQLDBNameTEST = "cuc$i"
  $SQLServerTEST = "servername\instance1"
  doit
    }

Write-Host 

$dt.select("DeviceName like '%$Machine%'") | foreach  {  $o++  } 

$dt | Format-Table -AutoSize | select -first 10

"$o machines found."`

I want to be able to pass the $Machine, the dbusename and the password as command line arguments for safety reasons.

I have tried already

Clear-Host 



Param (
[Parameter(Mandatory=$True)]
[string]$dbusername,

[Parameter(Mandatory=$True)]
[Security.SecureString]$Password,

[Parameter(Mandatory=$True)]
[string]$Machine
)

$SQLServerTEST = "servername\instance1"  
$SQLTableTEST = "Computer"

$SqlConnectionTEST = New-Object System.Data.SqlClient.SqlConnection
$global:dt = new-object System.Data.DataTable

$LONA = ""
$o = 0

function doit() {
    $SqlConnectionTEST.ConnectionString =         "Server=$SQLServerTEST;Database=$SQLDBNameTEST;uid=$dbusername;pwd=$Password"
$SqlConnectionTEST.Open()

…….

But this gives me errors:

Exception calling "Open" with "0" argument(s): "Login failed for user     'userid'."
At C:\Users\me\Desktop\cucu\PRIMARYOWNER\Primaryowner.ps1:15 char:5
+     $SqlConnectionTEST.Open()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

Exception calling "Fill" with "1" argument(s): "Login failed for user     'userid'."
At C:\Users\me\Desktop\cucu\PRIMARYOWNER\Primaryowner.ps1:31 char:5
+     $CommandLANDesk.fill($dt) | out-null
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

Can you help me figure this?

You have to extract plaintext value from SecureString. Just "$Password" will return string System.Security.SecureString. — user4003407
– user4003407, Commented Nov 6, 2015 at 17:20

Mitul · Accepted Answer · 2015-11-07 17:52:11Z

1

Try following to get plain text password inside a variable from a secure string which then you will be able to pass to your connectionString. Editing my answer based upon the comment related to memory leak.

$creds = get-credential
$Marshal=[Runtime.InteropServices.Marshal];
$Handle=[IntPtr]::Zero;
$Result= try {
  [Runtime.InteropServices.Marshal]::PtrToStringBSTR(($Handle=$Marshal::SecureStringToBSTR($creds.Password))) 
}catch {}
finally{
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR($Handle)
}
$Result

Another alternate approach and much easier approach is here

PS> $creds = Get-Credential
PS> $plainTextPassword = $creds.GetNetworkCredential().Password

edited Nov 7, 2015 at 17:52

answered Nov 6, 2015 at 17:52

Mitul

9,8644 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4003407 Over a year ago

Note, that you have memory leak here. You call to SecureStringToBSTR, but never call Free on returned value.

user4003407 Over a year ago

I personally would do it this way:

$Marshal=[Runtime.InteropServices.Marshal];$Handle=[IntPtr]::Zero;$Result=try{$Marshal::PtrToStringBSTR(($Handle=$Marshal::SecureStringToBSTR($Password)))}finally{$Marshal::ZeroFreeBSTR($Handle)}

.

Mitul Over a year ago

Thanks. I edited my answer and added an alternate much easier approach too.

Collectives™ on Stack Overflow

How to pass SQL credentials to an PowerShell script as command-line arguments

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related