THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Change SQL Servers Authentication Mode with PowerShell

Lately I've been working on scripts to check and set security and configuration settings for SQL Server using PowerShell. One of the settings that I normally set and forget at install time is the Authentication Mode setting. Best practices suggest that you set this to Windows Authentication, but my experience has been that it's always better to set it to Mixed mode, and set a very strong password for the sa account.

So, what if we want to change it after the fact? Well, it's a registry setting. Management Studio allows you to make that change via the Security page in Server Properties, but I prefer scripting when setting configuration settings. The setting is exposed in SMO (Server Management Objects), however, and we can check the setting using PowerShell. (I'm going to assume you're either running SQLPS.exe or you've already loaded the SMO libraries. If you don't know what I'm talking about, check here.)

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
[string]$nm = $s.Name
[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"

What the script returns is the instance name, and the authentication mode it's using. The property in SMO is called LoginMode, and can have one of four values:

  • Integrated - Windows Authentication
  • Mixed - Mixed Mode
  • Normal - SQL Server Only Authentication
  • Unknown - Undefined (and no, I haven't tried it.)

I sometimes encounter a system that was set to Integrated, and I want to change it to Mixed mode, because I like to have that safety net of sa if something goes wrong. Here's how I do that:

#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Make the changes
$srv.Alter()

Once that's done the server does need to be restarted, and I need to go in and set the sa password to something VERY strong right away. But now the server is set to the authentication mode I prefer, and it's a lot easier (in my mind) than going into the registry to do it.

 

Allen

Published Thursday, May 19, 2011 5:22 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ben Thul said:

While I'm all about scripting stuff like this, this setting is available in SSMS.  If you right click on a server, go to properties, and then go to the security page, it's the "Server authentication" group of radio buttons.

May 19, 2011 5:34 PM
 

AllenMWhite said:

Thanks, Ben. It's been a while since I looked at that page. I've adjusted the post.

May 20, 2011 8:06 AM
 

Abhijit said:

Hey dats nice one Allen.

June 20, 2011 11:42 AM
 

Luke said:

Allen,

how would one go about feeding a list of servers into this ?

i have ~100 sql instances i need to check/alter

April 22, 2013 10:51 AM
 

Luke said:

i solved it myself:

$colComputers = Get-Content -Path C:\computers.txt

foreach ($strComputer in $colComputers)

{

# Connect to the instance using SMO

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $strComputer

[string]$nm = $s.Name

[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"

write-output "Login Mode: $mode"

}

April 22, 2013 11:06 AM
 

AllenMWhite said:

Luke, it would look something like this:

$srvlist = @'

Server1

Server2

Server3

'@

foreach ($srvnm in $srvlist) {

 # Connect to the instance using SMO

 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srvnm

 #Change to Mixed Mode

 $s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

 # Make the changes

 $s.Alter()

 }

Make sense?

Allen

April 22, 2013 11:13 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement