PowerShell Core–Updated setup OpenSSH in Windows and Linux

It’s been over a year since my last post on “PowerShell Open Source – Windows PSRemoting to Linux with OpenSSH”. A lot has change, so here’s the updated version.

Linux OpenSSH installation

In Linux (Ubuntu), open a terminal (Bash) session.

Install the following *packages:

sudo apt install openssh-server
sudo apt install openssh-client

*Note: The system will let you know if they already exist.

Need to configure the OpenSSH config file:

sudo gedit /etc/ssh/sshd_config

The, add following line in the “subsystem” area:

Subsystem powershell pwsh.exe -sshs -NoLogo -NoProfile

Proceed to save the file.

Now, execute the following lines:

sudo ssh-keygen –A

Restart the ‘ssh’ service by executing the following command:

sudo service ssh restart

Windows OpenSSH installation

In *Windows Client or Server, open Services to ‘Stop‘/’Disable‘ both SSH Broker and SSH Proxy.

*Note: Latest Windows Insider Builds having the following services previously installed: SSH Broker and SSH Proxy

Open PowerShell Core Console (Run as Administrator):


First thing, make sure Chocolatey is installed in PowerShell Core: https://chocolatey.org/install

iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1')

*note: Chocolatey Install instructions will run ‘Set-ExecutionPolity Bypass’. The problem is, it won’t change it back to the previous setting.
Make sure to run “Get-ExecutionPolicy” to verify current settings.

Installing OpenSSH package from Chocolatey:

choco install openssh

Close/Reopen PowerShell Core (Run as Administrator), and execute the following command:


Change Directory to the OpenSSH folder:

cd 'C:\Program Files\OpenSSH-Win64\'

Now, we need to make changes to the sshd_config file with Notepad:

Notepad sshd_config

Need to enabled the following commented out lines:

Port 22
PasswordAuthentication yes
PubkeyAuthentication yes

Finally, add the subsystem line to include PowerShell Core path:

Subsystem     powershell    C:/Program Files/PowerShell/6.0.0-rc.2/pwsh.exe -sshs -NoLogo –NoProfile

Save the file and we are ready to configure the firewall rule for port 22.

Windows Firewall Port 22 Setup

Next, confirm that there are no other TCP ports using port 22:

netstat -anop TCP

Now, add the SSH firewall rule for using port 22:

netsh advfirewall firewall add rule name=SSHPort22 dir=in action=allow protocol=TCP localport=22

Open Firewall app and verify it’s added.

Completing Windows OpenSSH Installation

The following steps are essential for the sshd service to start without any issues. Make sure to be at the OpenSSH folder:

## - Generate SSH keys:
ssh-keygen -A

## - Execute both fix permissions scripts:
.\FixHostFilePermissions.ps1 -confirm:$false

## - Install both ssh services: sshd and ssh-agent:

Then, set both sshd and ssh-agent services set to start automatically.

Set-Service sshd -StartupType Automatic
Set-Service ssh-agent -StartupType Automatic

At this point, only start service sshd which will turned on the ssh-agent service.

Start-Service sshd
#Start-Service ssh-agent (optional)

Must important, open the *Services MMC console and verify that all running.

*Note: On the server will be needed to set the credential as Local System (see below).

Now, proceed to test connectivity between two system using PowerShell Core.  To test connectivity could use the following command:

Enter-PSSession -hostname systemname -username UsenameHere

Additional Note:

I found an issue when been a member of a domain but the Domain is Off. Trying to restart ssh service, I get the following error:

PS C:\Program Files\OpenSSH-Win64> Start-Service sshd
Start-Service : Failed to start service 'sshd (sshd)'.
At line:1 char:1
+ Start-Service sshd
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OpenError: (System.ServiceProcess.ServiceController:ServiceController) [Start-Service], ServiceCommandException
+ FullyQualifiedErrorId : StartServiceFailed,Microsoft.PowerShell.Commands.StartServiceCommand

Or trying to manually start the “sshd” service using the Services MMC:

This error was due to missing a step in the installation:

Resolution: Thanks to Github Win32-OpenSSH @bagajjal provided the following steps:

## - Fixing sshd service not starting with the NET Service credentials:
.\FixHostFilePermissions.ps1 -Confirm:$false

