PowerShell Core – Updating your SQL Server Linux Docker Containers Images

In this post I’ll be covering how to install some needed components, how to commit the changes, and create a revised images for deployment.

In recent event and meetings, I’ve been talking about how to work SQL Server Linux Containers Docker images. As these images get your container up-and-running quickly they lacks some tools that may be useful to complete the SQL Server configuration.

What’s missing?

The SQL Server images contains a small footprint of Linux Ubuntu 16.04 Operating System (OS) and is meant for quick deployment. The OS side the container need to be kept updated regularly.

At the same time, when you starts exploring inside the container, there still missing components you may want to use:

  • vim – for editing text files.
  • ifconfig – to check your network interfaces.
  • ping – to check IP-Address can be reachable across the network.
  • curl – for transfering data.

So, after you pull the docker image, create the container using “docker run …“, and then get to the container Bash session by using “docker exec -it …“. Remember the bash session only get you to the “root” level as there’s no users set on these containers.

## - First time setup: (for "server:2019-CTP2.2-ubuntu" and )
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=$SqlPwd01A' -e "MSSQL_PID=Developer" -p 1433:1433 --name sql2k19_CTP2.3 -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu;

## - Display all active containers;
docker ps -a

At this point make sure the active container status should be in “Up” status. Now can proceed to update the container.

Installing Missing Components

To have access to the container we use the “docker exec …” command.  This command will allow to get access to the container “root” prompt.

## - Configuring your container:
docker exec -it sql2k19_CTP2.3 bash

The first thing I would suggest to do, execute the following to commands:

## - Updating OS:
apt update

apt upgrade

Notice if you try to execute: vim, ping, ifconfig, and curl are not installed in the container images.

Let’s proceed to install these component by executing the following command:

## - Installing additional components:
apt-get -y install \
curl \
vim \
iputils-ping \
net-tools \
powershell-preview

Also, it’s a good idea to create a Downloads folder in case to install other application(s).

## - Create Downloads folder in root:
mkdir Downloads
chmod 755 Downloads

Notice that PowerShell Core Preview was included with the other missing components.  PowerShell has become a great tool to have in a Linux environment.

PowerShell Core SQLServer Module

Although, this is optional but it doesn’t prevent you to include PowerShell Core Preview 6.2.0-RC1 with the SqlServer module which included the “Invoke-Sqlcmd” use by many administrator.  This is a great module to have in a SQL Server container image.

So, from the “root” prompt in the container open PowerShell Core Preview, then proceed to install the SqlServer module preview version 21.1.18095.

## - Open PowerShell Core:
pwsh-preview

## - Install SqlServer module preview:
Install-Module SQLServer -AllowPreRelease

This completes the essential for using PowerShell to help managing a SQL Server instance(s).

How About Anaconda?

We could install the latest version of Anaconda with Python 3.7 in our SQL Server container image.

## - Change directory to Downloads folder:
cd Downloads

## - Download Anaconda with Python 3.7:
wget https://repo.anaconda.com/archive/Anaconda3-2018.12-Linux-x86_64.sh

## - Install Anaconda with Python 3.7:
bash Anaconda3-2018.12-Linux-x86_64.sh

This will give us the ability to test Python scripts within the container.

Testing installed Components

We need to verify that all previously installed components are working. Go back to the container “root” prompt, and to execute the commands:

ifconfig
ping 127.0.0.1
vim ~/.bashrc
pwsh
sqlcmd

Now, executing “sqlcmd” command line will not work unless you add the path to the executable to the “root” ~/.bashrc file:

## - Need to include the path to SQLCMD command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

## - Refresh ~/.bashrc:
source ~/.bashrc

## - Run Sqlcmd command:
sqlcmd -L localhost -U sa -P 'sapwd'
> select @@version
> go
> exit

This is a good indication that our *SQL Server container is active. And, now we got all missing components installed.

Now, we need to make sure we don’t lose out changes.

Creating your own SQL Server Docker image

This is an important step so you won’t lose the changes already made to the container.  Below are the brief step to follow:

## - Commit the container changes: (repository name must be lowercase but Tags are OK with uppercase)
## -> docker commit "<Get-Container_ID>" "<Image-name>":"<TAG name>"

docker commit "<Get-Container_ID>" sql2k19_ctp2.3_sandbox:CTP2.3-Version01

