PowerShell 6 Core on Linux with the SQL Server PowerShell module: A glimpse of the cross-platform future

“I picked the wrong week to stop sniffing glue”

I awoke to a tweet today from Ken Van Hyning (@sqltoolsguy) in my feed:

 

 

So, I bit: the tweet he referenced was announcing a new version of the SQL Server module (21.0.17240). Here’s a quick list of the updates included:

  • Added Get-SqlBackupHistory cmdlet
  • Ported PS Provider to .NET Core for PowerShell 6 support 
  • Ported a subset of cmdlets to .NET Core for PowerShell 6 support 
  • Powershell 6 support on macOS and Linux in Preview. 
  • To use SqlServer provider on macOS and Linux mount it using a new PSDrive. Examples in documentation.
  • Removed restriction of 64-bit OS for this module. Note: Invoke-Sqlcmd cmdlet is the only cmdlet not supported on 32-bit OS.

The bold lines are my emphasis: with PowerShell 6 support for Linux and macOS, that opens up new avenues for connecting to and automating SQL Server from any platform. While you could connect to a Linux-based SQL Server with the PowerShell module before, you had to do it from Windows. No longer! This is exciting stuff. I couldn’t wait to take it for a spin, so I set up a quick demo environment to test it out.

Spin me right round

Here’s my test set up, which is running on a Hyper-V VM locally:

Honestly, I can’t fault how easy it is to get this all up and running quickly, even without a dedicated bash script. Here’s a quick rundown of the commands you’d need to run (again, this for Fedora/CentOS/RHEL; using other distributions like Ubuntu means your process may-slash-will differ. Google is your friend):

# Add the PowerShell Core 6 repository
sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/microsoft.repo

#Install PowerShell 6 Core
sudo yum install -y powershell

# Install docker
sudo yum install docker

# Start docker
sudo systemctl start docker

# Download the SQL Server 2017 latest docker image
sudo docker pull microsoft/mssql-server-linux:2017-latest

# Run the image (feel free to use a better password, provided as an example only)
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P@55w0rd' -p 1401:1433 --name sqlserv -d microsoft/mssql-server-linux:2017-latest

# Verify that the image is running
sudo docker ps -a

If everything worked as advertised, your final command should output something like so:

If you haven’t ever tried using docker before to run a SQL Server, it’s pretty nifty to use to quickly get an instance up and running for testing or development purposes. The most crucial bit, though, is when we started up the container, we did a port map with the -p parameter. We’re telling our host that local port 1401 will map to the container port 1433. If we want to connect to this container’s instance, we won’t be connecting on the default port of 1433, we’ll need to use 1401 (which is a very important detail later).

You can test the connectivity a bunch of different ways, but in the interest of brevity, I’m going to get to the part that you probably clicked through this blog post to see: connecting via the SQL Server PowerShell module.

Stop me if you’ve heard this one before…

Let’s do what we do all the time with SQL Server and PowerShell: let’s load up the module in a session. To get started, we need to run PowerShell on Linux first by typing in the following command:

# Run PowerShell (as a superuser, aka, as Administrator)
sudo pwsh

That will launch pscore6 and you’ll be left with the familiar PowerShell prompt:

From here on out, it’s all PowerShell, all the time. First, let’s install the module:

Install-Module sqlserver

 

With that done, now it’s time to import the module with import-module sqlserver . Did it work? Let’s try a simple Get-Command  to see:

Solid. Better yet, check out Get-PSDrive :

It’s our old friend, the SQLSERVER:\ path! Awesome. Now, the REAL fun begins.

I’m the map I’m the map

So far so good: we’re heading right down the same path that I’ve shown before, which is browsing a SQL Server instance from the module. In Windows, that’s really simple to do with PowerShell and the SQL Server module. Part of the reason for that is that, at least in a Windows Active Directory environment PowerShell runs under our user credentials (or someone else’s if you use “Run as a different user…”). Here, though, in this quick and dirty Linux/SQL Server container environment, we don’t quite have that luxury. If we try to browse a SQLSERVER:\ path in our window, it will assume that the localuser account I’m running as has access to the instance, and it doesn’t. I only minimally configured the container, so really, all I have is an “sa” account. So before we get started, we need to take an extra step, and that’s to map a custom PSDrive to our container’s instance using a credential object that holds our SQL Server login info. Here’s what we need to do:

First, let’s create a credential object by calling “Get-Credential:”

$Cred = Get-Credential

The $Cred variable now has our user ID and password stored as a secure object. Next, we’re going to create a custom PS drive object and map it to the root of the SQLSERVER:\ PS Drive that already exists when we imported the module. Why? Without this step, the drive just assumes it’s going to authenticate with the local user credentials. Since we’re using SQL Authentication, we need to create a whole new drive with the credentials we saved above, like this:

New-PSDrive -PSProvider sqlserver -root "SQLSERVER:\SQL\localhost,1401\default" -name container2017 -Credential $Cred

If you haven’t used New-PSDrive before, this might look strange, so let’s break this down real quick:

  • New-PSDrive creates a new drive for PowerShell to access. Think of it like mapping a drive. To do this though, it needs a few parameters. The first is the -PSProvider parameter. This tells the cmdlet which libraries to use for the drive. If this were a file system, we’d use, uh, “FileSystem” but since we’re going to leverage SQL Server, we’ll give it the sqlserver value.
  • Next, we need to tell the cmdlet what the “root” of the path is. Again, if this were a file system, this would be a local or network path. For this example, since we’re looking for a specific SQL instance, we give it our SQL connection info. In this example, we’re still using the SQLSERVER:\ path that was loaded as part of the module, but we’re going to give it our server name (localhost) and port number of the container (1401) and the instance name (if there isn’t one, and there isn’t in my example, it’s just ‘default’).
  • We’re going to give this drive a name, container2017
  • Finally, we pass in the credential object we created above

When you run this, if everything works, you’ll see… nothing. Our drive is visible to PowerShell though, through a simple check:

Get-PSdrive | Select name, root

Awesome! Our drive is there and we can see that it used our “sa” credential. Now, to make sure we can browse it, let’s try changing the directory and then doing a Get-ChildItem :

#Connect to msdb in the container's instance, and get tables (including system tables, with -force)
cd container2017:\databases\msdb\tables
Get-ChildItem -force

And it continues down the page. We did it! So that begs the question: are we living in a cross-platform SQL Server PowerShell world?

I don’t think so, at least, not quite yet. The module is coming along nicely, but I wouldn’t call it “production ready” yet. Outside of getting these demos ready, I did hit some weird bugs, and I haven’t tested ALL my day-to-day scripts here yet, either, but this is certainly promising. Needless to say, you should watch the development of the module very intently. I can’t thank the tools team enough for all their hard work, either: these guys bust their humps for users like us. You should tell them thanks!

Oh and one more thing…

Speaking of cross-platform stuff, remember mssql-cli? No reason we can’t use it here, either:

We live in interesting times, friends. Thanks for reading!

 

3 thoughts on “PowerShell 6 Core on Linux with the SQL Server PowerShell module: A glimpse of the cross-platform future”

  1. Pingback: SQL Server Powershell Module On PowerShell 6 Core – Curated SQL

  2. Jon Kruger

    Dead bash lovers are rolling over in their graves with the thought of Powershell on Linux 🙂

    Seriously though, this is really cool that they’re doing this.

Comments are closed.