Checking for PowerShell Core latest version in non-Windows Systems

I’m not sure who don’t get desperate to get the latest version of PowerShell Core when it becomes available. I do!
Sometimes I’m lucky enough to check Github PowerShell repository to find out it has been recently out.

But, this doesn’t means that is available in any of the other repositories! And, it may take a few more hours after it’s released. So, what would you do?

Repository Availability

Let’s take a look at how to check for PowerShell latest version on different non-Windows Systems.

Ubuntu:
apt-cache policy powershell #-> Or, powershell-preview

CentOS:
yum list powershell #-> Or, powershell-preview

MacOS:
## - Display installed current information.
brew cask info powershell

## - Seems when is current it will not display anything.
brew cask list powershell

## - Intended to show if app is outdated:
brew cask outdated powershell

MacOS Home Brew always takes longer for the new release to be available.

Get It Now!

So, if PowerShell Core isn’t available in the package repository, with a few steps you can download and install PowerShell. But, the first thing I do is to remove it before installing.

Ubuntu

## - When PowerShell Core isn't available in their repository: (download and execute install)
cd Downloads
wget https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell_6.1.1-1.ubuntu.18.04_amd64.deb
sudo dpkg -i powershell_6.1.1-1.ubuntu.18.04_amd64.deb

## - When available in Apt/Apt-Get repository:
sudo apt install -y powershell #-> Or, powershell-preview

CentOS

## - When PowerShell Core isn't available in their repository: (download and execute install)
## - Before removing PowerShell - use Invoke-WebRequest cmdlet.
pwsh
invoke-webrequest https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell-6.1.1-1.rhel.7.x86_64.rpm
sudo yum install powershell-6.1.0-1.rhel.7.x86_64.rpm

## - When available in Yum repository:
sudo yum install powershell #-> or, powershell-preview

MacOS

## - When PowerShell Core isn't available in their repository: (download and execute install)
## - Before removing PowerShell - use Invoke-WebRequest cmdlet.
pwsh
invoke-webrequest https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell-6.1.1-osx-x64.pkg
sudo installer -pkg powershell-6.1.0-osx-x64.pkg -target /

## - When available in Home Brew repository:
brew cask install powershell #-> Or, powershell-preview

Make sure, when in doubt, to check the package management documentation for any of the non-Windows Systems.

Currently Available

As of now, at the time of writing this blog post, the following PowerShell Core versions are available:

  • PowerShell Core (GA) Version 6.1.1 – Release on 11/13/2018
  • PowerShell Core Preview Version 6.2.0-preview.2 – Release on 11/15/2018 (Updated)

More Information

Don’t forget to check the installation instruction for PowerShell Core installation in Github at this link.

Go ahead and give it a try!

Installing MS SQL Server in Ubuntu 18.04

This has been an issue for sometime until now. I found the following link that help me install SQL Server on the latest Ubuntu 18.04:

https://askubuntu.com/questions/1032532/how-do-i-install-ms-sql-for-ubuntu-18-04-lts

But, there are few missing steps which can help ease the burden of errors. At the same time, the information is a little out-dated.

But, it works with the following adjustments.

Please Understand!!  This is NOT approved by Microsoft.  Use this method for Test Only!!

Create Your Installation

The following instructions help you download and get the dpkg package ready for you Ubuntu 18.04 SQL Server installation:

  • Create the folders to extract, and make changes to repackage the dpkg SQL Server installation:
cd ${HOME} && mkdir -p tmp/mssql/newpkg/DEBIAN/ && cd tmp/mssql
  • Download the latest version of SQL Server dpkg to the current folder location: (dpkg SQLServer date: 20-Jun-2018 18:03)
wget https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017/pool/main/m/mssql-server/mssql-server_14.0.3029.16-1_amd64.deb
  • Extract the dpkg package:
