Quick blog on – How to recover/rebuild Master Database in Linux?

Back in March 30th, I posted a suggestion on SQL Server forum, about there was no documentation on how to rebuild SQL Server System database(s) in Linux.

https://feedback.azure.com/forums/908035-sql-server/suggestions/33805198-sql-server-2017-on-linux-system-databases-rebuild?tracking_code=befe44dc3ccdd27370ef0f54c9c8c975

The Issue – SQL Server stopped working

For some reason, I finished doing an “sudo apt update” follow by the “sudo apt upgrade“, and after doing some OS updates “my SQL Server stopped working”. The following message was logged in the log event: (see Image)

Command executed, looking at the last log entry:  “sudo cat /var/opt/mssql/log/system_health_0_131668975728890000.xel

I look everywhere for more information but found no blog post or any documentation about it.

To my surprise! I got a quick response which I appreciate very much.

The Answer

Execute the following command in the user context
/opt/mssql/bin/sqlservr –force-setup

And, will be seen this added to the SQL Server 2017 Linux documentation.

Thanks Microsoft!!

Remember

In Ubuntu I use the following command to check the status of my SQL Server instance:

sudo systemctl status mssql-server

Make sure the status is: active: (running)!

So, Backups are important! Even in Linux.  In my case, I’m running demos and love the fact that reinstalling SQL Server only took a few minutes. But, now I know another way to solve this issue!

And, the Mic is Dropped!!

Executing PowerShell Core from Windows PowerShell

Yes! It is possible to execute PowerShell Core in Windows PowerShell in a very creative way.  Of course, this is just a Proof-Of-Concept and fun to experiment.

In this scenario, the goal is to be able to rebuild the PowerShell Core PSObject in Windows PowerShell. and see the results.

What’s required?

First, if PowerShell Core is already installed, then that’s all we needed. The ingredient are there and we are the chef to create the recipe.

There are four main cmdlets to make receipe works:

1. Start-Process – This cmdlet will run PowerShell Core non-interactive.
2. Export-Clixml – This cmdlet will export to an XML file the PSObject from PowerShell Core session.
3. Import-Clixml – This cmdlet will import from an XML file to recreate the PowerShell Core PSObject in Windows PowerShell.
4. Select-Object – This cmdlet is use to display the output of that object.

Think of the *-Clixml cmdlets as the disconnected way to reuse PSObject in a disconnected way, is like saving the state of the object for testing.

The Recipe

Basically, Start-Process is where the PowerShell Core is run to execute the script. Now, within the PowerShell Core script, it is required to include the Export-Clixml cmdlet. This is where the PowerShell Core Object created in the script will export to an XML file. Finally, importing the same XML file in Windows PowerShell to be worked on and the displaying the PowerShell Core results in Windows PowerShell session.

Now, it’s very important to study and understand the PowerShell PSObject.  Also, do all necessary test to see if there are any limitations in this process.

The Code

Here’s the basic code to accomplish this Proof-Of-Concept:

[sourcecode language=”powershell”]
## ————— Proof of concept ————— ##
## – Build PWSH process for Windows PowerShell Form:

