You'll find a good number of hits on using powershell with SQL but most examples use integrated security. The key is to have the right connection string for your needs and environment. Check out
ConnectionStrings.com, they have a lot of SQL Server examples, many using standard authentication. Once you have the right string, it's
almost a matter of plugging in the username and password values you get from Get-Credential.
Because Get-Credential stores the password as a securestring object, you have to do a tiny bit of extra work to get the password back out to use with something like this which does not support securestrings directly. (Maybe it can be done another way without having to use the plaintext pw...I do not know).
Here's how to get that password:
76# $a = Get-Credential
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
77# $a
UserName Password
-------- --------
\username System.Security.SecureString
78# $a.GetNetworkCredential()
UserName Password Domain
-------- -------- ------
username password
So you would do $a.GetNetworkCredential().Username and $a.GetNetworkCredential().Password, as needed. e.g.
function Invoke-RadarSqlQuery { <br> <br> param ( <br> [string]$Query = $(Throw "You must specify a SQL statement.") <br> ) <br> # Script variables <br> $script:dbserver = "server" <br> $script:database = "database" <br> <br> #You'll need to change this connection string below <br> $connString = "server=$dbserver;Integrated Security=SSPI;database=$database" <br> $cn = new-object "System.Data.SqlClient.SqlConnection" $connString <br> <br> # create the command <br> $cmd = new-object "System.Data.SqlClient.SqlCommand" <br> $cmd.CommandType = [System.Data.CommandType]"Text" <br> $cmd.CommandText = $Query <br> $cmd.Connection = $cn <br> <br> #get the data <br> $dt = new-object "System.Data.DataTable" <br> <br> $cn.Open() <br> $rdr = $cmd.ExecuteReader() <br> <br> $dt.Load($rdr) <br> $cn.Close() <br> <br> write-output $dt <br> } That example uses the native SQL client, which must be installed for this method to work. There's other ways such as creating a machine ODBC data source. See the website above for hints on how the conn string would work.