Connect to SQL Server via Windows PowerShell with SQL Server authentication

By:   |   Comments (7)   |   Related: > PowerShell


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.

1

 

To make the script execution a bit easier to find in SQL Server Profiler, I have added the ApplicationName property

$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"

2

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

3

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, October 27, 2014 - 11:20:27 AM - bass_player Back To Top (35079)

Hi Schinni9,

One option is create multiple credential objects and use those as you connect to servers in a different domain. Take a look at this example for reference

https://gallery.technet.microsoft.com/scriptcenter/Execute-PowerShell-Script-38881dce

 

Thursday, October 16, 2014 - 10:24:58 AM - Schinni9 Back To Top (34979)

Hello Edwin...

Thanks for the tip it's great.I am trying to see how it fits in to my script.

I have a powershell script to check disk space on all servers but the servers are on different domains the only thing is SQL ID with SQLAUTHENTICATION.i can connect with this SQLID across domains on all servers.How can i force my PS script to use that authentication.

I have a server list...and the ps script go thru each server and get the disk space.

 

foreach ($Server in (Get-Content C:\psfiles\Winserver1.txt)){Get-WmiObject Win32_Volume -ComputerName $Server | Where {$_.Drivetype -eq 3 -or $_.DriveType -eq 4} | Select @{Expression={$Server};label="ServerName"},SystemName,name,label,@{Expression={$_.Capacity /1Gb -as [int]};Label="Total Size(GB)"},@{Expression={($_.Capacity /1Gb -as [int]) - ($_.Freespace/ 1Gb -as [int])};Label="InUse Size (GB)"},@{Expression={$_.Freespace / 1Gb -as [int]};Label="FreeSize (GB)"},@{Expression={[math]::round(((($_.Freespace /1Gb) / ($_.Capacity / 1Gb))*100),0)};Label="FreeSpace (%)"},@{Expression={$(Get-Date -format 'd')};Label="Report Date"}| Export-csv "C:\Psfiles\getServerspace.csv" -append -NoTypeInformation}

 

 

Thanks In Advance!

Friday, February 28, 2014 - 6:51:06 PM - Chris Back To Top (29619)

Thanks David - you saved me from having to figure that one out.

 

Tuesday, April 23, 2013 - 12:57:28 AM - Anna Back To Top (23497)

Hi Bass_player,

                     I am doing this for automation purpose..So i m preparing the power shell script for the manual process done in share point central administration..PFB the steps,

Sharepoint central admin>Application managment>Manage Service application>Secure Store Service

In Secure store service by default it will be windows authentication but as per our requirment we need sql server authentication,So we need to select the sql server authentication by means of POWERSHELL command as a part of automation,this is the final step of our process and we are struggling to do that,so can u pls help me out..

 

Monday, April 22, 2013 - 8:56:59 AM - bass_player Back To Top (23474)

SharePoint only supports Windows authentication. Plus, direct access to the database outside of the SharePoint APIs are not supported. Can you share with us what you're trying to accomplish with the Secure Store Service databae?

Monday, April 22, 2013 - 2:34:18 AM - Anna Back To Top (23463)

hi,

  How to connect the secure store service in sql server authentication mode by power shell command..the code which is already provided does not works for me...i m new to sharepoint so can u pls suggest me any other method ...

By default it is in windows authentication mode and it has to changed to sql server authenication modde to do the automation ....

Monday, September 26, 2011 - 5:54:10 PM - David Bayer Back To Top (14731)

I know this is a long time after the fact, but here's a little update to the extra backslash fix.  If you don't know if the user will enter with or without a domain, change -replace("\\","") to -replace("^\\","").  That will replace a backslash at the beginning of the line only, not one between the domain and the username.

 














get free sql tips
agree to terms