dpkg-deb -x mssql-server_14.0.3029.16-1_amd64.deb newpkg/
dpkg-deb -e mssql-server_14.0.3029.16-1_amd64.deb newpkg/DEBIAN/
  • Next step will change the OpenSSL version to avoid failure during SQL Server installation:
sed -i -e 's#openssl (<= 1.1.0)#openssl (<= 1.1.0g-2ubuntu4.1)#g' newpkg/DEBIAN/control
cat newpkg/DEBIAN/control | grep openssl
  • Next step it to Repackage the SQL Server installation:
sudo dpkg-deb -b newpkg/ 18.04-mssql-server_14.0.3029.16-1_amd64.deb

At this stage you could try to install SQL Server, but it might failed.  This is needed in order to check what dependencies are missing. Then, make the necessary dependencies installation.

Additional Steps

As of today, July 5th, I went thru a series of trial-and-error to get my SQL Server running on my Ubuntu 18.04.

After executing the following command:

sudo dpkg -i 18.04-mssql-server_14.0.3029.16-1_amd64.deb

But, I got errors:

The following is the list of all my missing dependencies on Ubuntu 18.04 for the SQL Server installation:

dpkg: dependency problems prevent configuration of mssql-server:
mssql-server depends on libjemalloc1; however:
Package libjemalloc1 is not installed.
mssql-server depends on libc++1; however:
Package libc++1 is not installed.
mssql-server depends on libcurl3; however:
Package libcurl3 is not installed.
mssql-server depends on openssl (<= 1.1.0); however: Version of openssl on system is 1.1.0g-2ubuntu4.1. mssql-server depends on python (>= 2.7.0); however:
Package python is not installed.
mssql-server depends on libsss-nss-idmap0; however:
Package libsss-nss-idmap0 is not installed.
mssql-server depends on gawk; however:
Package gawk is not installed.

Now, one thing to understand, if you execute the following command:

sudo apt install -f

It will clear/remove SQL Server installation components, but it also try to install some, but not all of the dependencies.

As is shown in the image, only two of the listed dependencies were installed: “gawk“, and “libsigsegv2” (this one might be from another package not for SQLServer).

So, identifying the missing dependencies can alleviate the burden of more fail attempts to install SQLServer.

The following command will install all on the listed failed dependencies, excluding OpenSSL because another version is already installed.

## - Adding the missing dependencies:
sudo apt install python libjemalloc1 libc++1 libcurl3 libsss-nss-idmap0

After all the missing dependencies are installed than I can proceed to rerun the re-package SQL Server installation for my Ubuntu 18.04. By the way, I already took care of the OpenSSL in the “Create Your Installation” step where I change the version number.

About Python Dependency?

Yes! In Ubuntu 18.04, Python version 3.6.5 is the one installed with the OS. So, Python 2.7 is not installed.

Try running the command: python –version, then python3 –version at the Terminal Console.

In order to install SQL Server in Linux, it need Python 2.7 installed in order for the installation to work. This is why I included Python in the “sudo apt install …” command to be installed with the other missing dependencies.

Finally Ready

So, finally all the dependencies have been installed. Now, I can rerun the SQL Server installation:

sudo dpkg -i 18.04-mssql-server_14.0.3029.16-1_amd64.deb

This time the installation completes without any error.

To verify that SQL Server is running, execute to following command:

sudo service mssql-server status

Next, verify from your Windows client and open SQL Server Management Studio to verify that the Ubuntu 18.04 SQL Server is accessible.

What’s Next?

Well, if you got PowerShell Core installed, then get the SQLServer Module and start to play around working with both the available cmdlets and/or start coding SMO (SQL Server Management Object) PowerShell Core scripts.

Be creative!  Check out my previous blog post “PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform” for more information.

In Summary

This is a hacking technique to be able to install SQL Server in Ubuntu 18.04.  This is not supported by Microsoft, but you will be able to make it work. Basically, is a matter of installing all the missing dependencies, and change the package required OpenSSL version number to the one installed in Ubuntu 18.04.  Then, repackaging the SQL Server installation dpkg file will allow the installation to work.

