CODEWITHSUNDEEP
databasepowershelldatabase-connection
Glowie
Glowie

Reputation: 2309

Powershell how to encrypt connectionstring to database

I have a powershell script that connects to a database:

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server=10.10.10.1;Initial Catalog=my_database;User Id=my_username;Password=my_password;"

But I don't want the plaintext password to be hardcoded into the script.

So, I tried the following:

read-host -assecurestring | convertfrom-securestring | out-file C:\PS\cred.txt
$password = get-content C:\PS\cred.txt | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "my_username",$password

And, I replaced the connection string with 

$conn.ConnectionString = "Server=10.10.10.1;Initial Catalog=my_database;$credentials;"

But I am getting numerous errors.

This script is run on a nightly basis, so it must get the password of the database automatically.

EDIT:

Below are the errors:

Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 46." At C:\sandbox\script.ps1:75 char:7
    + $conn. <<<< ConnectionString = "Server=10.10.10.1;Initial Catalog=my_database;$credentials;"
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException

and

Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 46." At C:\sandbox\script.ps1:82 char:14
    + $conn_update. <<<< ConnectionString = "Server=10.10.10.1;Initial Catalog=my_database;$credentials;"
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException   Creating and executing the SQL Query at 8/26/2013 10:28:38 AM

and finally

Exception calling "Open" with "0" argument(s): "The ConnectionString property has not been initialized." At C:\sandbox\script.ps1:98 char:11
    + $conn.Open <<<< ()
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

Upvotes: 0

Views: 10066

Answers (1)

Mitul
Mitul

Reputation: 9854

About the first error

First try this in ps console 

PS C:\>"server=server10;Initail Catalog=database;$credentials"
server=server10;Initail Catalog=database;System.Management.automation.PSCredential

and sql server doesn't expect the connection string with System.Management.Automation.PSCredential and it need it in a format of user id="myusername"; password="password"

so do this instead. First retrieve username and password from $credentials object and store them in variable and put that variable in the connectionstring. 

$username = $credentials.UserName
$password = $credentials.GetNetworkCredentail().Password

PS C:\>"server=server10;Initial Catalog=database;user id=$username;password=$password"
server=server10;Initial Catalog=database;user id=my_username;password=sdfsjci34929

Upvotes: 5

Related Questions