I awoke to a tweet today from Ken Van Hyning (@sqltoolsguy) in my feed:
Another SQL Server Powershell module update! The cross-platform journey is well underway! https://t.co/QspD9iwEmO
— Ken Van Hyning (@sqltoolsguy) April 10, 2018
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:
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.
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.
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.
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:
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!
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!
Pingback: SQL Server Powershell Module On PowerShell 6 Core – Curated SQL
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.
I still love you Jon Kruger.