Special Thanks to the contributor in the UbuntuAsk forum, as without it I won’t have figured out, and made it work

 

Installing PowerShell Core 6-Preview.3 in Ubuntu 18.04 and CentOS 7

As you may notice, only the deb package for PowerShell 6-Preview.3 is available for installation. So, only when it becomes GA (Generally Available), then we’ll see the repository documentation included for Ubuntu 18.04 and CentOS 7 installation.

But, don’t worry! Below are some undocumented steps for installing PowerShell Core Preview.3 release.

Installing PowerShell Core 6-Preview.3

1. Download PowerShell Core dpkg *deb for Linux Ubuntu 18.04 or the *rpm package for CentOS 7 from the Release page, and look:
https://github.com/PowerShell/PowerShell/releases/tag/v6.1.0-preview.3

2. Then, execute the next two lines because the first one will not complete successfully:

-For Ubuntu 18.04 dpkg *deb package-

sudo dpkg -i powershell-preview_6.1.0-preview.3-1.ubuntu.18.04_amd64.deb
sudo apt-get install -f

-For CentOS-

sudo yum install powershell-preview-6.1.0-preview.3-1.rhel.7.x86_64.rpm

In either Ubuntu and CentoOS, the installation will seems that will complete without any failures. But, when typing pwsh will not work.

Use the following workarounds if you feel is convenient to use.  Please skip to the “Things Are Changing!” and check out “Undocumented Tips” section.

Execute PowerShell Core – Workaround #1

Type the full path, and PowerShell Core will start:

/opt/microsoft/powershell/6-preview/pwsh

But, of course we don’t want to do this every time.

Execute PowerShell Core – Workaround #2

So, let Just add the PowerShell Core path to the user ~/.bashrc file, follow with reloading Bashrc:

echo ‘export PATH=”$PATH:/opt/microsoft/powershell/6-preview”‘ >> ~/.bashrc
source ~/.bashrc

After all this done, now we can start PowerShell Core from the Ubuntu 18.04 terminal.

Things Are Changing!  (Added – 06/14/2018)

Please make sure to read the PowerShell Core v6.1.0-preview.3 Release Notes.  It announce changes to the PowerShell Core executable for both Linux Distro and MacOS. So, although the above workaround mention works, you can just open PowerShell Core Console by typing “pwsh-preview”.

Undocumented Tips  (Added 06/16/2018)

Repo installation is available!

Most important to be aware of these few tips. You won’t find PowerShell 6.1.0-Preview.3 in any repository. But, there is a PowerShell-Preview.

Now, uninstalling PowerShell Core if you got the PowerShell 6.1.0-Preview.2 installed, then it need to:

  • Ubuntu

sudo apt remove powershell

  • CentOS 7

sudo yum remove powershell

But, in the case you need to remove or reinstall PowerShell Core Preview.3 in either Ubuntu and CentOS then use:

  • Ubuntu 

sudo apt remove powershell-preview

  • CentOS 7

sudo yum remove powershell-preview

Now, to install PowerShell Core Preview.3, beside been able to use either the dpkg *deb package for Ubuntu or the *rpm package to install it, you can use the following command:

  • Ubuntu

sudo apt install powershell-preview

  • CentOS 7

sudo yum install powershell-preview

It works!

These instruction was not documented at the time of the release of PowerShell Core Preview.3.

Thanks for the PowerShell Core Github Community for all the information available about PowerShell Core installation. I know this documentation will be eventually updated in due time.

 

Be Bold! Learn PowerShell Core!

PSCore6 – Creating a Hybrid Cross-platform SQLServer Script

There’s some discussion around scripting on using Windows PowerShell vs PowerShell Core. So, just pick one? No.
Just think about supporting a cross-platform environment. Use both!

