来自powershell的SQL连接
问题描述:
我试图将我的脚本连接到数据库。 以前我做它:来自powershell的SQL连接
psexec \\servername -accepteula sqlcmd.exe -U username -P password -S database -Q query
现在,我想将其转换成PowerShell代码。 我想这一点:
Enter-PSSession servername
$SQLCred = New-Object System.Data.SqlClient.SqlCredential("username","password")
$Connection = New-Object System.Data.SqlClient.SqlConnection("Database",$SQLCred)
$Connection.Open()
但我总是得到这样的错误:
Exception calling "Open" with "0" argument(s): "Login failed for user 'username'."
当然,我敢肯定的凭据是正确的。 这可能是两种连接模式之间的区别?在连接字符串中提供
答
凭据:
如果您有现有的认证对象,你可以使用它像这样:
$cred = New-Object System.Data.SqlClient.credential($userId, $password)
$connectionString = "Data Source=$Instance;Integrated Security=SSPI;Initial Catalog=master; User Id = $($cred.username); Password = $($cred.GetNetworkCredential().password);"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()