Powershell如何加密连接到数据库 [英] Powershell how to encrypt connectionstring to database

问题描述

我有一个PowerShell脚本连接到数据库：

  $ conn = New-Object System.Data.SqlClient。 SqlConnection 
 
 $ conn.ConnectionString =Server = 10.10.10.1;初始目录= my_database;用户ID = my_username;密码= my_password; 
  

但我不想将明文密码硬编码到脚本中。

所以，我试过以下：

  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 -argumentlistmy_username，$ password 
  

然后，我用

替换连接字符串

  $ conn.ConnectionString =Server = 10.10 .10.1; Initial Catalog = my_database; $ credentials; 
  

但我遇到了无数错误。

编辑：

这个脚本每晚运行一次，因此必须自动获取数据库的密码。

以下是错误：

 异常设置ConnectionString：初始化字符串的格式不符合规范在索引46。在C：\sandbox\script.ps1：75 char：7 
 + $ conn。 <<< ConnectionString =Server = 10.10.10.1;初始目录= my_database; $ credentials; 
 + CategoryInfo：InvalidOperation：（:) []，RuntimeException 
 + FullyQualifiedErrorId：PropertyAssignmentException 
  

和

 异常设置ConnectionString：初始化字符串的格式不符合从索引46开始的规范。 。在C：\sandbox\script.ps1：82 char：14 
 + $ conn_update。 <<< ConnectionString =Server = 10.10.10.1;初始目录= my_database; $ credentials; 
 + CategoryInfo：InvalidOperation：（:) []，RuntimeException 
 + FullyQualifiedErrorId：PropertyAssignmentException在8/26/2013 10:28:38创建和执行SQL查询
  

最后

 异常调用打开，带有0参数：ConnectionString属性尚未初始化。在C：\sandbox\script.ps1：98 char：11 
 + $ conn.Open<<< （）
 + CategoryInfo：NotSpecified：（:) []，MethodInvocationException 
 + FullyQualifiedErrorId：DotNetMethodException 
  

首先在ps控制台中尝试一下

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

并且sql server不希望使用System.Management.Automation的连接字符串。 PSCredential，它需要它的格式为user id =myusername; password =password

，所以这样做。首先从$ credentials对象中检索用户名和密码，并将它们存储在变量中，并将该变量放在connectionstring中。

  $ username = $ credentials.UserName 
 $ password = $ credentials.GetNetworkCredentail 
 PS C：\>server = server10;初始目录=数据库;用户id = $ username;密码= $ password
服务器= server10;初始目录=数据库;用户ID = my_username; password = sdfsjci34929 
  

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

解决方案

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

这篇关于Powershell如何加密连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！