SQL Server and PowerShell Security

Sometime back, I heard that Microsoft was going to start using PowerShell scripts to monitor and optionally enforce security standards in SQL Server configurations, such as in the Microsoft Security Compliance Manager (SCM). I knew little about PowerShell, but right away I had a serious concern about whether or not requiring the use of PowerShell would introduce a new set of vulnerabilities due to PowerShell itself.

SQL Server 2008 introduced support for PowerShell, and included right-click launch shortcuts in the SQL Server Management Studio's (SSMS) Object Explorer. If you launch PowerShell from the Windows Start menu, it won't load with the SQL Server commandlets by default, although you can add them to your running environment after it's started. However, if you launch PowerShell from inside SSMS, it will include all the SQL Server commandlets in the PowerShell environment. There's a gotcha in a completely default install of Windows and SQL Server 2008, however, because PowerShell won't run any scripts at all.

PowerShell has 4 ExecutionPolicy options: Restricted, AllSigned, RemoteSigned, and Unrestricted. The Windows default for ExecutionPolicy is Restricted, which prevents it from running any scripts, although you can run any commands in interactive mode if you have sufficient permissions. You have to change the ExecutionPolicy before you can run any scripts, and that always seemed to me like the most secure default configuration, but that apparently confused more than a few people. I suspect that confusion was the reason behind this change: When you install SQL Server 2008 R2 it changes the Windows PowerShell ExecutionPolicy to AllSigned. AllSigned lets PowerShell run any script that has a digital signature from a trusted publisher.

Well, when I was at TechReady 13 in July (TechReady is the main biannual technical conference for Microsoft field personnel such as Premier Field Engineers), I went to the Ask The Experts session, found the PowerShell table, and asked how PowerShell security works. An extremely knowledgeable person explained that PowerShell neither improves nor weakens security, just as Windows Explorer (the file browser) or a command prompt do not improve or weaken security. PowerShell merely lets us automate tasks if we have sufficient permission to execute those tasks anyway. The primary aspect of security for PowerShell, just like for Windows Explorer and command prompts, is that it depends on how you're logged in and what permissions your account has.

I asked more questions, and this very friendly person answered all of them. As I was about to leave, I asked his name, and he introduced himself as Ed Wilson! The same Ed Wilson that writes "The Scripting Guys" TechNet blog and the author of numerous books, including 3 on PowerShell! So here I am in Seattle, all expenses paid by Microsoft, in the midst of an intensive training week, and I get to have a one-on-one conversation with Ed to get my PowerShell questions answered. Have I mentioned lately that I love working for Microsoft?

In summary, PowerShell does NOT introduce any additional vulnerabilities, regardless of whether it's used to manage SQL Server or not. PowerShell security relies first and foremost on the permissions of the person who is logged in and running PowerShell. PowerShell scripts can also be run by a service, which will rely on the permissions of the service account, but that's not recommended.