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;"
$conn.open()
$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" }
$conn.close()

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;"
$conn.open()
$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" }
$conn.close()

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.