powershell - 通过 PowerShell 连接到 SQL

Question

我正在尝试通过 PowerShell 连接到 SQL 服务器。我尝试使用下面的代码，但我不断收到同样的错误。谁能解释为什么会这样？

param
(   
    [Parameter(Mandatory)]  #servername
    [string]$SQLServer,

    # [Parameter(Mandatory)]  #database
    # [string]$SQLDBName,

    [Parameter(Mandatory)]  #user ID
    [string]$uid

)

$pwd = ConvertTo-SecureString "xxx"-AsPlainText -Force #heslo
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID= $uid; Password= $pwd" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'StoredProcName'
$SqlCmd.Connection = $SqlConnection 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close() 

错误如下所示：

使用“1”参数调用“填充”的异常：“用户'目标'登录失败。” 在 C:\Users\salema\Documents\SQL.ps1:24 char:1 + $SqlAdapter.Fill($DataSet) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

标签： powershell

Answer 1

您需要调用该Open()方法来打开 SQL 连接。尝试添加

$pwd = ConvertTo-SecureString "xxx"-AsPlainText -Force #heslo
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID= $uid; Password= $pwd" 

# Open the SQL connection
$SqlConnection.Open()