Hello,

I have a script which does some actions, and upon completion writes some information to a sql database which allows us to go back and see who ran it. Currently it makes the connection using the credentials of the user running the script, however, I need to change it to use SQL authentication, but am having trouble getting started. Here is the snippet from my script:

$SQLQuery = "INSERT INTO Logs (Technician, DateTime) VALUES ('"

$SQLQuery += $curuser
$SQLQuery += "', '"
$SQLQuery += $now
$SQLQuery += "')"
$SqlServer = "SQLServer";
$SqlCatalog = "SQLDatabase";

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SQLConnection.Open()
$cmd = $SQLConnection.CreateCommand()
$cmd.CommandText = $SQLQuery
$result = $cmd.ExecuteNonQuery()

$SqlConnection.Close();


I'm using straight powershell, no other providers or anything. Can anyone assist? Thanks.

Hi Ayth,
The script below was originally created by Lee Holmes, but I added -username and -password parameters to allow SQL Authentication:


############################################################################## ## ## Invoke-SqlCommand.ps1 ## ## From Windows PowerShell Cookbook (O'Reilly) ## by Lee Holmes (http://www.leeholmes.com/guide) ## Updated by Chris Harris (seaJHawk) ## - Allow use of SQL Auth ## ## Return the results of a SQL query or operation ## ## ie: ## ## ## Use Windows authentication ## Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" ## ## ## Use SQL Authentication ## $cred = Get-Credential ## Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred ## ## ## Perform an update ## $server = "MYSERVER" ## $database = "Master" ## $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248" ## Invoke-SqlCommand $server $database $sql ## ## $sql = "EXEC SalesByCategory 'Beverages'" ## Invoke-SqlCommand -Sql $sql ## ## ## Access an access database ## Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users" ## ## ## Access an excel file ## Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]' ## ############################################################################## ############################################################################## ##param( ## [string] $dataSource = "sqlrpt1", ## [string] $database = "ShelbyReport", ## [string] $sqlCommand = $(Throw "Please specify a query."), ## [System.Management.Automation.PsCredential] $credential ## ) ############################################################################## param( [string] $dataSource = "sqlrpt1", [string] $database = "ShelbyReport", [string] $sqlCommand = $(Throw "Please specify a query."), [System.Management.Automation.PsCredential] $credential, [string] $username = "readonly", [string] $password = "readonly" ) ## Prepare the authentication information. By default, we pick ## Windows authentication $authentication = "Integrated Security=SSPI;" ## If the user supplies a credential, then they want SQL ## authentication if($credential) { $plainCred = $credential.GetNetworkCredential() $authentication = ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password) } else { if ($username -and $password) { $authentication = ("uid={0};pwd={1};" -f $username,$password) } } ## Prepare the connection string out of the information they ## provide $connectionString = "Provider=sqloledb; " + "Data Source=$dataSource; " + "Initial Catalog=$database; " + "$authentication; " ## If they specify an Access database or Excel file as the connection ## source, modify the connection string to connect to that data source if($dataSource -match '\.xls$|\.mdb$') { $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; " if($dataSource -match '\.xls$') { $connectionString += 'Extended Properties="Excel 8.0;"; ' ## Generate an error if they didn't specify the sheet name properly if($sqlCommand -notmatch '\[.+\$\]') { $Error = 'Sheet names should be surrounded by square brackets, and ' + 'have a dollar sign at the end: [Sheet1$]' Write-Error $Error return } } } ## Connect to the data source and open it $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString $command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection $connection.Open() ## Fetch the results, and close the connection $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command $dataset = New-Object System.Data.DataSet [void] $adapter.Fill($dataSet) $connection.Close() ## Return all of the rows from their query $dataSet.Tables | Select-Object -Expand Rows

It is all based off of the connection string line, the rest of the code doesn't care who logged in.

This is the line you need to modify (right now it is set to integrated security):
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

To help you out with what to put in this line, check out this website:
http://www.connectionstrings.com/