Hi All,

Below is the powershell script to connect a SQL DB using windows authentication by impersonating a user. The user should have login access to the server. Script can be highly useful when service id is used to access DB using powershell.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$ds=$null
$conn = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "Servername"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database

$conn.ConnectionContext.ConnectAsUser = $true 
$conn.ConnectionContext.LoginSecure=$true
$conn.ConnectionContext.ConnectAsUserName = "username"
$conn.ConnectionContext.ConnectAsUserPassword ="password
$conn.Loginmode
$db = $conn.Databases.Item("dbname")
$ds = $db.ExecuteWithResults("select top 10 Name from Tablename")

Foreach ($t in $ds.Tables)
{
   Foreach ($r in $t.Rows)
   {
      Foreach ($c in $t.Columns)
      {
          Write-Host $c.ColumnName "=" $r.Item($c)
      }
   }
}

That is OK when you want to manage the instance biut it wojn't work if the tools are not installed.

This works from anywhere all of the time and is simpler:

$datasource='MyServerInstance'
$database='NorthWind'
$connStr='Data Source={0}; Database={1}; Trusted_Connection=True;' -f '$datasource,$database
$conn=New-Object System.Data.SQlClient.SQlConnection($connStr)
$conn.Open()
$cmd=$conn.CreateCommand()
$cmd.CommandText='select * from products'
$rdr=$cmd.ExecuteReader()
0..($rdr.FieldCount-1) | %{$rdr.GetName($_)}
$conn.Close()

If you want credentials just add them.

$connStr='Data Source={0}; Database={1}; Trusted_Connection=True;UID={2};PWD={3};' -f  $datasource,$database,uid,$pwd

thanks for the reply, but the above code does not works getting many errors. 

New-Object : Exception calling ".ctor" with "1" argument(s): "Keyword not supported: ''data source'."