dbatools & sql on linux

dbatools and linux

As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.

A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as the Linux kernel (we’ve since re-licensed under the more permissive MIT). In addition, dbatools’ all-lower-case naming convention was also inspired by Linux, as most commands executed within Linux are in lower-case and a number of projects use the lower-case naming convention as well.

dbatools on linux

Thanks to Microsoft’s PowerShell and SQL Server teams, dbatools runs on Linux and mac OS!

As covered in our book which’ll be released next year, dbatools in a Month of Lunches, 75% of commands in dbatools are supported by Linux and mac OS.

Commands that are pure-SQL Server, like Get-DbaDatabase or New-DbaLogin, work on Linux and mac OS. Others that rely on WMI or remoting, like Get-DbaDiskSpace or Enable-DbaAgHadr, do not currently work on Linux.

If you’re curious, our docs site will tell you if a command is supported by Linux and mac OS.

And, of course, you can also ask on Linux itself. After installing dbatools, run Get-Command -Module dbatools to see all of the commands that are available.

sql on linux

dbatools also supports SQL on Linux. If it works on SQL Server Management Studio, it’ll work with dbatools as we’re built on the same libraries. Also because SQL on Linux is pretty much the same everything as SQL on Windows.

Interested in learning more? Check out the #1 rated SQL Server on Linux book, Pro SQL Server on Linux by Microsoft’s Bob Ward. This book was actually edited by our friend Anthony Nocentino and it’s rumored that Anthony will contribute some sweet Linux-centric commands to dbatools when the Year of the Linux Desktop arrives, so we’re looking forward to that 😊

Kidding aside, dbatools supports Linux and mac OS in a number of ways. Not only can you run dbatools FROM Linux, you can also connect TO SQL on Linux. We even have fantastic Registered Server support that eases authentication.

to linux / mac os

Connecting to SQL Server (Windows or Linux) from Linux or mac OS is generally done with using an alternative -SqlCredential. So let’s say you follow Microsoft’s guide to setting up SQL on Linux or if you use dbatools’ Docker guide, you’ll likely need to authenticate with the sa or sqladmin account. Execute the following command.

Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential sa

You’ll then be prompted for your password, and voilà! Don’t want to type your password every time? You can reuse it by assigning the credential to a variable.

$cred = Get-Credential sa
Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential $cred

You can also export your credentials to disk using Export-CliXml. The password will be encrypted and only the same user/computer can decrypt.

Get-Credential sa | Export-CliXml -Path C:\temp\creds.xml
$cred = Import-CliXml -Path C:\temp\creds.xml
Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential $cred

Prefer using the Windows Credential Store instead? Check out the PowerShell module, BetterCredentials.

to linux / mac os using registered servers

You can also use Local Registered Servers! This functionality was added in dbatools 1.0

Connect-DbaInstance -SqlInstance sqlonlinux -SqlCredential sa | Add-DbaRegServer -Name “SQL Server on Linux”

From there, you can see it in SQL Server Management Studio’s Local Server Groups.

And the detailed pane

Ohhh! What! Now you can do this without worrying about authentication:

Get-DbaRegServer -ServerName sqlonlinux | Get-DbaDatabase

from dbatools on mac os to sql server on windows

Something that I had super fun doing was joining my Mac Mini to my homelab then authenticating flawlessly with my domain-joined, Windows-based SQL Server. Didn’t even need the -SqlCredential because Integrated Authentication worked 👍

If you cannot join your Linux or mac OS workstation to a Windows domain, you can still use -SqlCredential. This parameter not only supports SQL Logins, but Windows logins as well. So you could connect to a Windows-based SQL Server using an Active Directory account such as ad\sqladmin.

Got any questions, hit up #dbatools on the SQL Server Community Slack at aka.ms/sqlslack and one of our friendly community members will be there to assist.

- Chrissy 🐧