Problem
In previous tips on Windows PowerShell with SQL Server, you’ve seen how you can use Windows PowerShell and SMO to administer SQL Server databases. Most of the examples have used Windows authentication to connect to SQL Server. How can I connect to SQL Server via Windows PowerShell using mixed mode authentication?
Solution
In most enterprise environments, SQL Server installations are configured using SQL Server and Windows Authentication mode, or what is commonly known as “mixed-mode” authentication. While Microsoft recommends using Windows Authentication for better security and ease of account management either via Active Directory or the local Windows Users and Groups, there are a lot of third-party applications – even those running on a non-Microsoft Windows operating system – that require SQL Server to be configured using mixed-mode authentication. And while Windows PowerShell can take full advantage of Windows authentication when connecting to SQL Server, there are cases when we cannot use a valid Windows account but rather a SQL Server account to manage SQL Server. In this tip, we will explore connecting to SQL Server via Windows PowerShell using mixed-mode authentication.
In the tip on Getting Started with SQL Server Management Objects (SMO), C# Code Block 1 – Connecting to SQL Server highlights connecting to SQL Server using both Windows and mixed-mode authentication. Let’s expand the use of the ConnectionContext property of the Server object to connect to SQL Server using mixed-mode authentication. The ConnectionContext property can be used to change connection settings, such as the name of the instance of SQL Server or the authentication type. In our case, we will use it to change the authentication type, which will be SQL Server authentication. This property is represented by the ServerConnection class, which is really not necessary in the previous tips with SMO. This is because the ServerConnection class is automatically created when declaring the Server class and that most of the default properties of this class are designed to use Windows authentication (which is why we really never defined anything when creating a new Server object except for the SQL Server instance name).
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
#This sets the connection to mixed-mode authentication
$srv.ConnectionContext.LoginSecure=$false;
#This sets the login name
$srv.ConnectionContext.set_Login("edwin");
#This sets the password
$srv.ConnectionContext.set_Password("PaSSw0rd")
The LoginSecure property is what tells the connection to use mixed-mode authentication – a value of true, which is the default, will use Windows authentication and defaults to the security context of the currently logged in user and a value of false will switch it to mixed-mode authentication. The set_Login and set_Password properties will set the login and password, respectively. Note that at this point, there will be no connection made to SQL Server unless an action is taken. You could verify this by running SQL Server Profiler and check for the credentials that you passed using the LoginName column. To initiate a connection, we can retrieve the list of databases running on the SQL Server instance by adding the code below.
$srv.Databases | Select name
Run the code either by typing it in the console or creating a PowerShell script and saving it.
To make the script execution a bit easier to find in SQL Server Profiler, I have added the ApplicationName property
$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"
Hard-coding Logins and Passwords? Enter Get-Credential
As with most applications that require authentication, it is not recommended to hard-code the credentials within the script – especially in clear text. Being a database administrator, you have elevated privileges on the SQL Server instances that you manage and storing your credentials in any script or application poses a security risk. One approach you can apply is to encrypt the credentials in your script. I will explain how that can be done using Windows PowerShell in a future tip. Another approach is to prompt for credentials when connecting to a SQL Server instance. This is where the Get-Credentials cmdlet come in. You use the Get-Credential cmdlet to create a secured credential object based on a supplied username and password. Depending on the system registry setting you have on the computer you are using to run Windows PowerShell, the cmdlet will prompt for credentials either via a dialog box similar to Windows login dialog box or thru the command line
The Get-Credential cmdlet can be used for both Windows authentication and SQL Server authentication when connecting to SQL Server. And while the default for Windows authentication is to use the credential of the currently logged in user, you might want to log in using a different Windows account with much higher privileges. A typical security principle when logging in to servers is to use an ordinary user account and switch to a user account with much higher privileges only when necessary. With Get-Credential, if you enter a user without a domain name, it will insert a backslash before the name. We will then be responsible to deal with the extra character when processing the credential.
Let’s update the PowerShell script above by replacing the hard-coded login name and password with the output of the Get-Credential cmdlet
#Prompt for user credentials
$credential = Get-Credential
#Deal with the extra backslash character
$loginName = $credential.UserName -replace("\\","")
#This sets the login name
$srv.ConnectionContext.set_Login($loginName);
#This sets the password
$srv.ConnectionContext.set_SecurePassword($credential.Password)
$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"
$srv.Databases | Select name
Notice how we no longer have the credentials hard-coded within the script. The UserName and Password properties of the Credential object are the ones we used. By doing so, we need to update the set_Password property of the ConnectionContext to set_SecurePassword. We also need to deal with the extra backslash character introduced by the Credential object using the -replace parameter.
This approach has provided us with a secure way of connecting to SQL Server by eliminating hard-coded credentials in scripts.
Next Steps
- Connect to SQL Server via Windows PowerShell using mixed-mode authentication
- Use the Get-Credential cmdlet to eliminate hard-coding credentials for SQL Server logins
- Check out the previous PowerShell and Security tips.