This resolved the sshd start failure. (see below)

Awareness on Installing PowerShell Modules

As you are all aware of the upcoming PowerShell Core, and the impact it will bring in our cross-platform infrastructure. For now, both Windows Powershell and Powershell Core will co-exist side by side. Also, our Powershell skill sets will still be on a high degree of demand in the workplace. This is not going to slow down.

So, let’s create some awareness when installing Powershell modules. Now, we’ve seen a raise of *core modules which are targeted to be use with PowerShell Core for the purpose of installing them in either Windows and/or non-Windows environments. But at the same time, and I have experience, installing different versions of the same module in Windows PowerShell.

Let’s take for example AzureRM modules.

Cloud Module Balance

Let me say you will possibly experience this in the following situation:

1. If you’re install Visual Studio with the Azure SDK
2. If you’re a Developer which are trying to keep up with the latest module but forget to remove the old ones.
3. Also, installing modules from different sources.

Which sources are currently available for installing Azure modules?
(Just to name a few)

1. PowerShell Gallery
2. Azure Download
3. Azure SDK’s (VS Studio installation or standalone)

So, having multiple versions of the same modules may lead to issues such as: deprecated commands, invalid parameters/paramterset, and all leading to broken automation scripts.

It’s been suggested to use the PowerShell Gallery to get the Azure PowerShell Modules. But, always to the proper search for the latest version.

Using Powershell Gallery as the main repository for grabbing PowerShell modules, you can query for the modules before installing them:

1. Set the main repository to be “PowerShell Gallery”:

## - Set PowerShell Gallery Repo:
Set-PSRepository `
-Name "PSGallery" `
-InstallationPolicy Trusted;

2. Find the modules you want to install: (In this case, “AzureRM”)

## - Find Module(s) in PowerShell Gallery for Windows PowerShell:
Find-Module -Name AzureRM

## - Find Module(s) in PowerShell Gallery for PowerShell Core:
Find-Module -Name AzureRM.Netcore

Now, you can proceed to install the module:

1. Proceed to get the module from PowerShell *Gallery:

## - Get/install Module(s) from PowerShell Gallery:
Install-Module -Name AzureRM `
-Repository "PSGallery";

*Note: Installing AzureRM.Netcore in Linux, you need to use ‘sudo pwsh’.

2. List the installed module(s):(Windows and Linux)

## - List of installed AzureRM modules:
Get-Module -ListAvailable AzureRM* `
| Select name, version, path;

At this point, you can start building scripts.

Working with PowerShell Gallery

The following series of commands will get you started working with PowerShell Modules.

1. List all installed modules in system and can help to spot duplicates:

## - List of installed all PowerShell modules:
Get-Module -ListAvailable `
| Select Name, Version, Path `
| Sort-Object Name, Version;

2. List installed modules locations in the PSModulePath variable:

## - List installed modules locations

These two commands will give you the necessary information to identify and locate all *”installed” modules in yours system.

*Note: This will not include manually custom modules as the are not install thru PowerShell Gallery, or outside of the standard PowerShell Module folders.

Managing your Modules

In order to prevent cluttering your system with modules you may not use, then its time to do some module updates and/or cleanup.

From Github post “…Moving forward, we recommend using the PowerShell Gallery to get the Azure PowerShell modules. We are looking to push out updates to AzureRM more often since we no longer need to update every module we ship in AzureRM, which will speed up the install/update. …

Taking advantage of PowerShell Gallery commands, we can update our installed modules:

# Updating the modules in AzureRM
Update-Module -Name AzureRM

This step should prevent us installing multiple versions of the same module. (Update) – But, I discovered that this will cause a duplicate module (different versions) after the Update-Module is completed.

What about module(s) cleanup? The following one-liner can be use to cleanup/remove module(s) that install from the PowerShell Gallery. In the case of cleaning up all AzureRM modules, the following command should remove all *modules:

## - This will remove all AzureRM submodules "AzureRM.*:
Get-Module -ListAvailable `
| where-Object {$_.Name -like "AzureRM*"} `
| Uninstall-Module;

## - This will remove the AzureRM main module:
Get-Module -ListAvailable `
| where-Object {$_.Name -like "AzureRM"} `
| Uninstall-Module;

