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

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
}else{
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:
https://github.com/Microsoft/sqltoolsservice/releases

Conclusion

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!

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:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
NetCoreGlobalization.dll

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:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
NetCoreGlobalization.dll

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", `
"Microsoft.SqlServer.ConnectionInfo",
"Microsoft.SqlServer.SqlEnum");
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!