Creating a SQL Server Instance Object

Most of what you will need to do in SQL Server will require a connection to an instance. One way to do this is by creating an instance object via SMO.

Getting ready

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>.

How to do it...

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

1. Import the SQLPS module:

   #import SQLPS module
   Import-Module SQLPS –DisableNameChecking
   $VerbosePreference = "SilentlyContinue"

2. Store your instance name in a variable:

   #create a variable for your instance name
   $instanceName = "localhost"

3. If you are connecting to your instance using Windows authentication from the account you are logged in as:

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

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)"

How it works...

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"

See also

• The recipe Loading SMO assemblies.
• The recipe Creating a SQL Server Instance Object.