sql-server - 从 PowerShell 连接到 SQL Server 数据库

Question

我在网上浏览了一段时间，发现了很多类似的问题，但由于某种原因，我似乎无法解决这个问题。

我只是尝试连接到 SQL Server 数据库并将查询结果输出到文件 - 请参阅下面的 PowerShell 脚本。我不确定如何将用户 ID 和密码集成到连接字符串中。

$SQLServer = "aaaa.database.windows.net"
$SQLDBName = "Database"
$uid ="john"
$pwd = "pwd123"
$SqlQuery = "SELECT * from table;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | out-file "C:\Scripts\xxxx.csv"

收到以下错误消息:

Exception calling "Fill" with "1" argument(s): "Windows logins are not supported in this version of SQL Server."

Answer 1

集成安全性 和用户 ID\密码 身份验证是互斥的。要以运行代码的用户身份连接到 SQL Server，请从连接字符串中删除用户 ID 和 密码:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"

要使用特定凭据进行连接，请删除集成安全性:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;"