Following my recent post on “PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform“, here’s a sample Hybrid-Script for cross-platform use.

Why not!

We all know the next generation (or evolution) of PowerShell is PowerShell Core. That’s it!
You are still using PowerShell, and Windows PowerShell is not going to be dropped nor removed any time soon.

So, why not start working towards, what I call, “Hybrid-scripting”? Powershell Core provides the necessary elements to help with cross-platform scripting.

In it’s basic code form, could look be something like this:


## - Logic Structure for executing either PowerShell Version:

## - Use Set-StrictMode for debug purpose:
Set-StrictMode -Version 5.1

If ($PSversionTable.PSEdition -eq "Desktop") {
"Windows PowerShell"
}
else {
## - Use Set-StrictMode for debug purpose:
Set-StrictMode -Version 6.1

if ($PSVersionTable.PSEdition -eq "Core") {
If ($IsWindows) {
"WindowsCore"
}
If ($IsLinux) {
"LinuxCore"
}
If ($isMacOS) {
"MacOSCore"
}
}
};

Now, let’s apply this code to a practical sample.

Sample Hybrid-Script

In the following sample script, includes Help information, begin-process-end and with try-catch code structure.
At the same time, the script will output the exception to the screen console with the failed line.

Script function name: Get-DBASQLInformation

