Written by: Joe Brinkman
Friday, October 19, 2007 9:12 AM 

As part of getting ready for my PowerShell session at OpenForce '07, I am creating a set of helper functions for working with SMO to manipulate and query the database server.  A common need when working with the database is to pass the username and password to various SMO methods.  When I first started coding my examples, I just passed a username and password as parameters into my functions.  This works, but does not exactly look professional when you are sitting in a presentation and typing out passwords in plaintext.

I decided that it would be better to use Get-Credential in this case since it would provide a professional dialog and keep the password hidden throughout the process.  Since I wanted to support both scenarios, I came up with the below function. 

function global:get-sqluser($username="", $password="") {

# We are creating an object to which we'll add custom properties

$user = New-Object Management.Automation.PSObject

if ($username -eq "") {

# No username was specified, so we should use Get-Credential to prompt for a user

# We also define a default username in order to suppress console output

# The results are added as synthetic properties to the PSObject we created above

$cred = Get-Credential "SqlUser"

$user.psobject.members.add( 
    (New-Object System.Management.Automation.PsNoteProperty UserName, $cred.username.split("\")[1]) )

$user.psobject.members.add( 
    (New-Object System.Management.Automation.PsNoteProperty Password, $Cred.Password) )

} Else {

# In this case we can just create synthetic properties using the values passed to the function

$user.psobject.members.add( 
    (New-Object System.Management.Automation.PsNoteProperty UserName, $username) )

$user.psobject.members.add( 
    (New-Object System.Management.Automation.PsNoteProperty Password, $password) )

}

# Return our synthetic object

$user

}

It is not perfect as the two cases return the password as either a cleartext string or a securestring.   Also, I don't like the hack with the Get-Credential since I don't really need a domain qualified name (notice the ugly split("\")[1] that is needed to get just the username without a domain qualifier).  My next version will resolve both of these issues, but for now this will have to suffice.

Trackback Print

Tags:

Categories:

Location: Blogs Joe Brinkman (ASP.Net MVP)