*Note: This command will work as long as you have use the Install-Module to grab the modules from PowerShell Gallery. And, it will take some time to complete.

This way you can start clean and reinstall the modules!

Then, restart your PowerShell session, and use the following command to Get-Module again to list all existing modules.


Side-By-Side PowerShell Modules

As, the PowerShell Gallery has become the main repository for PowerShell Module. The Microsoft PowerShell Team has provided a special module to allow Windows Modules to work side-by-side with PowerShell Core:

Github post: “… This is currently by-design as we deliberately wanted Windows PowerShell and PSCore6 to work side-by-side with predictability. …

This will assist in identifying what else need to be done PowerShell Core. Just give it a try!

If you find issues with Windows Modules in PowerShell Core, then let the Microsoft PowerShell Team know in Github:


Managing modules when using PowerShell Gallery still is a manual process but easy to use, and even better if you build your own solution. I some instances you may decide to us the old delete or move command to get rid of unwanted modules.

Just take the necessary precautions, and remember, that you could install it back if needed.

Streamlining SQL Server Management Objects (SMO) in PowerShell Core

I’ve been recently posting about getting SQL Server Management Objects (SMO) Framework to work in PowerShell Core in both Windows and Linux Systems. So, here’s the revised blog post as the method has kept improving. This way you can start creating some cross-platform SMO PowerShell Core scripts in your environments.

It will works the following way:
1. Windows connecting to Windows SQL Server.
2. Windows connecting to Linux SQL Server.
3. Linux connecting to Linux SQL Server.
4. *Linux connecting to Windows SQL Server.

*Note: Any issues with firewall connecting from Linux to Windows, can be solved by creating the inbound rule for Linux in Windows Firewall.

How to get the SMO for PowerShell Core?

It’s easy! You can get it from NuGet Gallery using PowerShell Core Console. Just make sure you open PowerShell Core as an Administrator to avoid any installation issues.

You could use the following one-liners to find and install the recent SMO package. The following “if-else” code snippet can execute in either Windows or Linux PowerShell Core console.

# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux)
if (-not (Get-PackageSource | Where-Object{$_.Name -eq 'Nuget'}))
Register-PackageSource -Name Nuget -ProviderName NuGet -Location https://www.nuget.org/api/v2
Write-Host "NuGet Already Exist! No Need to install."

Installing SMO from NuGet Gallery

After we verified NuGet Package Management is already installed in our system, then we can proceed in Find/Install “SQL Server Management Objects (SMO) Framework“. The current version is “140.17199.0”.

Execute the following one-liner by using the Find-Package to make sure is available. Then, do the install-package command

## - Check that the NuGet feed is available and has the SMO package:
Find-Package -Name Microsoft.SqlServer.SqlManagementObjects

## - Install latest SMO package from NuGet:
Install-Package -Name Microsoft.sqlserver.SqlManagementObjects -Scope CurrentUser

## - Next Line Confirmed Installation:
Get-Package Microsoft.SqlServer.SqlManagementObjects

As of today (November 6th, 2017), the current version of Microsoft.SqlServer.SqlManagementObjects is 140.17199.0. And, it can be installed on either Windows and Linux systems from NuGet.

For more NuGet information about the SMO package, click on the following link: https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects

Locating SMO Assemblies and connect to SQL Server

In order to use SMO in PowerShell, we need to know where they are installed. The next one-liner gets the NuGet location to build the path of the SMO installed assemblies.

## - Help find and save the location of the SMO dll's in a PowerShell variable:
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netcoreapp2.0)

The SMO path is saved. We are ready to load the needed SMO assemblies, to connect and work with SQL Server. The code snippet below will load the SMO assemblies, connect to SQL Server providing necessary credentials:

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

## - Prepare connection and credential strings for SQL Server:
## - (Connection to Linux SQL Server single instance sample)
$SQLServerInstanceName = 'lxSql00'; $SQLUserName = 'sa'; $sqlPwd = '$Pswrd1!';

## - (Connection to Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'winSql01,1450'; $SQLUserName = 'sa'; $sqlPwd = '$Pswrd1!';

## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

