PSCore6 – Installing Latest OpenSSH-Win64 v1.0.0.0beta

This next version of OpenSSH bring more changes and here’s how to configured it.
So, let’s refresh the installation steps so we can remote connect from Windows to Windows, or any other non-Windows Systems using ssh protocol.

For now, this applies to Microsoft Windows [Version 10.0.16299.248].

Where To Get It

Use Chocolatey package manager for Windows to install OpenSSH-Win64. On a new windows system, it will need to be install. Make sure to open PSCore6 console “Run as administrator“.

Then, in PowerShell, execute the following command to install Chocolatey Package Manager for Windows:

[Sourcecode language=”powershell”]
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString(‘’))

When the installation is completed, make sure to exit and open again a PSCore6 console “Run as administrator

Next, check what OpenSSH version is available by execute the following command in PowerShell:

[Sourcecode language=”powershell”]
choco info openssh

The result on screen will provide with the latest version available with the release notes for this package. One of the fixes, clears the issue with setting the ssh services permission it is set back to “Local System“.

Installing OpenSSH from Chocolatey

After verifying and reading the release notes, continue with installing the package. The following command will install OpenSSH-Win64 on the new system.

[Sourcecode language=”powershell”]
choco install openssh

Now that we got the module installed, we need to make some configuration changes before installing the ssh services.

Check Configuration settings

On this latest OpenSSH version there has been changes to the configuration file. There are two configuration files available:

1. sshd_config-default – located on: “C:\Program Files\OpenSSH-Win64“.

The second configuration file will be available after complete the script ssh installation:

2. sshd_config – located on: “C:\ProgramData\ssh”

3. Also, all security key files are copied over from the “C:\Program Files\OpenSH-Win64” folder into the “C:\ProgramData\ssh“.

Remember, before the ssh services installation, the folder “C:\ProgramData\ssh” doesn’t exist.

Remember that any changes to the sshd_config file will requires both ssh services to be restarted.

Steps for Installing SSH Services

So, before executing the “Install-sshd.ps1” script. I’ll make the needed changes are in place in the sshd_config_default file using Notepad:

1. Enable: Port 22
2. Enable: PubkeyAuthentication yes
3. Add PSCore6 Subsystems:

Subsystem powershell C:\Program Files\PowerShell\6.0.1\pwsh.exe -sshs -NoLogo -NoProfile

Also, if it doesn’t already exist, add the Firewall Rule for Port 22:

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

Now, we can proceed with the installation of the SSH Services. And, this installation will include the SSH-Agent Service.

## - Install both ssh services: sshd and ssh-agent:
.\install-sshd.ps1 /SSHAgentFeature

Both the ssh and the ssh-agent service are installed but remains stopped.

The installation also created a ssh folder under “C:\ProgramData” and the sshd_config file with the change we did previously to the sshd_config_default file.

Now, to complete the OpenSSH setup, we execute the following commands:

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

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

Notice, now we got the folder “C:\ProgramData\ssh” populated with all the ssh keys need for connectivity.

We are ready to work with the installed SSH Services.

Starting SSH Services

Here are some final adjustments to make sure both SSH Services will start automaticaly after the system reboots.

## - Set from the Service Startup from "Manual" to "Automatic":
Set-Service sshd -StartupType Automatic
Set-Service ssh-agent -StartupType Automatic

## - Start the SSH services:
Start-Service sshd
Start-Service ssh-agent

Finally, we are ready to test SSH connection to another system.

Testing OpenSSH Connectivity

The installation is complete and both SSH Services are running. In order to test, we open PSCore6 console and use the “Enter-PSSession” command to connect to a Linux system or Windows system using SSH protocol.

## - Connecting to Windows:
Enter-PSSession -HostName sapien01 -UserName max_t

## - Connecting to Windows:
Enter-PSSession -HostName mars -UserName maxt

Now, we are successfully connected to another system using OpenSSH.

Word Of Caution!

All Windows System need to have the same version of OpenSSH in order to connect via ssh protocol, or the connection will failed with the following error message: “Enter-PSSession : The background process reported an error with the following message: The SSH client session has ended with error message: Connection reset by port 22.”

Be Bold!! Learn PowerShell Core!!

PowerShell Get-AzureRMNetworkInterface Customize View

This is an example that can be use in both Windows PowerShell and PowerShell Core to customize the result information from the “Get-AzureRmNetworkInterface” cmdlet. Showing the importance of using the Script-block Expression in the Select-Object statement when querying PowerShell .Net Object.

Executing Get-AzureRmNetworkInterface

After successfully signing on the Azure from, in this case my Windows 10 Ubuntu PowerShell Core prompt, executing the “Get-AzureRmNetworkInterface” command will return lots of information:


Now, to document this information in a proper format, a custom script need to be created.

Thought Process

First identifying which properties are going to be displayed. Let’s pick the following:
1. AdapterName
2. Virtual Machine name
3. Private IPAdress
4. Private IP Allocation Method
5. MAC Address

Now, looking at the previous results of the command, lets look at the ‘VirtualMachine‘ property:


This will display all Virtual Machine network interface in your subscription. But, I’m just interested in getting the Virtual Machine name.

Notice the common separator is the forward-slash ‘/’. We can use the .NET split() method to extract the *Virtual Machine name value.


*Note: Notice the use of single-quote forward-slash

This way we can list all the separate values belonging to the “*.Id” property.
So, in order to access the Virtual Machine name, we count the listed values from 0 thru 7. We found the name is on #6, then use the number to extract the value.


How About The Ip Configutation section?

in the case of extracting information from the “IpConfiguration” property, we can execute the following line to list all available properties and its values:


This makes it much easier to extract information by just pick and chose properties.

Custom script code

Now that we know how to extract value, the block of code would look like:

