Introduction
In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication.
There is one object in PowerShell utilized for making connections as a separate account: PSCredential. This object is used to capture the new credentials. In most cases it allows you to securely pass in those credentials without making the account information visible in your script. In the examples below I am just going to use the “Get-Credential” command to build this object. The article referenced at the end of this article for securing your passwords in PowerShell goes over this command and others in more detail.
Options
As the previous article, the following are the options we will cover:
- SQL Server PowerShell (SQLPS)
- SQL Server Management Objects (SMO)
- .NET (System.Data.SqlClient)
SQL Server PowerShell (SQLPS)
While SQLPS is still around a better module that Microsoft released with SSMS 2016 was renamed to “SqlServer”. This module is being expanded upon as SSMS updates are released so is more robust that SQLPS module. The methods shown below would work with either module though.
SqlServer Provider
SQLPS.exe, the utility, is not going to be touched on because it does not support changing the account before you connect. When you open that utility you are placed directly into the “SQLSERVER:” provider, without being able to change the account. The provider itself though, can be adjusted to use a separate account. If you have used the file provider in PowerShell to connect to remote shares (equivalent to”net use“) it works the same way.
The command to create a new drive in PowerShell is “New-PSDrive”. This command requires the following parameters to create the drive:
- Name – this is used to do directory lookup, so instead of “SQLSERVER:” you would use the name provided
- PSProvider – this tells the command what provider to actually use, in our case it is SqlServer
- Root – the root path you want to connect to (e.g. “SQLSERVER:”)
- Credential – the account you want to make the connection under
|
1 2 |
New-PsDrive -Name DefaultSql -PSProvider SqlServer -Root 'SQLSERVER:\SQL\SERVERNAME\DEFAULT' -Credential (Get-Credential) |
The limitation you have with the SqlServer provider (both module and PSDrive) is it only supports SQL Login authentication. It creates the drive with a Windows account, but when you try to do a directory lookup on the drive you will see the exception:
You can see from this screen shot it errors that it does not exist. There is no associated login failure logged to SQL Server, so my belief is it was not implemented to properly handle using Windows Account as SMO supports. If you try using a SQL Login though, you can see a successful attempt to browse the drive:
SqlServer Module
The module is slowly growing with new commands each month, and has officially replaced the SQLPS module. I have found not all of the commands support the “-Credential” parameter though, only 24 out of 82 as of this article. You can easily find the ones that do by using the following command:
|
1 |
Get-Command -Module SqlServer -ParameterName Credential |
You will notice that the command “Invoke-Sqlcmd” is not listed. The “Invoke-Sqlcmd” command does let you pass in a username and password for a SQL Login, but only in plain text. The commands that do support the credential parameter have the same limitation as the provider, only accepting SQL Login authentication.
A short example that uses “Get-SqlErrorLog”:
|
1 2 |
$cred = Get-Credential Get-SqlErrorLog -ServerInstance MyServer -Credential $cred | select -Last 3 |
SQL Server Management Objects (SMO)
SMO supports Windows or SQL Authentication, but connecting with SMO is a bit unique based on which authentication type you want to use. To just refresh, the lines to create our SMO server object are noted below and include just creating the PScredential object:
|
1 2 3 4 5 |
$cred = Get-Credential Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=13.0.0.0,Culture=neutral,PublicKeyToken=8984 5dcd8080cc91" $srv = New-Object Microsoft.SqlServer.Management.Smo.Server MyServer |
Once you have the server object create we work out passing in the account desired, based on which type is needed.
SMO – Windows Authentication
To use a Windows account you have to let SMO know by setting some properties to true:
|
1 2 |
$srv.ConnectionContext.LoginSecure = $true $srv.ConnectionContext.ConnectAsUser = $true |
We can then pass in the username and password from the credential variable and connect.
|
1 2 3 |
``powershell $srv.ConnectionContext.ConnectAsUserName = $cred.username $srv.ConnectionContext.ConnectAsUserPassword = $cred.GetNetworkCredential().Password $srv.ConnectionContext.Connect() ``` |
SMO – SQL Login
In comparison to use a SQL Login account you set the LoginSecure to false, and then just pass in the username and password but we have to use a different set of properties:
|
1 2 3 4 |
$srv.ConnectionContext.LoginSecure = $false $srv.ConnectionContext.set_Login($cred.username) $srv.ConnectionContext.set_SecurePassword($cred.password) $srv.ConnectionContext.Connect() |
dbatools
The module dbatools is helping to make DBA’s lives easier where we do not have to remember all that SMO syntax. Instead of remembering all of the above properties based on which authentication type wouldn’t it be easier to just use a function to create the server object? Let me introduce you to “Connect-DbaSqlServer”, one of many commands in the dbatools module. This command is based on an internal function that is used within each command to build the needed server object for SMO. After you install dbatools it is as simple as the following lines:
|
1 2 3 |
Import-Module dbatools $cred = Get-Credential $srv = Connect-DbaSqlServer -Sqlserver MyServer -Credential $cred |
Isn’t that much easier to use?
.NET (System.Data.SqlClient)
SqlClient only allows a SQL Login account to be provided. Which is expected as .NET utilizes the login that initiated the process for Windows Authentication. To utilize a SQL Login account we need to build a specific credential object, System.Data.SqlClient.SqlCredential. You have to pass in the username and the password (as SecureString). There is one extra thing required for the password. The SqlCredential requires that the password is read-only, so it cannot be altered once the connection is made. Which means once you set it as read-only it cannot be reverted, you would have to rebuild the object. Any attempt to modify that password will give you an invalid exception error.
We can still use our PSCredential to collect the username and password. The following are the steps to take for building the SqlCredential object:
|
1 2 3 4 5 6 7 8 9 |
# First create the PSCredential object $cred = Get-Credential #set the password as read only $cred.Password.MakeReadOnly() # Create the SqlCredential object $sqlCred = New-Object System.Data.SqlClient.SqlCredential($cred.username,$cred.password) |
After we have that we just need to set the credential for our connection:
|
1 2 3 |
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConn.ConnectionString = “Server=localhost\sql12;Initial Catalog=master” $sqlConn.Credential = $sqlCred |
Wrap Up
As you probably noticed, SQL Login gets the most support in the options above. If you need to use Windows Authentication you have a few options but for the most part will need to start PowerShell as the needed Windows Account. PowerShell offers multiple options for connecting as a different Windows Account that are not directly related to SQL Server, if SQL Login is just not an option. You can look into commands like “Invoke-Command” or “Start-Process”, these provide an option to also pass in a Windows credential.
I hope the above information provides some help in better understanding using alternate credentials to connect to SQL Server.
After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.
He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).
View all posts by Shawn Melton
- Learning PowerShell and SQL Server – Introduction - April 23, 2018
- Connecting PowerShell to SQL Server – Using a Different Account - January 24, 2017
- How to secure your passwords with PowerShell - January 18, 2017