In the previous code sample, I included some variance in providing the SQl Server instance for cross-platform use:
1. In Linux, either using the “SqlServername” or, “IP-Address“.
2. In Windows, either using the “SqlServerName“, or “IP-Address“, or in the case of mutliple instance “SqlServerName,Port“.

Now that the SMO connection to the SQL Server has been established, then we can continue to explore our options using .NET SMO Framework. The Code snippet below shows how to display some of the SQL Server SMO information .NET properties:

## - SMO Get SQL Server Info:

$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - End of Code

More SMO Options…

There are additioanl sources providing SMO dll’s:
1.NuGet SMO: https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/#
2.GitHub SQLToolService: https://github.com/Microsoft/sqltoolsservice
3.Installing SQL Server mssql-scripter(Python-based): https://github.com/Microsoft/sql-xplat-cli

I’ve been using GitHub “SqlToolsService” for some time now and it works closs-platform. At the same time, I’ve been keeping it up-to-date:


I dare to say! Using .NET SQL Server Management Objects (SMO) Framework, let you be flexible adding control over your scripting. Keep in mind, this is well documented in Microsoft MSDN site: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/sql-server-management-objects-smo-programming-guide

My number one choice is to use NuGet Package Management. Although, you can play around with the GitHub SqlToolsService version as it gets frequent updates. The trick in using the GitHub version, is to add the path to where the Dll’s are stored and you’re good to go.

Just Dare to Experiment! Keep learning PowerShell!

Special Thanks to Microsoft: Matteo Taveggia and  David Shiflet for providing me with Nuget PowerShell code piece. I just change it a little!

PowerShell Core useful cross-platform variables

In PowerShell Core, there are three useful variable you can use in a cross-platform scripts. Then, the script can have the necessary logic to response based on the environment it is running.

Use the cmdlet Get-Variable to find them, and keep in mind, these variables are not found in Windows PowerShell 5.x.

Get-Variable Is*

Although, the results will display four variable, but let’s pay attention to three of them. Below are the variables with their default values:

IsLinux                            False
IsOSX                              False
IsWindows                    True

These three variables can help in identifying which Operating System the script are been executed.  This way just adding the necessary logic, in order to take the correct action.

For example, you can want to execute a Python code on different systems.

In Windows, if Anaconda (Python 3.6) was installed then Python executable is located at: C:\ProgramData\Anaconda3\python.exe

In Ubuntu Linux, Anaconda (Python 3.6) may be found on user Home folder, then it can be located at:  /home/username/anaconda3/bin/python

Of course, Python location may vary if different versions are installed, and/or installation default location was change.

Sample Cross-platform Code Snippet

Here’s a simple script code logic to accomplished cross-platform behavior.  In my environment the sample code was executes in a Windows system.

if ($IsLinux -eq $true)
Write-Host "Executing in Linux - $($psversionTable.platform.ToString())" -ForegroundColor Green;
#Linux Python path:
if ((Test-Path '/home/maxt/anaconda3/bin/python') -eq $true)
Write-Host "Linux Python Path Validated" -ForegroundColor Yellow;
if ($IsWindows -eq $true)
Write-Host "Executing in Windows - $($psversionTable.platform.ToString())" -ForegroundColor Green;
#Windows Python path:
if ((Test-Path 'C:\ProgramData\Anaconda3\python.exe') -eq $true)
Write-Host "Windows Python Path Validated" -ForegroundColor Yellow;
Write-Host "Can't execute under this OS!";

Go ahead and try the same code in Linux PowerShell Core. Of course, this code can be improved, but let’s leave it to your creativity. Start simple and grow!

By the way, did you know you can use SAPIEN Technologies PrimalScript to execute PowerShell Core scripts. Yes! It is possible!

Check the following SAPIEN Blog post about it: https://www.sapien.com/blog/2017/09/15/using-powershell-core-in-primalscript/

SAPIEN PowerShell Tools at Orlando Microsoft Ignite Conference 2017

As I started a new role working for SAPIEN Technologies as their Technology Evangelist in September, I had the opportunity to be working with them a the Microsoft Ignite Conference in Orlando.

Greatly appreciate this opportunity and the chance the meet everyone interested in PowerShell as well as our SAPIEN PowerShell Tools at the event.

