How to enable SQL Server 2008 R2 authentication mode with PowerShell

Some of my friends who follow this blog have told me that they have no idea what I’ve been posting about lately. That’s because my recent posts have been mostly about helping people who are Googling specific questions.

If you are one of my pals who who’ve stuck with me here, thank you. I appreciate it. But this post ain’t for you. (Though I know you’ll read it anyway, if just to poke me a little over a beer.)

This post is among the most narrow technical tidbits I’ve ever written about — and one of the more frustrating to solve. This blog is about a single registry key that turns on both both Windows authentication and SQL Server authentication in SQL Server 2008 R2. It’s useful in a specific deployment situation: when using an AWS-provided Amazon Machine Image (AMI).

Nobody in their right mind attempts to install a DBMS in an AWS instance. Instead, if you’re using Windows, you’d mostly likely start with one of the AMIs that “Microsoft and Amazon have jointly developed…”

If you’re using one these AMIs plus you are using a configuration management system — like Chef — and you have a SQL Server application that requires SQL Server authentication, you have a problem.

Microsoft really doesn’t want you using SQL Server authentication, so they have made it hard to turn on programmatically. In the MSDN article on this topic, note that Microsoft tells you how to enable the “sa” user in both the UI and via T-SQL. But it does not tell you how to change the installation default authentication method which Amazon and Microsoft, in their wisdom, have set to Windows authentication only.

It took me forever to find the registry key that controls this. And I’m pretty sure it’s release specific, so your results for other than Windows Server 2008 R2 and SQL Server 2008 R2 may differ.

Just run this little script in your configuration management setup and you’ll be able to programmatically add SQL users and databases via script.

Push-Location
Set-Location HKLM:
$path = '\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQLServer'
Set-ItemProperty -Path $Path -name LoginMode -Value 2
Pop-Location
Restart-Service -Name MSSQLSERVER