## - List images included the committed ones:
docker images

## - Stop Image before the Save step:
docker stop sql2k19_CTP2.3
docker ps -a

## - Save docker updated image:
docker save -o ./Downloads/sql2k19ctp23_sandboxVer01.tar sql2k19_ctp2.3_sandbox

The “docker commit …” command, you’ll provide both the image-name (all lowercase) and a TAG name (uppercase allowed). You can be creative in having an naming conversion for you images repositories.

It’s very important to save images after doing the commit. I found out that having an active container would be useless without an image.  As far as I know, I haven’t found a way to rebuild an image from an existing container if the image was previously removed.

Summary

Hope this brief run down on working with SQL Server Docker container images will get you started with modifying existing images for quick deployment.

One thing to keep in mind!

  • The SQL Server Container memory need to be 4GB minimum.
  • In Windows, if your’re using non-Hyper-V virtualization tools such as Virtualbox, the virtual machine memory need to be change to 4GB.
  • Also, when you are creating images, the virtual machine disk size default is 20GB. This may need to be increase unless you keep cleaning/removing images to make room.

Just layout what you need, commit, save and deploy your docker solution in your environment.

Keep learning about this amazing technology!

 

PowerShell – Docker Setup for Windows 10 WSL Ubuntu 18.04 with VMware Workstation

The purpose of this blog post is to show how to setup Docker Community Edition in a Windows 10 with VMware Workstation to be use in Windows Subsystem for Windows (WSL).

There are a few blog post that helped me figure out what’s needed to get this to work and I’ll be sharing these links at the end of this post.

My current environment

My current environment consist of the following components:

  • Windows 10 Build 17763
  • VMware Workstation Pro 12
  • *Oracle Virtualbox 5.2
  • WSL – Ubuntu 18.04
  • SQL Server 2017 Developer Edition
  • Windows PowerShell (v5.1.17763.316)
  • PowerShell Core GA v6.3.1 (both Windows and Linux)
  • PowerShell Core Preview v6.2.0-preview.4 (both Windows and Linux)

*Note: This is not the latest version  of Virtualbox but it’s still supported.

Remember, the purpose of this environment is to build a “developer sandbox” that can allow me to learn and work with Docker containers.

What’s needed!