Feel free to reach out, keep asking about our product, product services, and must important, give us feedback on how to make it better.

Don’t forget to check out our blog posts, support forums, YouTube videos, and specially the “Information Center” under the following link: https://www.sapien.com/support



PowerShell Core Stable SQL Server SMO Assemblies and DataRow objects

Yes! Just recently I downloaded the latest SQL Server SMO assemblies that can be use with PowerShell Core in both Linux and Windows. You can find them in Github under Microsoft SqlToolsService. But, you’ll need to extract only the necessary DLL’s before you can start creating your PowerShell Core SMO scripts. There’s no installation program, as this is installed manually.

There’s one requirement I would suggest to do. Download and install .NET Core 2.0.
To download click this link: https://www.microsoft.com/net/download/core

Manual SMO Installation

The latest SqlToolsService version can be found at this Github link: https://github.com/Microsoft/sqltoolsservice/releases
I’m currently using is V1.1.0-alpha.31.

Just download the file for the OS you’re working:

1. In Windows, download the zip file “Microsoft.SqlTools.ServiceLayer-win-x64-netcoreapp2.0.zip
a. Open the zip file.
b. In the zip app, select only the following dll’s:


c. Extract all selected dll’s into your *PowerShell Core Beta folder “C:\Program Files\PowerShell\6.0.0-beta.x”.

2. In Ubuntu Linux, download the tar file “Microsoft.SqlTools.ServiceLayer-ubuntu16-x64-netcoreapp2.0.tar.gz“.
a. To open the file, use either Desktop Nautilus, or use the command-line tar command.
b. In your home folder, create a folder for the dll’s you’re going to extract (for example: mkdir sqltoolsservice).
c. In the tar app, Select only the following dll’s:


d. Extract the files into the folder you created.

Definitely, using the GUI tar or zip application seems better as you can use the Ctrl key to individually highlight the files to extract.

*Note: Keep in mind, when you add these dll’s into the PowerShell Core folder, uninstalling PowerShell beta won’t removed them. You must manually delete them and the folder.

Verifying SMO Works

In order to test SQLServer Management Objects working with PowerShell Core, we are going to use the following PowerShell Core script snippet:

# - Windows Hack:
cd 'C:\Program Files\PowerShell\6.0.0-beta.x'

# - Linux Hack:
cd /home/username/SqlToolsServices