Function Get-DBASQLInformation {</pre>
<#
.SYNOPSIS
This is a cross-platform function to Get SQL Server Information.

.DESCRIPTION
This is a cross-platform function to Get SQL Server Information using SQL Authentication.

.PARAMETER UserID
Enter SQL Authentication UserID parameter.

.PARAMETER Password
Enter SQL Authentication Password parameter.

.PARAMETER SQLServerInstance
Enter SQLServerInstance name parameter.

.EXAMPLE
PS> Get-DBASQLInformation -UserID 'sa' -Password '$SqlPwd01!' -SQLServerInstance 'mercury,1433'

.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2018 v5.5.152
Created on: 5/25/2018 8:27 AM
Created by: Maximo Trinidad
Organization: SAPIEN Technologies, Inc.
Filename: Function_Get-DBASQLInformation.ps1
===========================================================================
#>
<pre>[CmdletBinding()]
[OutputType([psobject])]
param
(
[Parameter(Mandatory = $true,
Position = 0)]
[string]
$UserID,
[Parameter(Mandatory = $true,
Position = 1)]
[string]
$Password,
[Parameter(Mandatory = $true,
Position = 2)]
[string]
$SQLServerInstance
)

BEGIN {

## - Internal function:
function GetSqlInfo {
param
(
[parameter(Mandatory = $true, Position = 0)]
[string]
$U,
[parameter(Mandatory = $true, Position = 1)]
[string]
$P,
[parameter(Mandatory = $true, Position = 2)]
[string]
$S
)
Try {
## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($S, $U, $P);
$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;

}
catch {
## - Write Exception to Console:
Write-Host `
"Excepion found on line:`r`n$($error[0].InvocationInfo.line)"+ `
"`r`n$($Error[0].Exception)" `
-ForegroundColor Magenta;

}
}

};

PROCESS {

## - Cross-platform logic:
If ($PSversionTable.PSEdition -eq "Desktop") {
Write-Host "Windows PowerShell"
GetSqlInfo -U $UserID -P $Password -S $SQLServerInstance;
}
else {

if ($PSVersionTable.PSEdition -eq "Core") {
If ($IsWindows) {
Write-Host "Windows PScore";
}
If ($IsLinux) {
Write-Host "Linux PSCore";
}
If ($isMacOS) {
Write-Host "MacOS PSCore";
}
## - execute on non-Windows:
GetSqlInfo -U $UserID -P $Password -S $SQLServerInstance;
}
};

};

END {
## - EndBlock (Optional)
};
};

The heart of the code are stored in the “Begin” section as a Internal-Function GetSQLInfo(). The internal-function will be only executed if it the criteria for each of the different platforms. The Try-Catch is just to trap the error if the SMO connection failed, or to indicate the SMO .NET wasn’t loaded.

Go ahead! Create a script file, copy/paste this code, and load this function. Give it a try cross-platforms: Windows, Linux, and MacOS.

Remember, SQLServer module is a replacement for SQLPS module. I won’t recommend having both modules installed unless you use the namespace_module/cmdlet to identify which module is going to execute the cmdlet.

So make sure to always test your scripts.

What’s Next!

This function still need to worked on, but is functional enough to test-drive and see the results. So, it be modified to support Windows Authentication. Once you start scripting and building functions, you won’t stop thinking what else can be added.

Just keep working on it and learning from the PowerShell Community.

Go Bold! Learn PowerShell Core!

PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform

If you haven’t heard yet! The SQLServer Module is available for Windows, Linux, and MacOS. Yes!
And, with it,now you can even expand your scripting using .NET SQL Server Management Objects to manage your SQL Server Engine cross-platform.

How to get it!

It’s available in PowerShell Galley. Just run the following command to install the module in Windows PowerShell and PowerShell Core.
Yes, you read it! Install in PowerShell Core for Windows, Linux, and MacOS.


Install-Module -Name SQLServer -Force -Scope AllUsers

What’s in it?

Contains all of the SQL Server Management Objects .NET assemblies that will work in both Windows and non-Windows Systems. At the same time, it contains a total of 63 commands. This will support all existing SQL Server 2017(and older) on your network. Of course, there will be some limitations because there might be some features lacking in older features. But, for most use it will work.

It also includes the ability to provision the SQLSERVER: drive when you import the module.


Import-Module SQLServer

Get-PSDrive

If you care for what SMO .NET Assemblies are installed, execute the following commands:


## - Get the SQLServer Module path:
(Get-MOdule -ListAvailable SQLServer).path

## - List of all SQLServer and Analysis Services DLL's:
dir 'C:\Program Files\PowerShell\Modules\SqlServer\21.0.17262\*.dll' `
| Where-Object{$_.basename -match 'SqlServer|Analysis'} `
| Format-Wide;

## Linux CentOS - Total of SQLServer and Analysis Services DLL's:
(Get-ChildItem '/usr/local/share/powershell/Modules/SqlServer/21.0.17262/*.dll' `
| Where-Object{ $_.basename -match 'SqlServer|Analysis' }).count

Using the SQLServer: Drive

Although, I’m not a fan of using SQLServer: drive. This will allow you to navigate thru the SQL Engine like a file system from the console prompt.

In order to use the drive, it need to be recreated with the proper credentials for cross-platform use.
Below steps will create additional SQLServer: drives to another SQLServer on the *network.

###==&gt;For Windows, Linux, MacOS
Import-Module SqlServer

## - New way for Streamlining Get-Credential:
$MyUserName = 'sa'; $MyPassword = ConvertTo-SecureString '$SqlPwd01!' -asplaintext -force;
$MyCred = [System.Management.Automation.PSCredential]::new($MyUserName, $MyPassword)

## - Creating SQLSERVER: connection to Windows SQLServer:
New-PSDrive -PSProvider sqlserver -root "SQLSERVER:\SQL\sapien01,1451\default" -name MyWindowsSQL -Credential $mycred

## - List all SQLSERVER: Drives:
Get-PSDrive *SQL* | Select-Object Name, Provider, Root;

 

Note: In this example, I’m using SQL Authentication.

Now, I can navigate thru my SQLServer objects like a filesystem.


## - Change directory to SQLSERVER: drive:
cd MyWindowsSQL:/databases/sampledb1/tables
dir

Wait! Did you notice I’ve created a SQLServer Drive in MacOS? This is Awesome!
By the way, there’s no Docker involved in here. The fun doesn’t stop here!

What about using SMO scripting?

If anyone have been following me recently, everytime I’ve created the SMO script, I always have to load the assemblies before I can connect to the SQLServer.


## - When using the 'Microsoft.SqlServer.SqlManagementObjects'package installed from Nuget
## - Help find and save the location of the SMO dll's in a PowerShell variable: ver.14.17224
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netstandard2.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);

The above code is not needed if the SQLServer module had been previously imported.
This way you will code less.

Here’s a small SMO script example for getting SQLServer information using SQL Authentication:


## SMO with Import-Module SQLServer
Import-Module SQLServer

## - Prepare connection strings and connect to SQL Server
$SQLServerInstanceName = 'mercury,1433';
$SQLUserName = 'sa'; $sqlPwd = '$SqlPwd01!';

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

As you can see, there’s no reason why not try and experiment using PowerShell Core with SQL Server.  Next blog post I’ll be creating this script code in the hybrid-script function that can be executed cross-platform. I mean, on any PowerShell version too.

What’s in the future!

Now that PowerShell SQLServer Module is available cross-platform, I will see others Community SQL modules (DBATools, DBAReports) making their way to PowerShell Core. Of course, it will take some before it becomes available!

In the meantime, you can use SMO to build your own PowerShell SQL Scripts. Why not! Go and Expand your horizon!!

Be Bold! Learn PowerShell Core!

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(‘https://chocolatey.org/install.ps1’))
[/Sourcecode]

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
[/Sourcecode]

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
[/Sourcecode]

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 1.0.0.0beta 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. Enable: PasswordAuthentication yes
4. 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
.\FixUserFilePermissions.ps1

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

Get-AzureRmNetworkInterface

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:

(Get-AzureRmNetworkInterface).VirtualMachine.Id

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.

(Get-AzureRmNetworkInterface).VirtualMachine.Id.split('/')

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

(Get-AzureRmNetworkInterface).VirtualMachine.Id.split('/')[7]

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:

(Get-AzureRmNetworkInterface).IpConfigurations

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 } },
MacAddress;

$IpConfig | Format-Table -AutoSize;

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

Conclusion

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!!

Listing SQL Server 2017 Installed Anaconda Packages Using PowerShell

SQL Server 2017 comes with the option to include Python (Anaconda) during SQL Server installation. It will install Anaconda with a small set of python packages for the purpose of creating Data Science solution that sre executed within T-SQL statement(s). Unfortunately, there’s no documentation of what Anaconda packages are installed with SQL Server.

Much Easier with Full Installation

Doing the full Anaconda installation, gives the necessary commands to query what has been installed in your system. This makes it much easier to list all existing installed packages.

In the full installation of Anaconda, done separate from SQL Server, you can use the following command to list all packages installed:

conda info

But, with SQL Server 2017 is a different story.

Where’s my SQL Server Anaconda packages?

These packages are found in the default installation location: “C:\Program Files\Microsoft SQL Server\”YourSQLServerInstanceName”\PYTHON_SERVICES\conda-meta

All packages are of file type *json. Each Anaconda package will named with: the package name, package version, and python version number. But, this makes it hard to view using “File Explorer“.

So, solution to list the SQL Server Anaconda packages in a proper format will be needed.

PowerShell To The Rescue

So, here’s a PowerShell function that will list all installed Anaconda packages in SQL Server 2017. This will required to enter some parameters, such as: SQL Server Installation Location, and SQL Server Instance name.

function Get-SQLServerAnacondaPkgList
{
[CmdletBinding()]
Param (
[string]
$SQLServerInstallationDrive = 'C:',
[string]
$SQLServerInstanceName
)

$SQLServerInstallationLocation = "$($SQLServerInstallationDrive)\Program Files\Microsoft SQL Server\MSSQL14.$($SQLServerInstanceName)\PYTHON_SERVICES\conda-meta"
$SqlAnaconda = Get-ChildItem $SQLServerInstallationLocation -File *.json;

[array]$global:SqlCondaPkgList = $null;
[array]$global:SqlCondaPkgList = foreach ($Pkg in $SqlAnaconda.name)
{
## - Build PSCustomObject:
[PSCustomObject]$PkgList = New-Object PSObject -Property @{
PackageName = $Pkg.Split('-')[0];
PackageVersion = $Pkg.Split('-')[1];
PackageLocation = $SQLServerInstallationLocation;
}; $PkgList;
};
$global:SqlCondaPkgList;
}

## To execute function:
$SQLServerInstallationDrive = 'C:'
$SQLServerInstanceName = "MSQL2K17A"

Get-SQLServerAnacondaPkgList -SQLServerInstallationDrive $SQLServerInstallationDrive `
-SQLServerInstancename $SQLServerInstanceName;

## - Or, after executing the function, go back to use
## - the existing global variable:
$global:SqlCondaPkgList | Select-Object PackageName, PackageVersion

Bottom line

Executing Anaconda within T-SQL seems only available on Windows version. But, you can still create the Python code and do some testing on Linux.

The total number of packages provided with Microsoft SQL Server 2017 is about 146. Now, in the full version of Anaconda, there is a total of about 217 python packages.

Full listing of all Anaconda Packages installed for SQL Server 2017 (See below):

PackageName PackageVersion
----------- --------------
alabaster 0.7.10
babel 2.4.0
blaze 0.10.1
bleach 1.5.0
bokeh 0.12.5
bottleneck 1.2.0
bzip2 1.0.6
cffi 1.9.1
chest 0.2.3
click 6.7
cloudpickle 0.2.2
colorama 0.3.7
conda 4.3.22
conda env
configobj 5.0.6
console_shortcut 0.1.1
cryptography 1.7.1
curl 7.52.1
cycler 0.10.0
cython 0.25.2
cytoolz 0.8.2
dask 0.14.1
datashape 0.5.4
decorator 4.0.11
dill 0.2.5
docutils 0.13.1
entrypoints 0.2.2
et_xmlfile 1.0.1
flask 0.12.1
flask cors
freetype 2.5.5
h5py 2.7.0
hdf5 1.8.15.1
heapdict 1.0.0
html5lib 0.999
icu 57.1
idna 2.2
imagesize 0.7.1
ipykernel 4.6.0
ipython_genutils 0.2.0
ipython 5.3.0
ipywidgets 6.0.0
itsdangerous 0.24
jdcal 1.3
jinja2 2.9.6
jpeg 9b
jsonschema 2.5.1
jupyter_client 5.0.1
jupyter_console 5.1.0
jupyter_core 4.3.0
jupyter_kernel_gateway 2.0.0
jupyter 1.0.0
libpng 1.6.27
libtiff 4.0.6
llvmlite 0.16.0
locket 0.2.0
lxml 3.7.3
markupsafe 0.23
matplotlib 2.0.0
menuinst 1.4.2
mistune 0.7.4
mkl 2017.0.1
mkl service
mpmath 0.19
multipledispatch 0.4.9
nbconvert 5.1.1
nbformat 4.3.0
networkx 1.11
nltk 3.2.2
notebook 5.0.0
numba 0.31.0
numexpr 2.6.2
numpy 1.12.1
numpydoc 0.6.0
odo 0.5.0
olefile 0.44
openpyxl 2.4.1
openssl 1.0.2k
pandas 0.19.2
pandas datareader
pandasql 0.7.3
pandocfilters 1.4.1
partd 0.3.7
path.py 10.1
pathlib2 2.2.1
patsy 0.4.1
pickleshare 0.7.4
pillow 4.1.0
pip 9.0.1
prompt_toolkit 1.0.14
psutil 5.2.1
py 1.4.33
pyasn1 0.2.3
pycosat 0.6.1
pycparser 2.17
pycrypto 2.6.1
pycurl 7.43.0
pygments 2.2.0
pyodbc 4.0.16
pyopenssl 16.2.0
pyparsing 2.1.4
pyqt 5.6.0
pytables 3.2.2
pytest 3.0.7
python 3.5.2
python dateutil
pytz 2017.2
pywavelets 0.5.2
pywin32 220
pyyaml 3.12
pyzmq 16.0.2
qt 5.6.2
qtconsole 4.3.0
requests 2.13.0
requests file
ruamel_yaml 0.11.14
scikit image
scikit learn
scipy 0.19.0
seaborn 0.7.1
setuptools 27.2.0
simplegeneric 0.8.1
sip 4.18
six 1.10.0
snowballstemmer 1.2.1
sphinx 1.5.4
sqlalchemy 1.1.9
sqlparse 0.1.19
statsmodels 0.8.0
sympy 1.0
testpath 0.3
tk 8.5.18
toolz 0.8.2
tornado 4.4.2
traitlets 4.3.2
unicodecsv 0.14.1
vs2015_runtime 14.0.25123
wcwidth 0.1.7
werkzeug 0.12.1
wheel 0.29.0
widgetsnbextension 2.0.0
win_unicode_console 0.5
xlrd 1.0.0
xlsxwriter 0.9.6
xlwt 1.2.0
zlib 1.2.8

So, there’s plenty of room to learn with Python Data Science and SQL Server 2017.

Be Bold! Learn PowerShell Core!

PSCore 6.0.0 – Upgrading/Uninstalling OpenSSH in Windows

This is all done with Chocolatey Package Manager for Windows. The following commands are important to know:

1. Information about the package:
choco info OpenSSH

2. Upgrade installed package:
choco upgrade OpenSSH

3. Remove installed package:
choco uninstall OpenSSH

4. Seach for a package:
choco search OpenSSH

Now, this is very important to know as these packages gets update without any notice and avoiding upgrades could impact the system.

As of the writing of this post, the latest version of Win32 OpenSSH is v0.0.24.0.

## - Chocolatey package information command:
choco info OpenSSH

Keep in mind, all these steps need to be executed with administrator privileges.

Upgrading Win32 OpenSSH

The upgrade process should be enough to get the package to the latest build. Now, I notice my latest upgrade step, I found files that shouldn’t be in the folder.

## - Chocolatey upgrade package command:
choco upgrade OpenSSH

So, take the time to verify and confirm everything is installed as expected. Don’t forget to document and/or save any file, such as: sshd_config.

Uninstalling/Installing Win32 OpenSSH

In the other hand, it doesn’t hurt to uninstall everything, check there are no files left in the folder, and that event the folder was removed. In other words, a clean installation will be perfect.

Before uninstalling, I would recommend a few extra steps, such as:

1. Stop SSH services: sshd and ssh-agent

## - Stopping SSH Services:
Stop-Service sshd
Stop-Service ssh-agent

2. Proceed to uninstall SSH services and change directory:

## - Execute uninstall ssh services script from the OpenSSH folder:
.\uninstall-sshd.ps1
cd c:\

3. Before, doing the uninstall step, make sure there are no process using OpenSSH that may have open file(s).

## - Execute uninstall command:
choco uninstall OpenSSH


Sample of the uninstall error due to an open file:

In this case, just rerun the uninstall step. The goal is to have everything removed including folders.

Now, you’re ready to do a clean installation of Win32 OpenSSH.
Please refer to the following blog post which I have recently added few thing I missed last time: http://www.maxtblog.com/2017/12/powershell-core-updated-setup-openssh-in-windows-and-linux/

For information about Chocolatey The package manager for Windows:
https://chocolatey.org/

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

pwsh

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:

refreshenv

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
.\FixUserFilePermissions.ps1

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

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&gt; 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
.\uninstall-sshd.ps1
.\install-sshd.ps1

This resolved the sshd start failure. (see below)