Because I’m using VMware Workstation instead of Hyper-V, there are a few things need to be in place to make this work. Windows 10 need to have the following:

  • All Hyper-V services need to be disable by using “System Configuration” tool.

  •  Install VMWare Workstation Pro. (https://www.vmware.com/products/workstation-pro.html)
  •  Install Oracle Virtualbox version 5.2. (https://www.virtualbox.org/wiki/Download_Old_Builds_5_2)

  •  Install from the Microsoft Store, WSL – Ubuntu 18.04.

  • And, make sure to run “sudo apt update” and “sudo apt upgrade” because images are not updated with latest components.

Installing PowerShell Components

Next, the following Docker components packages from Chocolatey need to be install using Windows PowerShell with administrator privileges:

* Install docker

choco install -y docker

* Install docker-machine-vmwareworkstation

choco install -y docker-machine-vmwareworkstation

Getting WSL Ready for Docker

Now, open the “WSL – Ubuntu 18.04” Linux console and execute the following *commands:

sudo apt update

sudo apt upgrade

*Note: You’ll need to run these two commands manually to keep your Linux distribution up-to-date.

At this point, follow the Docker installation instructions for “Docker-CE for Ubuntu 18.04“. But, in a nutshell, here’s the shortcut:

sudo apt-get install \
apt-transport-https \
ca-certificates \
curl \
gnupg-agent \
software-properties-common

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

sudo add-apt-repository \
"deb [arch=amd64] https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) \
stable"

sudo apt-get update

sudo apt install docker-ce

sudo usermod -aG docker maxt

exit

At this point. make sure to reopen the WSL linux console.

Setup Docker-Machine in Windows

Back in Windows PowerShell, the next steps show the way to have Docker work in “WSL – Ubuntu 18.04“. Starting with Windows PowerShell console, execute the following commands:

docker-machine --native-ssh create -d vmwareworkstation default
docker-machine create docker-host

These commands should complete without any errors. At the same time, two virtual machines: “default” and “docker-host” will be created and running in *Virtualbox.

*Note: These two *NEED* to be running in order for docker to work with WSL. At the same time, both VMware Workstation and Virtualbox need to be installed or this will not work

To check that for the Docker-Machine environment(s) are working, use the following command:

docker-machine ls

Next, execute the following command to write down “docker-host” environment results to be copied into the Linux user ~/.bashrc file.

docker-machine env docker-host
PS C:\WINDOWS\system32> docker-machine.exe env default
$Env:DOCKER_TLS_VERIFY = "1"
$Env:DOCKER_HOST = "tcp://192.168.220.xxx:2376"
$Env:DOCKER_CERT_PATH = "C:\Users\max_t\.docker\machine\machines\default"
$Env:DOCKER_MACHINE_NAME = "default"
$Env:COMPOSE_CONVERT_WINDOWS_PATHS = "true"
# Run this command to configure your shell:
# & "C:\ProgramData\chocolatey\lib\docker-machine\bin\docker-machine.exe" env default | Invoke-Expression

Open a “WSL – Ubuntu 18.04 console to edit the user “~/.bashrc” file, to add the following Docker variables:

## Added manually for Docker machine docker-host:
export DOCKER_HOST=192.168.99.xxx:2376
export DOCKER_TLS_VERIFY=1
export DOCKER_CERT_PATH=/mnt/c/users/max_t/.docker/machine/machines/docker-host
export DOCKER_MACHINE_NAME=docker-host
export COMPOSE_CONVERT_WINDOWS_PATHS=true

sudo vim ~/.bashrc

Reopen the “WSL – Ubuntu 18.04 console.

Testing Docker in WSL

Now, I can test Docker in my “WSL – Ubuntu 18.04 console session. Open PowerShell Core console, and execute the following command to run the Docker Hello-World demo:

docker run Hello-World

This command download (or pull) the Docker image, then run the Hello-World container. If everything work as expected, then it will display the following text.

To check both Docker image(s) and/or container(s) in WSL , use the following commands: (Picture

# - Check for all pulled images in system:
docker images

# - Check the status of active containers:
docker ps -a

As you can see there no issues executing Docker command lines in Linux PowerShell Core.

To see the full list of docker command line help available click on the following link.

After all this is done! Docker working in my WSL environment.

Limitations

YES! There are limitations. This is a workaround on the issue of using Docker without Hyper-V. And, this will allow you to:

  • Pull images
  • Update containers
  • Save images

In my environment, I found limitations working with Docker Network using WSL which can impact Windows Docker-Machine VM “docker-host” interface. This issue can force you to rebuild both VM interfaces: “default” and “docker-host“.

Make sure to learn how to commit, save, and reload Docker images.  Don’t lose your changes!

So, if you have either VMware Workstation and/or Oracle Virtualbox, consider investing the time creating a Linux virtual machine and then install Docker CE.

Summary

We have accomplished setting up Docker containers in *Windows 10 “WSL – Ubuntu 18.04” using both Windows PowerShell and PowerShell Core in Linux. So, using Oracle Virtualbox v5.2 with VMware Workstation is a required component to make this work.

*Note: These post is meant for people to make Docker work in WSL Linux.

Also, if you’re familiar with PowerShell, Docker commands can execute without any issues. Now, I can use my favorite editor SAPIEN’s PowerShell Studio to build my automation scripts with docker commands.

What’s Next?

Try downloading other Docker images, like SQL Server 2017 and SQL Server 2019. This is the quickest way for providing a built solution using containers.

Learn about Docker Compose, and Kubernetes as these can be use in the Cloud environment as well.

Go and Explores the possibilities of provisioning solutions to your organization!

Resource links

Powerhell Core Ubuntu 18.04 – PSRemoting to an Active Directory Machine

Sometime there’s the need to do PowerShell remoting from Linux to a Windows System. In my lab environment, I was able to install, configure, and established a PowerShell Remote connection from a Linux Ubuntu 18.04 system to *Active Directory joined Windows System.

*Note: Before trying to following steps, if you’re in a corporate domain, consult with your security team. I would recommend that you try this scenario in virtual machine environment.

I’ve been struggling trying to OpenSSH in both Windows 10 (Build 1803) and Windows Server 2019 with no success connecting from Linux. So, I decided to try install Kerberos component on my Ubuntu system and it works!  And, with no need to joined my Linux system to my virtual Active Directory domain.

Install and configuring Kerberos Client

  • I need to install and configure the Kerberos Client application on my system:
$ sudo apt-get install krb5-user
  • Customizing *krb5.conf file settings for my domain:
$ sudo vim /etc/krb5.conf
  • The following are my custom settings in the krb5.conf file for “DOMAINNAME” Kerberos:
[libdefaults]
default_realm = DOMAINNAME.COM

# The following are custom settings for "DOMAINNAME" Kerberos:
dns_lookup_realm = true
dns_lookup_kdc = true
default_tgs_enctypes = arcfour-hmac-md5 des-cbc-crc des-cbc-md5
default_tkt_enctypes = arcfour-hmac-md5 des-cbc-crc des-cbc-md5
permitted_enctypes = arcfour-hmac-md5 des-cbc-crc des-cbc-md5

[realms]
TRINITY.COM = {
kdc = DOMAINMACHINENAME
admin_server = DOMAINMACHINENAME
}

[domain_realm]
.com = DOMAINNAME

*Note: Make a copy of the krb5.conf file before any changes.

One thing to point out! Both DOMAINNAME and DOMAINMACHINENAME, must be in uppercase.

Configuring ssh

Next step involves in configuring the ssh for Kerberos negotiation. This is the ssh_config file (not sshd_config).

$ sudo vim /etc/ssh/ssh_config

Make sure the following parameters are set at the end of the *ssh_config file:

SendEnv LANG LC_*
HashKnownHosts yes
GSSAPIAuthentication yes
GSSAPIDelegateCredentials no
GSSAPIKeyExchange yes

*Note: If there are missing ones, don’t touch the commented ones. Just copy/paste and set the values.

After completing the changes, I would recommend a reboot.

Testing and working Kerberos Client

Here are a few linux commands to work with Kerberos client.  If the krb5.conf setting are set correctly, then the following commands should work without any issues.

1. This command will verify user domain, asking for password.

$ kinit username@domainname

2. Shows the list of Kerberos Cached tickets and credential.

$ klist

3. To delete\clear all Kerberos Cache entries:

$ kdestroy

What about setting in Windows Systems?

I’m will cover the whole PowerShell remoting setup. But, I will highlight what’s needed to make Linux connect to a Active Directory Domain system.

  • Enable PSRemoting

In PowerShell Conscole, run the “Enable-PSRemoting -force” command line on both client and server. This command will add the firewall rule to allow PowerShell remoting to work.

  • Check WinRM Service

Check the Windows Remote Management service is running. By default, in Windows 10 client, this is set to “Manual”.
On the server, just verify that the service running.

Before, connecting Linux to a windows domain system, make sure to test PowerShell remoting between Windows machines. This will guarantee that you got everything working correctly.

Name Resolution Tip

I don’t join my Linux system to my AD domain. So, to resolve my name resolution issues, I manually update the hosts file on my systems. This will include the domain ip-address as well as all other systems

hosts file
:
xxx.xxx.xxx.xxx domainname.com
:

Testing connectivity

Ubuntu 18.04 Connecting to a domain system final test.

1. In Linux, open PowerShell:

$ pwsh

2. Prepare the domain user:

PS /home/user> kinit domainuser

3. Create a *PowerShell Remote interactive session:

PS /home/user> Enter-PSSession -ComputerName wincomputer -Authentication Negotiate -Credential user@domainname.com

*Note: This remote connection will open Windows PowerShell and not PowerShell Core.

Summary

So, in Ubuntu 18.04 installing and configuring Kerberos user client only, you can connect your Linus system to a Active Directory Domain systems. But remember, this will connect to a Windows PowerShell session only.

I’m hoping that in the near future we can have the ability to select a PowerShell versions. Wait!!  There’s a way to open a PowerShell Core session instead of Windows PowerShell!!

How To Connect to PowerShell Core

So, by default you’re going to connect to Windows PowerShell. But, if you use the following parameter ‘-ConfigurationName’ folllowed by either ‘PowerShell.6‘ or ‘PowerShell.6-Preview‘ then you’ll get PowerShell Core session.  Also, you can use an specific version ‘PowerShell.6.1.0‘.

Enter-PSSession -ComputerName venus -Authentication Negotiate -Credential max_t@trinity.com -ConfigurationName PowerShell.6

Thanks to Steve Lee (Microsoft PowerShell Team) for letting me know this is already available.

References

The following links help figured out the needed components to make my lab environment work.

PowerShell Core Ubuntu 18.04 Using Docker Containers

Containers have been popular for some time now in the industry. It’s becoming as important as learning PowerShell.  Have you try it?

Installing Docker

While learning about Docker Container, I notice that is much easier to installed on a Linux system. In Windows, Hyper-V is a requirement to install Docker, and specially if you want to use the “Windows Subsystem in Linux” WSL feature, there’s more setup to complete. So, I’m not using Hyper-V, I’m using VMware Workstation.  To keeping simple, I created an Ubuntu 18.04 VM using VMWare Workstation.

You can find the Docker CE installation instructions in the following link.

If you’re using Ubuntu 18.04. make sure to install Curl, as it isn’t included in the OS.

$ sudo apt install curl

After the Docker CE installation completes, make sure to run the “hello-world” to test the container runs.

If  you have Hyper-V and you’re interested in pursuing making Docker CE works in Windows 10 WSL (Windows Subsystem for Linux), check the following link on “running-docker-on-bash-on-windows“.

Use PowerShell Core

Make sure you have the latest PowerShell Core installed. If it was previously installed, then use the “$ sudo apt upgrade” to get the latest version. Of course, this all depends on which installation method you previously chose to install PowerShell

Or, just go to the Github PowerShell page to get the latest version(s) for either GA (Generally Available) or the Preview.

Now, before starting to working with Docker as a non-root user, you will need to add the current user to the “Docker” group, by using the following command line:

$ sudo usermod -aG docker your-userid

Then, open a PowerShell Core session to start working with Docker containers.

The following docker commands are the essentials to work with containers and can be executed from within the PowerShell Core session:

1. Listing active container

PS /> docker ps -a

2. Listing images in your system

PS /> docker images

3. Get an container image

PS /> docker pull image-name (not GUID)

4. Image(s) cleanup

PS /> docker rmi image-GUID

If you want to find a particular image, then go to Docker Hub site. An account is required in order to login to the Docker Hub.  In there you’ll find certified images that can download using the “docker pull <imagename>” command line.

Docker PowerShell Containers

Interesting enough, while doing a search in Docker Hub, I found ta couple of PowerShell Core containers available that caught my attention.

1. “PowerShell” (Verified Publisher).

2. “azuresdk/azure-powershell-core”.

Also, “PowerShell-Preview” is available under the PowerShell Core container page

To try them out, just execute the following command:

1. PowerShell – Read documentation. Then execute the following command line to pull the container to your system.

PS /> docker pull mcr.microsoft.com/powershell

2. Azure-PowerShell-Core – As of now, current documentation is out-dated. (

PS /> docker pull azuresdk/azure-powershell-core

3. PowerShell-Preview

PS /> docker pull mcr.microsoft.com/powershell:preview

After completing pulling the docker images, then we are ready to check the containers by executing the following command:

1. Execute PowerShell Core GA

docker run -it microsoft/powershell

2. Execute *Azure-PowerShell

docker run -it azuresdk/azure-powershell-core

3. Execute PowerShell Core Preview (latest)

docker run -it microsoft/powershell:preview

Note: It seems the Azure-PowerShell container is not currently up-to-date for both PowerShell Core GA and the Azure modules version. Just check for upcoming updates on these repositories later.

Summary

As you can see, working with Docker containers and PowerShell makes it a convenient test environment. Either way, if you’re want to use Linux terminal session with or without PowerShell, there’s no reason why not to try it.  So, after you have created the linux VM and complete the installation of Docker CE, you’ll get it working in minutes.

 

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:

[sourcecode language=”powershell”]

## – 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"
}
}
};

[/sourcecode]

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

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

[/sourcecode]

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.

[sourcecode language=”powershell”]

Install-Module -Name SQLServer -Force -Scope AllUsers

[/sourcecode]

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.

[sourcecode language=”powershell”]

Import-Module SQLServer

Get-PSDrive

[/sourcecode]

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

[sourcecode language=”powershell”]

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

[/sourcecode]

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.

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

[/sourcecode]

 

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

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

[sourcecode language=”powershell”]

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

[/sourcecode]

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.

[sourcecode language=”powershell”]

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

[/sourcecode]

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:

[sourcecode language=”powershell”]

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

[/sourcecode]

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:

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