# - Loading necessary SMO Assemblies:
$Assem = ("Microsoft.SqlServer.Management.Sdk.Sfc", `
"Microsoft.SqlServer.Smo", `
Add-Type -AssemblyName $Assem

# - Prepare variables for connection strings to SQL Server using SQL Authentication:
$SQLServerInstanceName = 'Sql01,1451';
$SQLUserName = 'sauser'; $sqlPwd = '$MyPwd99!';

## - Prepare connection to SQL Server:
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO sample 1
## -> Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - SMO sample 2
## -> To execute T-SQL Query:

# - Prepare query string variable:
$SqlQuery = "SP_WHO2";

# - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);

# - Display T-SQL Query results:
$result.tables.Rows | Select-object -first 10 $_ | Format-Table -AutoSize;

When executing the code both Windows and Linux, make sure you are in the folder you installed the dll’s files or it won’t execute.

In Window, in PowerShell Core console stays in folder: “C:\Program Files\PowerShell\6.0.0-beta.x”.

In Ubuntu Linux, in PowerShell Core console, change directory to “SqlToolsService”.

The above script will verify your manual installation of the SMO dll’s in PowerShell Core was successful. Now, you can use SMO in PowerShell Core in both Linux and Windows. And, most important, the previous issue I describe in my previous blog post “PowerShell Core – Getting SQL Server using ADO.NET Data provider” about the DataRow object has been cleared. So, there’s no need for adding code to fix the object to display data columns and values correctly.

Please, go ahead the give it a try! It’s great that now we can use PowerShell Core in Linux to create .NET object we can use and take advantage of this technology.

FLPSUG goes live at Keiser University Port St. Lucie

Yes, its finally happening! Thanks to Leslie Haviland (Director of Student Services), Dewan Persaud (Program Chair Information Technology), and staff to help me setting this meeting at their Port St. Lucie location.

Everyone is welcome to attend no matter what’s your skill level. I’m hoping that this will be first of many upcoming meetings as this technology is finally On-Demand in the industry. Keep in mind, PowerShell is also available Open Source running on Linux and Mac OS’s.

Most important! Is never too late to start learning about PowerShell.

Please, come over or register at: bit.ly/2u6unrs

Event Address:
Keiser University – Port St. Lucie
9400 SW Discovery Way
(Room 106)
Port St. Lucie, FL 34987

Hope to see you all there!

FLPSUG – Next Online meeting July 26th 2017

I’m working on getting a meeting with Keiser University to allow me to host my Florida PowerShell User Group Monthly meetings at their Port St. Lucie Campus location.  But, in the meantime, I setup July’s Online meeting for Wednesday 26th at 6:30pm (EST).

This month topic:

Working with SQL Server for Linux Cross-Platform

You’re welcome to explore the latest build of SQL Server for Linux, including everything you need to install and connect to SQL Server. He will also look into the tools that are available to use from Linux and / or Windows. Maximo will provide samples on querying for database information using Python/Java and PowerShell between two environments. This will be a demo intensive session you will not want to miss!

To register, click on the following Eventbrite link: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-july-2017-tickets-36113308879?ref=estw

I hope you can joined me in this exciting session!

South Florida SQLSaturday – Working with SQL Server in Linux session

First! Thanks to the organizers, and specially the attendees as they waited patiently to the attend my last session of the day “Working with SQL Server for Linux Cross-Platform”. It proves to be good. Love their interaction and the will to embrace technology.

As, I almost didn’t make it to the event, due to car problem, I one my coworker gave me ride to the event at Nova Southeastern University. I missed giving the early session “SQL Server working with PowerShell and Python” so I ended up merging both sessions into one.

For my surprise, the last session went better than I expected. I ran everything from Azure Cloud which work like a charm, and the attendee were awesome.

Both presentation and all demo scripts were uploaded to the SQLSaturday #627 event site. I hope you all take advantage of the resource link I provided.

In the demo it’s interesting we covered the following on PowerShell Core, Python 3.6 (Anaconda), and SQL Server 2017 (Linux):

* In Window 10, using SSMS v17.1 connecting to SQL Server 2017 in Linux
* In Linux, connect to a Windows Shared folders
* In Windows, using SSMS to restore a Windows Database into Linux SQL Server.
* Sample script using Python tk (Gui) w/pyodbc (SQL connector), and PowerShell displaying PowerShell object in a Gridview.
* Using SMO in Linux with PowerShell.

And, we did covered a lot in a short time.

By the way, I will be giving the same session at IDERA’s Geek Synch webinar, on July 12th, at 11:00am CT/12:00pm ET:

Geek Sync – I Working with SQL Server for Linux Cross-Platform

Once again Thanks to everyone!

I’ll see you all in Orlando SQLSaturday #678 on October 7th!

Using Linux SQL Server SMO in PowerShell Core

Yes! It’s possible. Here’s the information in how to set it up and start doing some PowerShell scripting. But, first understand that everything posted here is still a Work-In-Progress. And, the good news, it’s all Open Source.

I hope you find the following information essential as there’s no really any instruction in how to install these components. So, let’s get started!

Where To Get It!

The Microsoft SQL Tools Service is a set of API that provided SQL Server Data Management capabilities on all system cross-platforms. It provide a small set for SMO dll’s enough to get started.

You can download the file from following Github link: https://github.com/Microsoft/sqltoolsservice 

Here’s the list of available SMO DLL’s currently include in the “SqlToolsService – ServiceLayer” file:


Keep in mind, this list will continue to grow and we hopefully expect more SMO DLL’s added.

Installation pre-requisites

In my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file

As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.

Just in case, here’s the link to download “.NET Core 2.0 Preview 1“: https://www.microsoft.com/net/core/preview#windowscmd

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:

Windows Installation

You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

To make *”Sql Tools Services” to work in PowerShell Core, I had to extract all content in the file into the “C:\Program Files\PowerShell\6.0.0-Beta.x” folder. Remember, this will replace any existing DLL’s on that folder.

*Caution: This steps should be done on a test machine as there’s always a possibility that it could PowerShell Core DLL’s.

Don’t forget that all these components are still in development but this should stopped us from trying and even contributing.

The file you’ll need to download for Windows is: microsoft.sqltools.servicelayer-win-x64-netcoreapp2.0.zip

Please, for now ignore the *microsoft.sqltools.credentials*.  If you install the Credentials DLL’s in the PowerShell Beta folder, PowerShell will not work.

Linux Installation

Now, for Linux is a different story as there’s no need to add the DLL’s in the PowerShell Core folder. You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

I would recommend doing the following steps in the Bash Console:

1. At your /home/user-name location, create the sqltoolsservice folder:

maxt@MyUbuntu01:~$ mkdir sqltoolsservice

2. Change directory and Download the file for Ubuntu:

maxt@MyUbuntu01:~$ cd sqltoolsservice/
maxt@MyUbuntu01:~/sqltoolsservice$ wget https://github.com/Microsoft/sqltoolsservice/releases/download/v1.0.0-alpha.34/microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz

3. Continue extract the *tar.gz into the folder:

maxt@MyUbuntu01:~$ tar -xzvf microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz

That’s it for Linux. Now, you are ready to work with SMO and PowerShell.

Testing SMO in PowerShell Core

This is changing my way I script SMO in PowerShell. As my normal way I’ve been scripting SMO in PowerShell doesn’t work in PowerShell Core. Basically, a few more lines need to be added and now I will use the Add-Type to get the SMO assemblies loaded.

Loading SMO Assemblies

The first step is to load the SMO assemblies needed to start working with SQL Server. So, the following line is finally depricated and won’t work:


The old method I’ve been using for a long time will failed because is expecting the “Property Login …” to be set.

The updated way, has been replaced by the Add-Type with the following essential three assemblies:

## - Loadind SQL Server SMO assemblied needed:
$Assem = (
); Add-Type -AssemblyName $Assem;

The above assemblies are required in order to work since SQL Server SMO 2012 and greater. You can have limited use when connecting to SQL Servers version 2005, and possibly 2000.

Prepare connection parameters for Windows Systems

In Windows systems, we use ‘Integrated Authentication‘. But, here’s where things change a bit since SQL Server 2012 SMO. You will need to prepare the connection parameters, and set the *.UseIntegratedSecurity property to ‘true‘ (the default is ‘false‘). At the same time, you’ll need to set the password to ‘null’ in order to connect successfull.

## - Prepare connection strings and connect to a Windows SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'winUsername';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $null);
$SQLSrvConn.UseIntegratedSecurity = $true;
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Now, you can query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Prepare connection parameters for Linux Systems

For Linux systems, we use ‘SQL Authentication’. Here we add the SQL User password, then passing the value to the SqlConnectionInfo class.  And, the *.UseIntegratedSecurity property by the default is ‘false‘.

## - Prepare connection strings and connect to a Linux SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'sqluser01'; $sqlPwd = '$usrpwd01!';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd)
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Again, you can proceed to query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Please notice in the above image, the Windows 10 Insider Build 16215 Bash Console is running PowerShell Core. This list insider release made it possible for PowerShell Core to be functional again.


As we can see, this opens new opportunities to build cross-platform PowerShell scripts solutions working with SQL Servers in Linux, Windows, and others.

This is very exciting to start experiencing first hand these upcoming changes. I can’t deny that’s it’s challenging but you can’t turn down an opportunity to gain more skills.

Please, take advantage and subscribe to Microsoft Azure. Build, test, and start deploying solutions. Don’t be afraid to be creative. We all learn thru trial and errors!

This is a good time to keep up with what’s going on with technology.

Additional References:

Microsoft Azure: https://azure.microsoft.com/en-us/
Github: https://github.com/
Ubuntu: https://www.ubuntu.com/
Microsoft Windows Bash Shell: https://msdn.microsoft.com/en-us/commandline/wsl/about
Microsoft Academy: https://mva.microsoft.com/
Microsoft Channel 9: https://channel9.msdn.com/
Microsoft MVP Blog: https://blogs.msdn.microsoft.com/mvpawardprogram/
Microsoft SQL Server Docs: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
Microsoft PowerShell Blog: https://blogs.msdn.microsoft.com/powershell/