Unable to connect to SQL server via PowerShell using secure password-CodePudding

I'm trying to connect to SQL server via PowerShell using the below (I'm new to this). I always get the error "login failed" when I use secure password (from Get-Credential or password stored in file). But if I pass the password as plaintext instead of secure string, it connects successfully. Could any one please suggest a method to pass secure password, ideally stored in an external file.

The code I ran and the error is below:

$cred = Get-Credential

$pwd = $cred.Password
$uid = $cred.UserName


$SQLServer = "."
$SQLDBName = "TestDB"


#Initialize connection string
$connString = "Data Source=$SQLServer;Database=$SQLDBName;User ID=$uid;Password=$pwd"

#Create a SQL connection object
$conn = New-Object System.Data.SqlClient.SqlConnection $connString

#Attempt to open the connection
$conn.Open()
if($conn.State -eq "Open")
{
    # We have a successful connection here
    # Notify of successful connection
    Write-Host "Test connection successful"
    $conn.Close()
} 


    Exception calling "Open" with "0" argument(s): "Login failed for user 'TestUser'."
At line:18 char:1
  $conn.Open()
  ~~~~~~~~~~~~
      CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      FullyQualifiedErrorId : SqlException

Further details of error from SQL server:

Login failed for user 'TestUser'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

CodePudding user response：

Change this

$pwd = $cred.Password

to this

$pwd = $cred.GetNetworkCredential().Password

However, I would advise against storing a plain text password in memory like this. Your method requires it to--at best--be passed as a parameter in plain text, so you need a better method.

Try using this sqlserver module which supports the -Credential parameter in the Invoke-Sqlcmd function.

CodePudding user response：

I was able to pass secure string as password by adding Integrated Security = True; parameter in connection string.

Thank you.