## – Create Scriptblock to execute in PowerShell Core:
$pwshScriptBlock = @’
(Get-Variable PSVersionTable).value.GetEnumerator() `
| Where-Object{ $_.Name -eq ‘GitCommitId’ } `
| Select-Object Name, Value `
| Export-Clixml -Path C:\Temp\PwshCoreObject.xml;
‘@;

## – Set file path to PowerShell Core Executable:
$FilePath = "C:\Program Files\PowerShell\6.0.2\pwsh.exe";

## – Set executable parameters to run with Scriptblock:
$ArgumentList = ‘-noprofile -nologo -NonInteractive -w hidden -command "& {‘ + $pwshScriptBlock + ‘}"’;

## – Execute PowerShell Core process:
$Process = Start-Process -FilePath $FilePath -ArgumentList $ArgumentList -PassThru;

## – Import pwsh CliXml object back to Windows PowerShell PSObject:
$pwshPSObject = Import-Clixml -Path "C:\Temp\PwshCoreObject.xml";

## – Display results from PowerShell Core CliXml file:
$pwshPSObject | Select-Object -Property $_;

[/sourcecode]

The code is executed from a Windows PowerShell session. Try it, experiment, and test. See if it’s fit your need in any way.

Conclusion

Please, I’m not saying that this the way to go. But, it just showing one way to handle sharing results between Windows PowerShell and PowerShell Core. Again this is just for fun, and at the same time learning more about PowerShell.

The possibilities are endless! (Teaser)

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:

[sourcecode language=”text”]
Subsystem powershell C:\Program Files\PowerShell\6.0.1\pwsh.exe -sshs -NoLogo -NoProfile

[/sourcecode]

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

[sourcecode language=”powershell”]
netsh advfirewall firewall add rule name=SSHPort22 dir=in action=allow protocol=TCP localport=22

[/sourcecode]

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

[sourcecode language=”powershell”]
## – Install both ssh services: sshd and ssh-agent:
.\install-sshd.ps1 /SSHAgentFeature

[/sourcecode]

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:

[sourcecode language=”powershell”]
## – Generate SSH keys:
./ssh-keygen -A

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

[/sourcecode]

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.

[sourcecode language=”powershell”]
## – 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

[/sourcecode]

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.

[sourcecode language=”powershell”]
## – Connecting to Windows:
Enter-PSSession -HostName sapien01 -UserName max_t

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

[/sourcecode]

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:

[sourcecode language=”powershell”]
Get-AzureRmNetworkInterface

[/sourcecode]

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:

[sourcecode language=”powershell”]
(Get-AzureRmNetworkInterface).VirtualMachine.Id

[/sourcecode]

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.

[sourcecode language=”powershell”]
(Get-AzureRmNetworkInterface).VirtualMachine.Id.split(‘/’)

[/sourcecode]

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

[sourcecode language=”powershell”]
(Get-AzureRmNetworkInterface).VirtualMachine.Id.split(‘/’)[7]

[/sourcecode]

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:

[sourcecode language=”powershell”]
(Get-AzureRmNetworkInterface).IpConfigurations

[/sourcecode]

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:

[sourcecode language=”powershell”]
## – 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;

[/sourcecode]

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:

[sourcecode language=”powershell”]
conda info
[/sourcecode]

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.

[sourcecode language=”powershell”]
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

[/sourcecode]

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

[sourcecode language=”text”]
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
[/sourcecode]

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

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: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

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: https://www.powershellgallery.com/packages/SqlServer/21.0.17199

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:
https://www.sapien.com/blog/2017/12/07/powershell-studio-feature-nugget-refreshing-local-cache-powershell-cmdlets-and-modules/

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:
http://www.maxtblog.com/2017/11/streamlining-sql-server-management-objects-smo-in-powershell-core/

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: https://blogs.msdn.microsoft.com/powershell/2018/01/24/powershell-core-6-1-roadmap/

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: https://github.com/PowerShell/PowerShell/releases

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:
https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects

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:

[sourcecode language=”powershell”]
Install-Package Microsoft.SqlServer.SqlManagementObjects -RequiredVersion ‘140.17199.0’

[/sourcecode]

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

[sourcecode language=”powershell”]
## – 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

[/sourcecode]

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.

[sourcecode language=”powershell”]
# 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."
}

[/sourcecode]

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

[sourcecode language=”powershell”]
uninstall-package Microsoft.SqlServer.SqlManagementObjects

[/sourcecode]

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

Be Bold!! Learn PowerShell Core!!

Installing PowerShell Core in Server Core

In the following Virtual machine scenario, I got one Server Core with Active Directory (Build 16299) and Windows 10 (Build 16299) joined to my new domain. Both Build 16299.
On my Windows client I create a shared folder named “SharedFiles”, where I copy over the latest MSI version of PowerShell Core “PowerShell-6.0.0-win-x64.msi”.

Then, on the Server Core I’m going to create a map drive to my Windows client shared folder to then run the MSI installation from Windows PowerShell Console.

Install Steps

Here are the steps on Server Core Command Prompt:

1. Open Windows PowerShell:

[sourcecode language=”powershell”]
## – Start PowerShell:
PowerShell

[/sourcecode]

2. Map network drive to Windows Client shared folder, then change drive:

[sourcecode language=”powershell”]
## – Map network drive:
New-PSDrive -Name “m” -PSProvider “FileSystem” -Root “\\systemname\sharedfolder”;

## – Change to drive to “m:” and verify the MSI file is there:
m:
dir

[/sourcecode]

3. At the “m:” drive, execute the installation with the following command:

[sourcecode language=”powershell”]
## – Start installing PowerShell Core:
msiexec /i .\PowerShell-6.0.0-win-x64.msi /q

[/sourcecode]

4. As this is a silent installation, so give it a few minutes, then exit to the command prompt
[sourcecode language=”powershell”]
## – type “exit” to get back to the command prompt
exit

## – Change directory to PowerShell Core, and execute pwsh.exe:
cd “C:\Program Files\PowerShell\6.0.0”
pwsh

[/sourcecode]

Setting ExecutionPolicy and Update Help

It has become a routine that after first time openning Powershell, that we need to: 1) set the ExecutionPolicy, and update the help documentation:

So, setting the execution policy, pick the one you need. The most common ones are: “RemoteSigned”, “ByPass”, “AllSigned”, “Unrestricted”.

[sourcecode language=”powershell”]
## – Example setting ExecutionPolicy as “RemoteSigned”
Set-ExecutionPolicy -ExecutionPolicy “RemoteSigned”;

[/sourcecode]

Then, its very important to update the help documentation. I had the tendency to always include to parameter “-Force“:

[sourcecode language=”powershell”]
## – Updating Help documentation:
Update-Help -Force

[/sourcecode]

And, you’re ready to work with PowerShell Core on Server Core.

Oh! How to uninstall PowerShell Core

Let go a little further. Here are the steps to successfully uninstall PowerShell Core on Server Core.
Open Windows PowerShell, make a list of all installed products using Get-WMIObject command:

[sourcecode language=”powershell”]
## – Listing installed products
get-wmiobject Win32_Product `
Select IdentifyingNumber, Name, LocalPackage `
| Format-Table -AutoSize;

[/sourcecode]

This will confirmed PowerShell Core is installed. Pay close attention to the “IdentifyingNumber” property which is really the product GUID.

Now, we can proceed to uninstall PowerShell Core. There are two way to execute a product MSI uninstall:

1. Providing the installation path with filename:

[sourcecode language=”powershell”]
## – Executing Uninstall MSI silently and write to log file: (Using filename)
msiexec.exe /x “c:\filename.msi” /qn /lv “C:\msilogfile.log”

[/sourcecode]

2. Using the IdentifyingNumber (Product GUID) listed when executing the Get-WMIObject:

[sourcecode language=”powershell”]
## – Executing Uninstall MSI silently and write to log file: (Using Product GUID)
msiexec.exe /x ‘{11111111-1111-1111-1111-11111111111X}’dir /qn /lv “C:\msilogfile.log”

[/sourcecode]

Image below doing MSI uninstall using GUID:

Both examples, include the use to write to a log file “C:\msilogfile.log“, along with the necessary switches to uninstall and execute silently. (Above switches are lowercase)

Additional Information

Check the following links on:

1. Installing MSI files with PowerShell: https://kevinmarquette.github.io/2016-10-21-powershell-installing-msi-files/

2. Report or check any issues with PowerShell Core: https://github.com/PowerShell/PowerShell/issues

3. PowerShell Core Reach GA (Generally Available) status: https://blogs.msdn.microsoft.com/powershell/2018/01/10/powershell-core-6-0-generally-available-ga-and-supported/

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.

[sourcecode language=”powershell”]
## – Chocolatey package information command:
choco info OpenSSH

[/sourcecode]

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.

[sourcecode language=”powershell”]
## – Chocolatey upgrade package command:
choco upgrade OpenSSH

[/sourcecode]

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

[sourcecode language=”powershell”]
## – Stopping SSH Services:
Stop-Service sshd
Stop-Service ssh-agent

[/sourcecode]

2. Proceed to uninstall SSH services and change directory:

[sourcecode language=”powershell”]
## – Execute uninstall ssh services script from the OpenSSH folder:
.\uninstall-sshd.ps1
cd c:\
[/sourcecode]

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

[sourcecode language=”powershell”]
## – Execute uninstall command:
choco uninstall OpenSSH

[/sourcecode]


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