talking to your security team about powershell and dbatools

Posted on by Chrissy LeMaire

Last year, during our PASS Summit and SQL Bits precons, we highlighted the reasons that PowerShell is even more secure than GUI administration.

I even created a cute lil logo for it 😁

Recently, our team had a discussion about security in #dbatools-dev and I realized I should probably highlight why PowerShell and dbatools are ideal for every organization, including security-minded organizations.

If you’re questioned about PowerShell or dbatools, here are some handy facts to help prove we can help make your administration more, not less, secure.

PowerShell

So how is using PowerShell more secure than GUI administration? This shocking video from a few years back can help explain. In it, Microsoft Security MVP Marcus Murray highlights just how easy it is to steal a token and impersonate an admin who is logged in via the GUI.

JPG -> IIS -> cmd/.NET -> file system -> SQL Server -> token theft from GUI -> domain controller -> ☠

Remote PowerShell sessions do not create tokens, and consequently do not subject us to the threat of token theft and privilege escalation.

Remote PowerShell, you say?

I remember when I heard the term PowerShell Remoting and saw some book chapters on it, I was like “aw man, another thing I gotta learn?” but it’s actually pretty straight forward.

Windows PowerShell supports remote computing by using various technologies, including WMI, SQL WMI, RPC, SSH & WS-Management. Some commands require no remote configuration, including:

  • Restart-Computer
  • Test-Connection
  • Get-HotFix
  • Get-Process
  • Get-Service
  • Get-WinEvent

Remoting

Remoting refers to commands that use WS-Management. And it’s Microsoft’s recommended method of managing Windows. Why?

  • By default, it only allows connections from members of the Administrators group
  • It uses single port: 5985 or 5986
  • Regardless of the transport protocol used (HTTP or HTTPS), PowerShell Remoting always encrypts all communication after initial authentication with a per-session AES-256 symmetric key
  • Initial authentication is NTLM, Kerberos and Certificates so no credentials are ever exposed

The PowerShell team’s mantra is Secure by Design and you can see it in action with this remoting implementation.

Secure by Design

The PowerShell team is very serious about security and their lead security architect, Lee Holmes, is well-respected in the InfoSec community. He and Jeffrey Snover even did a keynote at DerbyCon where they talked about PowerShell security in-depth.

Yet we’ll sometimes see things like this:

Click to Tweet 🙄 So why do anti-virus (AV) companies highlight the obvious fact that suspicious files uploaded to a suspicious-file-checker will probably be suspicious?

Lee Holmes addresses this very issue in the keynote:

AV, they love to talk about ransomware attacks that use PowerShell, and the reason they do this is because they literally can’t keep up with the plain old stuff like exes. But they also do this because we as a community encourage it. We blog it, we tweet about it, we talk about it. And the next thing you hear is that there’s a PowerShell problem. They don’t seem to ask how to solve their C++ problem or their darn x64 problem.

He then went on to say

When security researchers leverage PowerShell, it is always in a post-compromise context. They have compromised a machine through some other avenue (phishing, SQL injection, etc.), and ultimately have the choice of any number of tools on the system. They were able to accomplish their goals before PowerShell, and will be able to accomplish their goals without PowerShell.

Pretty convincing argument there, Lee! Thank you 🎈

Looking for more? Check out PowerShell, Remoting, and Security by Microsoft MVP Don Jones.

Hackers avoid PowerShell

This tweet speaks volumes.

dbatools

dbatools is an open source project so all of our coding and discussions happen out in the open. Whether it be on GitHub or Slack. We even created a channel called #dbatools-github just to watch GitHub spam, which includes all Pull Requests, Issues and commits to master & development.

Open source is generally considered more secure, but as the SQLShack article “SQL Server security considerations with open source tools” highlights, there are some concerns to address.

Code signed

What data pro doesn’t love integrity? #DBCCCHECK4EVER

Thanks to a donation from dbatools contributors, Data Masterminds, dbatools is legit, code-signed, Enterprise software.

Code signing requires a code signing certificate. Obtaining a globally recognized code signing certificate isn’t easy and took me about a month. A ton of paperwork & multiple proofs of identity were required, but I did it because I wanted dbatools to be as professional as possible.

What are code signing certs? DigiCert says:

Code Signing Certificates are used by software developers to digitally sign apps, drivers, and software programs as a way for end-users to verify that the code they receive has not been altered or compromised by a third party. They include your signature, your company’s name, and if desired, a timestamp.

Our signatures do include a timestamp, and I personally sign every release that goes into the PowerShell Gallery and chocolately. Rob Sewell and I are the only two people with access to the certificate. And while Rob can also potentially sign dbatools, he uses the certificate solely to sign our sister project, dbachecks.

Limited permissions to merge

Going back to that article on SQLShack.com: they talk about diffusion of responsibility. Basically, the human impulse to say “another developer is looking, so I don’t have to.”

Only six of us can merge code into the dbatools master branch, and only six of us ever have. How did we determine this number? We executed the following command and threw the results into Excel:

git log --graph --abbrev-commit --decorate --first-parent master --merges --pretty=format:%h-%aN

