Creating a SQL Server instance object

Most of what you will need to do in SQL Server will require a connection to an instance.

Getting ready

Open up your PowerShell console, the 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, follow these steps:

1. Import the SQLPS module:

   #import SQLPS module
   Import-Module SQLPS –DisableNameChecking

2. Store your instance name in a variable as follows:

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

3. If you are connecting to your instance using Windows authentication using 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 -replace("\\", "")
   $server.ConnectionContext.set_Login($login) 
   $server.ConnectionContext.set_SecurePassword($credentials.Password)
   
   #check connection string
   $server.ConnectionContext.ConnectionString
   
   Write-Verbose "Connected to $($server.Name)"
   Write-Verbose "Logged in as $($server.ConnectionContext.TrueLogin)"

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 is the server.

The server instance uses the type Microsoft.SqlServer.Management.Smo.Server. By default, connections to the server are made using trusted connections, 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

If, however, 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
$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 credential window will capture the login and password. The Get-Credential cmdlet returns the username with a leading backslash if the domain is not specified. In this case, we want to remove this leading backslash.

#remove leading backslash in username
$login = $credentials.UserName -replace("\\","")

Once we have the login, we can pass it to the set_Login method. The password is already a SecureString type, which is what the set_SecurePassword expects, so we can readily pass this to the method:

$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 we are using Write-Verbose instead of Write-Host to display our results. This is because we want to be able to control the output without needing to always go back to our script and remove all the Write-Host commands.

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 line in the beginning of your script:

$VerbosePreference = "Continue"

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

$VerbosePreference = "SilentlyContinue"

See also

• The Loading SMO assemblies recipe

• The Creating SQL Server instance using SMO recipe