Archive | SQL RSS for this section

Writing to a SQL database

Last week I needed to store information in a SQL database, so I can share certain user account information with a select group of users in the helpdesk department, but they shouldn’t have access to the AD itself. So I decided that a SQL database was the way to go and I can create a gridview in Powershell for them, to retrieve this information for now, while our programmers change their helpdesk tool to include this information in there.

I decided that I wanted to update this information every 15 minutes and wanted to clear the database everytime (so I don’t need to be bothered with deleting old users and changing ones that have been changed), before I filled it with my data. I can use Integrated Security, because the AD account that runs the script has the correct access rights to the SQL database and my SQL server is set to Windows Authentication mode.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=SQLSERVER(\INSTANCE);Initial Catalog=DatabaseName;Integrated Security=SSPI;"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.commandtext = "TRUNCATE TABLE TableName"
If($cmd.executenonquery() -eq -1) { Write-Host "Succesfully cleared table" }

If you were to use the above code, change the SQLSERVER text to the name of your SQL server, if needed add the backslash and the instance name and change the text DatabaseName to your database name. Also change the text TableName to your table name.

Once the table is cleared, I get all my desired information from my AD servers. When I get this, I’ll have to put it in the SQL database. Remember that when adding data to a SQL table, you need to specify each column name you’d want to fill with your data (usually all column names of the table, but you might have columns which allow nulls, and if you don’t need to fill those with data, you don’t need to enter those names. In my case all columns need to be filled (as they are all the data I need to share from my AD with our helpdesk people), so I entered all my column names.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=SQLSERVER(\INSTANCE);Initial Catalog=DatabaseName;Integrated Security=SSPI;"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.commandtext = "INSERT INTO TableName (OuName,UserName,AccountEnabled,EmailAddress,LastLogonTime,LogonCount) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')" -f $ouName,$userName,$accountEnabled,$emailAddress,$lastLogonDateTime,$logonCount
If($cmd.executenonquery() -eq 1) { Write-Host "Successfully added $ouName, $userName, $accountEnabled, $emailAddress, $lastLogonDateTime, $logonCount to the SQL database" }

In case you wonder how to get the logonCount and emailAddress from an AD user, those are extra properties which can be requested with the Get-ADUser command in powershell, like this:

Get-ADUser $userName -Properties logonCount,emailAddress

The lastLogon information isn’t found in the Get-ADUser command, but can be found trhough the Get-ADObject command, like this:

Get-ADUser $userName | Get-ADObject -Properties lastLogon

And that’s all there’s to it, to write to SQL and clearing the table.

Running .SQL scripts with Powershell

Today I came across a great script by Andy Mishechkin on MS Technet.

The PowerShell script for execution of T-SQL batch files

It’s a great script for .SQL file execution against a SQL server. It can also run .SQL files which contains GO commands. In short: works like a charm!

Creating an ODBC connection

With Powershell 4 on Windows 8 and 2012, they’ve improve the support for ODBC connections. You just need to import the Wdac module to be able to use the different ODBC functions.

Get-Command *ODBC* shows the following functions:


For instance, if you were to create a System ODBC connection, with a 32 bit SQL driver, use the following code:

Import-Module Wdac
$OdbcDriver = Get-OdbcDriver -Name *SQL* -Platform 32-bit
If(!$OdbcDriver.Count) # Only continue if 1 SQL ODBC driver is installed
{ Add-OdbcDsn -Name "ODBC Connection Name" -DriverName $OdbcDriver.Name -Platform 32-bit -DsnType System -SetPropertyValue @("Server=SQL ServerName\SQL Server Instance", "Trusted_Connection=Yes","Database=SQL DatabaseName") }