Creating a SQL Server Instance Object

Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell editor.

You will need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>.

If you are connecting to your instance using Windows authentication and using your current Windows login, the steps are as follows:

If you are connecting using SQL Authentication, you will need to know the username and password that you will use to authenticate. In this case, you will need to add the following code, which will set the connection to mixed mode and prompt for the username and password:

#set connection to mixed mode
$server.ConnectionContext.set_LoginSecure($false)

#set the login name
#of course we don't want to hardcode credentials here
#so we will prompt the user
#note password is passed as a SecureString type
$credentials = Get-Credential

#remove leading backslash in username
$login = $credentials.UserName $server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

#check connection string
#note though that this outputs your password in clear text
$server.ConnectionContext.ConnectionString

Write-Verbose "Connected to $($server.Name)"
Write-Verbose "Logged in as $($server.ConnectionContext. Login)"

Before you can access or manipulate SQL Server programmatically, you will often need to create references to its objects. At the most basic level is the server.

The server instance is using the type Microsoft.SqlServer.Management.Smo.Server. By default, connections to the server are using a trusted connection, meaning it uses the Windows account you're currently using when you log into the server. So all it needs is the instance name in its argument list:

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

However, if you need to connect using a SQL login, you will need to set the ConnectionContext.LoginSecure property of the SMO Server class setting to false:

#set connection to mixed mode
#note that this authentication will fail if mixed mode
#is not enabled in SQL Server
$server.ConnectionContext.set_LoginSecure($false)

You will also need to explicitly set the username and the password. The best way to accomplish this is to prompt the user for the credentials:

#prompt
$credentials = Get-Credential

The Get-Credential cmdlet will display a popup window that will capture the login and password entered by the user:

$login = $credentials.UserName

Once we have the login, we can pass it to the set_Login method. The password is already a SecureString type, which means it is encrypted. This is the data type required by the set_SecurePassword method, so no further conversion is needed. The commands are as follows:

$server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

Should you want to hardcode the username and just prompt for the password, you can also do this:

$login = "belle"

#prompt
$credentials = Get-Credential –Credential $login

In the script, you will also notice that we are using Write-Verbose instead of Write-Host to display our results. This is because we want to control the output without always going back to our script and removing the Write-Host command.

By default, the script will not display any output, that is, the $VerbosePreference special variable is set to SilentlyContinue. If you want to run the script in verbose mode, you simply need to add this in the beginning of your script:

$VerbosePreference = "Continue"

When you are done, you just need to change the value to SilentlyContinue:

$VerbosePreference = "SilentlyContinue"