## - Get VM Physical Machines IPAddress:
$IpConfig = `
Get-AzureRmNetworkInterface `
| Select-Object @{ label = "AdapterName"; Expression = { $_.Name } },
@{ label = "VMname"; Expression = { $_.VirtualMachine.Id.Split('/')[8] } },
@{ label = "PrivateIpAddress"; Expression = { $_.IpConfigurations.PrivateIpAddress } },
@{ label = "PrivateIpAllocMethod"; Expression = { $_.IpConfigurations.PrivateIpAllocationMethod } },

$IpConfig | Format-Table -AutoSize;

In the above sample code, the results are saved into a PowerShell variable for better output formatting.


Although I’m only showing extracting information from the Get-AzureRMNetworkInterface command, this can apply to any PowerShell cmdlet that provide such complex properties values. This can apply to both Windows PowerShell and PowerShell Core.

Be Bold!! Learn PowerShell Core!!

SSMS Version 17.4 no more SQLPS Module

It was just a matter of time, as it was already mention in previous SSMS (SQL Server Management Studio) documentation that SQLPS module was going to be deprecated and replace with the new SQLServer module.

See SSMS download information at:

After SSMS Version 17.4 was release back in December, SQLPS module is no longer available. So, if you try to use the “Start PowerShell” from any of the database object, you’ll get the message “No SQL Server cmdlets found…” popup message.

New SQLServer PowerShell Module

But, no worries! Both the SSMS link and the popup message tell you where to get the new *SQLServer PowerShell module as is a separate installation from the PowerShell Gallery.

PowerShell Gallery SQLServer PowerShell Module, Get here:

One thing to point out, this module is only meant to be use on Windows PowerShell.

In other words, it will not work in PSCore6.

Housekeeping Needed

Now, there’s the chance previous SSMS installations have left the older SQLPS PowerShell Module in the system.

As is shown in the previous image, the variable $env:PSModulePath contains the path to the existing SQLPS module(s).

Either, remove the path manually using PowerShell, or thru the GUI System “Environment Variable“.

Or better yet, if you’re using SAPIEN Technologies, Inc. “PowerShell Studioproduct, the n use the Cache Editor feature to manage your existing PowerShell Modules. Check out the blog post and video about this feature at:

Video featuring PowerShell Studio Cache Editor

Option for PSCore

The only way to use PSCore6 to work with SQLServer cross-platform, is using the SMO (SQLServer Management Objects) for .NETCore, which is available in NuGet. For more information in how to install it, check my blog post at:

The only downside, you need to create the script from scratch. There’s plenty of documentation about SMO to overcome this hurdle. Most important, you are  sharpen your PowerShell scripting skills.

Don’t forget that before install any artifacts from PowerShell Gallery, NuGet, or Chocolatey the console shell need to be open “as an Administrator“.

Be Bold!! Learn PowerShell Core!!

PSCore6 – Version 6.0.1 is out of the oven!

Yes! Go and get while it still hot.  The Microsoft PowerShell Team is making it happen and they are not stopping.

Need to know more about the PSCore Roadmap, the check this link:

Keep mind, that it always take few hour for some of the links to be update.  So, the quick way to download the latest version is to go directly to the “Release“page:

Then, select the OS version for PSCore.

While we await for Ubuntu (or other) Repositories gets the latest update, you can use the debian installation format after downloading the file :

sudo dpkg -i *.deb 

Don’t forget to always update the help documentation using “Update-Help -force” with Administrator privileges.

Also, notice that previously installed PowerShellGalley Modules remains installed.

Be Bold!! Learn PowerShell Core!!


PSCore6 – Nuget Microsoft.SqlServer.SqlManagementObjects latest Package (v140.17218.0) Broken

This is the SMO (SqlServer Management Objects) package use to create PSCore6 scripts to connect and manage SQL Server on Windows, Linux, and Azure.

But today, I found out the latest version “140.17218.0″ is broken. I had to rolled back to use an older version “140.17199.0” to get it to work again.

You can find the information about this package in this link:

This NuGet SMO package version is built on .NETCore 2.0 for PSCore6, and will not install in Windows PowerShell.

Installing SMO Package

To *install the previous SMO package version “140.17199.0“, use the following command:

Install-Package Microsoft.SqlServer.SqlManagementObjects -RequiredVersion '140.17199.0'

*Note: Need to install as an Administrator.

If  the newer SMO version “140.17218.0” is installed then it will not connect. There are no errors, or failures displayed.  (See image)

This issue has been reported to NuGet SMO owners and hopefully will be resolved soon.

Testing SMO in PSCore6

Here’s the PSCore6 script for SMO testing. The script will work in both Windows and Linux.

## - 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)

# 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 Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'System01,1451'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Turn ON below for Linux:
## - (Connection to Linux SQL Server multi-instance sample)
# $SQLServerInstanceName = 'LinuxSystem02'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - 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);

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

## - End of Code

Most Important

In order for this to work, NuGet needs to be installed first. The following *code block will help to check if it’s already installed. And, if not, then it will install NuGet in PSCore6.

# 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
Write-Host "NuGet Already Exist! No Need to install."

*Note: Thanks to the SMO guys for providing this code block to get me started testing.

Also, if you already installed the buggy NuGet SMO version, remember to use the following command to uninstall the package:

uninstall-package Microsoft.SqlServer.SqlManagementObjects

I’m hoping this blog post will help in any way.

Be Bold!! Learn PowerShell Core!!

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:

iex ((New-Object System.Net.WebClient).DownloadString('')

*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.

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:

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: 

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“:

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:

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

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:
Microsoft Windows Bash Shell:
Microsoft Academy:
Microsoft Channel 9:
Microsoft MVP Blog:
Microsoft SQL Server Docs:
Microsoft PowerShell Blog: