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