Five of the six people (Me, Shawn, Fred, Rob, Simone and Stuart) who can currently merge code intro dev/master are current/former MVPs or Microsoft employees. Our primary C# library developer, Friedrich Weinmann, is a former MVP and current Security PFE at Microsoft. Friedrich approves all C# code. The other dbatools code is mostly approved by me and fellow MVPs Shawn Melton and Stuart Moore.

We are all known by Microsoft and have visible community profiles. You’ll notice that I’m the primary merger, but if you watch our repo, you’ll also notice Shawn spends a good deal of time evaluating and testing code, even after I’ve merged it and after it’s been tested by appveyor & Pester.

Manageable code base

dbatools also has a decently manageable code base. Here are some stats about our code, courtesy of Simone who used cloc to produce this pretty chart.

Note that over 35% of our PowerShell code is comments or comment-based help 😊

You can compile dbatools.dll yourself

Our dbatools C# library code can be found in our GitHub repo, AND! You can even set it to compile each time you import dbatools using the $dbatools_alwaysbuildlibrary variable instead of relying on our included dll.

$dbatools_alwaysbuildlibrary = $true
Import-Module dbatools

The module itself handles the compile so that’s all you have to do.

Note that $dbatools_alwaysbuildlibrary is not supported by our PowerShell Gallery or chocolatey releases. If you need to compile the DLL, you’ll need to either clone our repo or download the zip directly from GitHub.

External libraries and programs

Our external libraries and programs come primarily from Microsoft. But like SQL Server Management Studio, we also use community DLLs.

Our XESmartTarget support comes from Data Platform MVP & Formula 1 DBA Gianluca Sartori and nuget.

We also obtained permission to include bcp.exe and sqlcmd.exe, and that came directly from my own installation of SQL Server Management Studio.

While it’d be foolish to make a guaranteed promise that our library is 💯, we do try our best and no malware has ever been detected in my repo root or in our PowerShell Gallery and chocolatey packages.

We’re on chocolatey

Thanks to Paul Broadwith, dbatools is now available in the chocolatey repository, and chocolatey takes package integrity very seriously.

Every version of every package submitted must pass through a rigorous moderation review process before they become publicly available (includes checks for quality, consistency, installation, and validations against VirusTotal).

You can find out more about this review process at chocolatey.org.

We’re in the Microsoft PowerShell Gallery

You can also find us on the PowerShell Gallery which performs an “antivirus scan by using System Center Endpoint Protection”

According to Microsoft, all modules have to meet a minimum quality standard, which includes being free of malware and viruses.

We’re used by Microsoft

Amazingly enough, Microsoft also uses dbatools (and 1999 me is totally tripping out). So while Microsoft does not officially endorse us, they appear to trust that we’re a project with integrity.

David Peter Hansen, SQL Server PFE, has an awesome blog post where he details the tools he uses. dbatools is all up in the mix 😇 Another PFE and former Data Platform MVP, Ryan J. Adams, actually suggests using dbatools to sync logins for Availability Groups.

And I just noticed he even made a video about dbatools!

Also, SQL PFE Patrick Keisler wrote a post on blogs.msdn.microsoft.com which doesn’t use dbatools directly, but does use some of the shared code base that I wrote.

Word has it, we’re also used in the banking industry, the airline industry, the super fancy speedcar industry, the insurance industry, the medical industry and more.

Low-hanging fruit

There’s also some low-hanging fruit that we address, like using ScriptAnalyzer to ensure we follow security best practices. We also update our website’s backend regularly and each of our sites use HTTPS.

Resources

Microsoft itself has written extensively about PowerShell security.

Who’s afraid of PowerShell security?

Who’s afraid of PowerShell security? by former Microsoft PFE Ashley McGlone was an instant classic because it was effective and succinct. This article made so many great points, including:

The improvements in WMF 5.0 (or WMF 4.0 with KB3000850) make PowerShell the worst tool of choice for a hacker when you enable script block logging and system-wide transcription. Hackers will leave fingerprints everywhere, unlike popular CMD utilities. For this reason, PowerShell should be the only tool you allow for remote administration. These features allow you to answer the classic questions who, what, when, where, and how for activities on your servers.

PowerShell Security at Enterprise Customers

PowerShell Security at Enterprise Customers by former Microsoft PFE David das Neves is another highly referenced article. I consider this the definitive, in-depth article about PowerShell security.

This post is practically a book, and even making an outline would be too long. If you need to know anything about PowerShell security, this post is a great reference. Bravo, David!

A Comparison of Shell and Scripting Language Security

A Comparison of Shell and Scripting Language Security by PowerShell’s Security Architect Lee Holmes is another ace.

My favorite part was this easy-to-understand visual bit that shows why PowerShell is mega-secure 💪

I hope this addresses everything your security department needs. If not, post a comment or email me at [email protected] and we’ll see what we can do.

- Chrissy

3 thoughts on “talking to your security team about powershell and dbatools”

  1. Pingback: talking to your security team about powershell and dbatools - How to Code .NET

  2. Pingback: SQL Server - Add Powershell dbatools module - dbafox

  3. Pingback: PowerShell Remoting is not a Vulnerability - System Center Automation

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.