sql server语言 - Powershell脚本连接到sql数据库与windows身份验证

Question

使用windows身份验证连接SQL server数据库的正确语法是什么?

Answer 1

https://technet.microsoft.com/en-us/magazine/hh855069.aspx上的Technet文章详细介绍了如何使用PowerShell连接到SQL Server数据库。它还包括一个可以在脚本中使用的示例函数:

````powershell
function Get-DatabaseData {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($isSQLServer) {
        $adapter = New-Object-TypeName System.Data.SqlClient.SqlDataAdapter $command
    } else {
        $adapter = New-Object-TypeName System.Data.OleDb.OleDbDataAdapter $command
    }
    $dataset = New-Object -TypeName System.Data.DataSet
    $adapter.Fill($dataset)
    $dataset.Tables[0]
}
function Invoke-DatabaseQuery {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $connection.Open()
    $command.ExecuteNonQuery()
    $connection.close()
}
````

在Technet文章的上述脚本中，您只需要提供3个参数:连接字符串(对于集成安全，您将使用Trusted connection =True)，要运行的查询和数据库类型(SQL Server或OleDB)。

Answer 2

一个正常的SQL身份验证连接字符串看起来像

ConnectionString 'Server=$server;Database=$databaseName;UID=$DOMAINUSER;PWD=$password'

上述认证与windows认证之间的唯一主要区别是通过切换集成安全

集成安全= true;

那么windows身份验证连接字符串将读取

ConnectionString 'Server=$server;Database=$databaseName;UID=$DOMAINUSER;PWD=$password;Integrated Security=true;'

注意，如果您的服务器是localhost，为UID指定域是可选的，因此您可以简单地将UID指定为UID=sa;

在下面找到一个完整的代码示例，可以适应您的用例。

function global:SelectAllUsers()
{
    Read-Query -ConnectionString 'Server=localhost;Database=Ulysses;UID=EMEAXJ193;PWD=somepassword;Integrated Security=true;' `
        -Query "SELECT * FROM Users" `
        -Action {
            echo "I can take an action here"
        }
}
function Read-Query
{
    param (
        [Parameter(Mandatory=$true)]
        [string]$ConnectionString,
        [Parameter(Mandatory=$true)]
        [string]$Query,
        [Parameter(Mandatory=$true)]
        [scriptblock]$Action
    )
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $reader = $SqlCmd.ExecuteReader()
    while ($reader.Read())
    {
        $x = $null
        $x = @{}
        for ($i = 0; $i -lt $reader.FieldCount; ++$i)
        {
            $x.add($reader.GetName($i), $reader[$i])
        }
        Invoke-Command -ScriptBlock $action -ArgumentList $x
    }
    $SqlConnection.Close()
}

SelectAllUsers

Answer 3

此外，您还可以恢复到函数Invoke-Sqlcmd2，它为您自动执行所有这些操作。我们使用它非常成功，它使生活更容易。

CmdLet Invoke-SqlCmd2带有参数Credential，在使用Windows身份验证时可以省略。

.PARAMETER Credential
  Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine.
  If -Credential is not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows account running the PowerShell session.