Handling SQL Server Connection strings with PowerShell Secret Management

Finally, I came up with a practical example using the Powershell Secret Management module for storing SQL credentials. This is an excellent way of keeping your SQL connection strings information out of your scripting code. This way we just have it stored in our Vault.

Where do I start?

To install the Powershell Secret Management module, execute the following series of one-liners in a PowerShell prompt to install the latest version from the PowerShell Gallery:

Note: This module has finally reached GA (Generally Available) status.

## - install from the PowerShell Gallery both: SecretManagement, and SecretStore modules:
Install-Module Microsoft.PowerShell.SecretManagement, Microsoft.PowerShell.SecretStore

## - Register the vault with a given name:
Register-SecretVault -Name SecretStore -ModuleName Microsoft.PowerShell.SecretStore -DefaultVault

Now, we got the default “SecretStore” vault created. The vault password will ask once you start adding secrets.

The following cmdlets make it easy to manage your vault:

## - Module: Microsoft.PowerShell.SecretManagement
Get-Secret
Get-SecretInfo
Get-SecretVault
Register-SecretVault
Remove-Secret
Set-Secret
Test-SecretVault
Unregister-SecretVault

## - Microsoft.PowerShell.SecretStore
Get-SecretStoreConfiguration
Reset-SecretStore
Set-SecretStoreConfiguration
Set-SecretStorePassword
Unlock-SecretStore

Note: By-Design. There can only be one vault available.

Take your time to learn these commands.

Let the fun begin

Let’s cut down to the chase and see how this works. I’m going to proceed to create my secret SQL Server connection string values.

Keep in mind, secrets management supports five types of objects: byte[], String, SecureString, PSCredential, and Hashtable. By DEFAULT, the secret will be stored as a ‘SecureString‘ object.

Be creative! Why not store my connection string(s) as a hash table object containing my credential information in the following way:

## - Create hashtable object containing the SQL Connection String:
[hashtable]$MysqlCred01 = @{SqlName = "localhost,1445";Sqlusr = "sa"; SqlPwd = '$MyPwd01!';};

## - This is to veryfy the hashtable object was Properly created:
$MysqlCred01.GetType();
$MysqlCred01

Next after creating the hashtable object, is to save it in the vault with the following command “Set-Secret“:

## - Storing the secret in the vault:
Set-Secret -name MysqlCred01 -secret $MysqlCred01

Note: the first time you store a secret value to the vault, you’ll be prompted for a password.

As you save more secrets, use the following command “Get-SecretInfo” to list what you have in the vault:

## Displaying all stored secrets:
Get-SecretInfo

Now, to get your secret from the vault and use it in PowerShell:

## - Pulling the secret out of the vault into PowerShell variable as plain text:
$MysqlhashCred01 = Get-secret -name MysqlCred01 -asplaintext

## - Accessing hash table values:
$MysqlhashCred01.SqlName
$MysqlhashCred01.Sqlusr
$MysqlhashCred01.SqlPwd

You will notice that eventually, your access will time-out locking you out of the vault. Here’s you use the following command “Unlock-SecretStore” to temporarily unlock the vault:

## - Unlocking the vault to access your secrets providing the vault password:
Unlock-SecretStore -Password '$yourpwd!'

Now, the “Unlock-SecretStore” command is useful for script automation. when you want the script to quickly access the vault. You’ll need to do the following:

## - Unlocking the vault for automation:
Unlock-SecretStore -Password $(ConvertTo-SecureString -String '$yourpwd!' -AsPlainText);
Get-SecretInfo

This way SecretStore vault will not prompt for a password.

Implementing Secret in a GUI Application

Here’s an example of implementing secret in one of my SAPIEN PowerShell Studio GUI applications that check for SQL Server Database Index Fragmentation.

This is a multi-form Window application that where you can select a connection string stored in your SecretStore vault. then you can select the Database and click on the “Start-Job” button to list the status of Database index fragmentation. In this sample application, I can connect to both my local SQL Server and 2 of my Azure SQL Databases.

If you work with PowerShell, both SAPIEN’s Primalscript and PowerShell Studio is a good tool to have for any Administrators and DevOps. Try them out!

For more information

1. Secret Management Blog post.

2. Secret Management in Github. (Post any bugs and/or feedback here)

3. SecretStore in Github. (Post any bugs and/or feedback here)

Have a GREAT SQL PowerShell Day! This is the way!

PowerShell Working with SQL Logins Name

This blog post is following “Changing a SQL Server Login name with T-SQL“. Let’s take advantage of the .NET SMO framework assembly object model Namespaces with PowerShell to change a Windows account in a SQL Server Logins.

Remember to download the latest version of PowerShell.

There’s nothing wrong in using SSMS (SQL Server Management Studio) as our GUI application to manage our SQL Server engine. But soon, you will have the need to use PowerShell scripting for automating some daily tasks. And, Trust me! It will save you time.

Both Microsoft and the SQL Server Community provide you with some of the Awesome tools, such as the following PowerShell modules: SQLPS, SqlServer, Secretmanagement, and DBATools.

Let’s begin with creating  a list all SQL users on our SQL Server using the DBATools module  “Get-DBAuser” command:

Get-DBADBUser -SqlInstance 'localhost,1433'

As you can see, this command returns a lot of information you can export and dissect in many way.

Now, let’s take this a little further using SMO Object Model Namespaces.

Don’t be scare! in order to start using these SMO Classes. To start, all you need to have installed any of the following PowerShell Modules: SQLPS, SQLServer or DBATools, then execute the “import-Module” command:

## This will load SMO assemblies:
Import-Module SqlServer

Then all necessary SMO Assemblies are loaded and ready to be consumed during your PowerShell session. You can start building your own PowerShell one-liners or scripts/functions command to interact with the SQL Server engine.

Let’s cut to chase, and create a simple PowerShell function “Get-SqlLogins‘ to simply list all my SQL logins in my SQL Server:

## - function_Get-SqlLogins.ps1:

function Get-Sqllogins
{
param
(
[parameter(Mandatory = $true)]
[string]$sqlname,
[string]$uname,
[string]$upwd
)

## - Prepare connection to SQL Server:
$SQLSrvConn = `
new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($sqlname, $uname, $upwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - Get SQL SERVER list of database names:
$global:itm = 0
$SQLSrvObj.logins | Select-Object @{ l = 'itm'; e = { $global:itm; ++$global:itm }; }, name, logintype;

}; 
$sqlname = 'localhost,1433';
$uname = 'sa';
$upwd = '$SqlPwd01!';

Get-Sqllogins -sqlname $sqlname -uname $uname -upwd $upwd

## - End-of-File

Note: Save this code as function_Get-Sqllogins.ps1. 

You can edit this file to run one liner at the time and explore the $SQLSrObj PowerShell object.

Use the following GET-Member(alias gm)command to explore the object content:

## - exploring .NET Objects:
$SQLSrvObj | gm | Out-GridView

This is a good way to learn about your PowerShell objects.  You’ll be surprised by the ton of information you can find for documentation.

Now, try listing all SQL logins names by typing the following:

## - shorthand  to list all values in a object proprety:
$SQLSrvObj.logins.name

So, with a few lines of code, you can quickly get results.

Now, proceeding with looking for the Windows account I want to change the name in the SQL Login.

For this, I need to add line numbers to the PSObject result. This way I can Isolate the Login ID:

$global:cnt = 0
$SQLSrvObj.logins | Select-Object @{ l = ‘cnt’; e = { $global:cnt; ++$global:cnt }; }, name, logintype

For the finale: Changing the SQL Login Name. I’m going to manually do this using SMO PowerShell One-liner:
I found that element #5 is the SQL login I need to change:

## - verify before making the changeto the SQL Login object;
$SQLSrvObj.logins[5]

So far we’ve been working with SMO .NET Objects properties. Here’s where we use SMO .NET methods which affect the object (element#5) I have manually selected using “$SQLSrvObj.logins[5]“:

Last steps for updating the SQL Login name:

Note: Keeping in mind, the actual change starts at the Windows Account level. 

1. The *.Alter() method sets the object ready to be changed:

$SQLSrvObj.logins[5].alter()

2. followup by the *.rename(**string**) method which will affect the name object.

$SQLSrvObj.logins[5].rename('MXTLPT01\Dev01')

3. And, finally we use the *.refresh() to update all logins list with the name change.

$SQLSrvObj.logins.refresh()

AS you can see,  this open to some automation opportunities that can involve Windows Domain with SQL Server Accounts administration.

Don’t forget! always test your scripts. Practice makes a good scripter, and never be afraid of trying new stuff.

SQL PowerShell! It is the way!

Changing a SQL Server Login name with T-SQL

It’s been some time since my last blog post! Changing a SQL Server Login name uising  T-SQL.

Everyone who has tried at first, knows that it has its caveats. I couldn’t find a straight answer to the errors I was getting. You would think changing the sql login name should be simple. But, I was using the wrong approach.

First, we are working with “WindowsUser” login type. In SQLServer, I’m trying to fix a Windows User (local account)name  from “dev01” to “devUser01” using the following T-SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

but, in  doing so I got the following error message:

“Windows NT user or group ‘MXTLPT01\DevUser01’ not found. Check the name again.”

Of course, “MXTLPT01\DevUser01” Windows user account doesn’t exist on my system.

So, I created the user I try to run the T_SQL statement:

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\Devtest01]

Nope, it didn’t not work ending with the following message:

Msg 15098, Level 16, State 1, Line 31
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.

Basically, It translate to your Windows Account SID Object in SQL Server doesn’t match the object you’re trying to change.

In other word, you need to work with the Windows User originally created in WINDOWS (Locally or Domain), has changed first before executing the T-SQL statement “Alter Login… With Name=…”will be successful:

I ran the statement after the the change took place in my Windows system;

ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]

This time it works.

Wait! What about using PowerShell?

Check out the next blog post “PowerShell Working with SQL Logins Name

Happy SQLAdmin!

Fixing the SQL Server Configuration SERVERNAME function value after renaming the computer name

You own a personal computer, and you want to learn about SQL Server. Then, you probably installed the SQL Server Developer Edition, which is free. But, there will be times when you may decide to change the computer name. And, because you have already installed SQL Server, you will find there are a few things in the SQL Server configuration that will need to be corrected.

The first sign of something wrong!

In my case, I renamed my computer name from ‘DESKTOP-K6AKFTA‘ to ‘MAXTLAPTOP1‘ with SQL Server previously installed. Then, while executing an SSIS SQL job, the job failed to execute giving me the following error:

Keep in mind, the User-id was added to the SQL Server security logins group with the previous computer name. Here is where you’ll find that something is not right.

Fixing the Computer Name for the User-Id

Use the SQL Server Object Explorer and navigate to the “Security\Logins” folder. Here you can find the local user with the wrong machine name.

To correct the User-Id, just right-click and select “Rename” to change the computer name.

image

Next, right-click in the User-Id, select “Properties“, and then go to “Securables“. Here you’ll find the SQL Server Instance Name is wrong.

image

This needs to be fixed!

Check the current SQL Server Configuration Server Name

To verify the SQL Server configuration system name use the “@@SERVERNAME” function by executing the following T-SQL script:

## - Execute the following T-SQL command:
--
-- Get SQL Server first time "Installed" Servername with Instance Name:
-- Note: Assuming this is run after the system has been renamed.
--
Select @@SERVERNAME CurrentSvrNameWithSqlInstanceName;

--Output:
CurrentSvrNameWithSqlInstanceName
DESKTOP-K6AKFTA\MSSQL2K19A

As stated in the Microsoft documentation, this function will display the value saved after the SQL Server installation. This value is will not be updated if the computer gets renamed.

By executing the above T-SQL script you’ll notice the return will not match the new computer name.

Fixing the SQL Server Configuration Server Name

To fix the SQL Server Configuration “@@SERVERNAME” function value you’ll need to use two of the Security Stored Procedures: “sp_dropserver” and “sp_addserver“. Below, I created a Dynamic SQL (T-SQL) Script solution to take care of this issue:

First, using the “sp_dropserver” to drop the server name:

--
-- Drop the wrong SQL Server Configuration @@Servername:
--
Declare @TSql1 nvarchar(max);
Declare @cfgSvrNameWithSqlInstName nvarchar(50);
Set @cfgSvrNameWithSqlInstName = (Select @@SERVERNAME);
Set @TSql1 = 'sp_dropserver ' + '''' + @cfgSvrNameWithSqlInstName + '''';

print @Tsql1;

exec sp_executesql @TSql1;
GO

Next, I use the “sp_addserver” to add back the correct server name:

--
-- Fix the SQL Server Configuration @@SERVENAME with SqlInstance Name:
--
Declare @TSql2 nvarchar(max);
Declare @fixSvrNameWithSqlInstName nvarchar(50);
Set @fixSvrNameWithSqlInstName = (Select convert(nvarchar, SERVERPROPERTY('servername')));
Set @TSql2 = 'sp_addserver ' + '''' + @fixSvrNameWithSqlInstName + ''''+ ', local';

print @TSql2;

exec sp_executesql @TSql2;
GO

Then, to verify the change works, use the following T-SQL statement:

Select @@SERVERNAME

Or, in SSMS, use the SQL Server Object Explorer, navigate to the “Security\Logins” folder, right-click in the User-Id, select “Properties“, and then go to “Securables“. Then, you’ll see the server name has been fixed.

Now, can this be automated? Yes, we can use PowerShell to automate this process.
That’s the next step.

Stay tuned for more!

Streamlining SQL Server Management Objects (SMO) In PowerShell 7 (Revised)

It’s been over two years since I touch this topic, so here’s an updated post about using SQL Server Management Object (SMO) on the latest PowerShell Version 7.

Here’s 411 on what’s out there!

For the most part, nowadays you can use SMO to  connect:

1. Windows to Linux.
2. Linux to Windows.
3. Windows to Linux Containers.
4. Linux to Linux Containers.
5. Windows to Windows Containers.
6. WSL to Linux Containers or Windows.

And, of course, will include cloud technologies.

Now, we have to extend our skills thanks to Docker Container.

*Note: Any connection issues connecting from Linux to Windows, can be solved by creating the inbound rule for Linux in Windows Firewall.

Ways to use SMO

There are two ways you could use SMO in PowerShell 7 (cross-platform):

1. Installing the SMO NuGet packages, two packages are requiered:
a. Microsoft.SqlServer.SqlManagementObjects Version 150.18208.0 (as of 03/23/2020)
b. Microsoft.Data.SqlClient Version 1.1.1 (recommended)

2. Installing the PowerShell Module: SqlServer Version 21.1.18221 (as of 03/23/2020)

Keep in mind, once the packages and/or modules are installed, you need to update them manually.

Working with SMO NuGet Packages

To install the Microsoft.SqlServer.SqlManagementObjects package. You first need to verify that Nuget Package Management is registered in PowerShell 7. Execute the following code will do the task of registration:

function Verify-NugetRegistered
{
[CmdletBinding()]
Param ()
# Microsoft provided code: Test Auto sAVCE
# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux/Windows)
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." -ForegroundColor Yellow;
};
}; Verify-NugetRegistered;

Now, here’s the tricky part. There’s a known issue when executing the Install-Package cmdlet which will fail to install the package.

The workaround is to download the Nuget.exe CLI and place the executable in the following folder: “C:\Program Files\PackageManagement\NuGet\Packages.”

This is the PowerShell default path for storing Packages, and it may not exist in the beginning. So you may need to manually create the folders.

To install the SMO packages needed, execute the following command in PowerShell 7 prompt as an Admin:

cd 'C:\Program Files\PackageManagement\NuGet\Packages\'
./nuget install Microsoft.SqlServer.SqlManagementObjects -version 150.18208.0
Pause
./nuget install Microsoft.Data.SqlClient -version 1.1.1
Pause

Notice, I included the versions of the packages as of 3/23/2020. These SMO packages will support SQL Server 2019 or older, but keeping in mind the older the SQL Server version the latest features will not apply.

Also, these packages doesn’t contain any PowerShell cmdlets, they are meant for developing solution from scratch. For example, below I’m creating an SMO script to connect to a SQL Server providing my SQL authentication, query to get the SQL Server engine version, and manipulate the results from the script.

## - PowerShell 7 loading .NET Core netstandard 2.0 library SMO dll's:
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netstandard2.0);

Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.SmoExtended.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Management.Sdk.Sfc.dll);

## - Prepare login credentials:
$SQLServerInstanceName = 'sapien01,1449';
$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);

## - Sample T-SQL Queries:
$SqlQuery = 'Select @@Version as FullVersion';

## - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);
$GetVersion = $result.tables.Rows;
$GetVersion.FullVersion.Split(' - ')[0];

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, `
@{ label = 'FullVersion'; Expression = { $GetVersion.FullVersion.Split(' - ')[0]; } }, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

The best thing! This Package is supported cross-platform so you can execute the script on any OS.

The beauty of coding with SMO is that everything is documented. Just check the Microsoft Documentation “SQL Server Management Objects (SMO) Programming Guide“.

Working with SqlServer Module

Now, using the SQL Server Module in PowerShell 7 is makes it a bit simple to install. And, it’s supported cross-platform.

Just execute the following command as an Admin:

Install-Module -Name SqlServer -AllowClobber

The latest version contains a total of 66 commands you can use to manage your SQL Server engine.

Now, besides having all of these commands available, in the future, you may have the need to create custom functions.

Here’s the variation of the previous SMO script sample:

## - Import the SqlServer module which it loads all needed SMO assemblies:
Import-Module SqlServer

## - Prepare login credentials:
$SQLServerInstanceName = 'sapien01,1449';
$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);

## - Sample T-SQL Queries:
$SqlQuery = 'Select @@Version as FullVersion';

## - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);
$GetVersion = $result.tables.Rows;
$GetVersion.FullVersion.Split(' - ')[0];

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, `
@{ label = 'FullVersion'; Expression = { $GetVersion.FullVersion.Split(' - ')[0]; } }, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

The differences is quite simple. All SMO assemblies are previously loaded when you import the SqlServer module. So, you don’t have to worry about including the assemblies in the code. Make sure to check all of the commands available that can help you manage the SQL Server.

Additional Tools Available

Now, don’t forget to check other SQL Server community tools that are available, such as:
1. DBATools – SQL SMO PowerShell.
2. MSSql-Scripter – Python-based tool.
3. Mssql-cli – Python-based tool.

And, don’t forget to check out .NET Interactive which brings Jupyter Notebook with PowerShell kernel.

If you want to try .NET Notebook, I suggest to first install Anaconda (Python 3.7) which makes it simple to use in Windows.

If you want to experiment with .NET Notebook without installing anything in your system, then try MyBinder. This is a web-based .NET Notebook that’s run from a container.

Unfortunately, in this scenario, only the PowerShell 7 core modules are available. But at least you will be able to learn the essentials of .NET Notebook.

Go ahead and start using this Amazing technology!

PowerShell Core – Working with Persistent Disk Storage in Docker Containers

This quick blog post will hope to give you a heads up in how to work with container(s) disk data. It’s a known fact that container(s) storing data will not persist if the container is removed. Yes! If you build a container to store your data, it will be gone.

Containers are perfectly suited for testing, meant to fast deployment of a solution, and can be easily deployed to the cloud. It’s cost effective!

Very important to understand! Containers disk data only exist while the container is running. If the container is removed, that data is gone.

So, you got to find the way to properly configure your container environment to make the data persist on disk.

Persisting Data

There are *two quick way to persist data when working with container(s):

1. Create a docker volume.
2. Or, use a local machine folder area.

*Note: There are other solution to help with persisting data for containers, but this a good starting point.

I’m using the docker command line for now. Later, I will be creating some blog post about using Docker Compose and Kubernetes.

I love to use PowerShell Core with Docker command line!

Docker Create Volume

Using docker command “docker volume create <nameofvolume>” will create the volume to help persist data on your local machine.

docker volume create MyLinuxData

Use the following docker commands to check your newly created volume:

* To list all existing docker volume(s):

docker volume ls

* To check “inspect” a docker volume(s) to provide detail information:

docker volume inspect MyLinuxData

Using the “docker volume inspect <VolumeName>.” command line, it will show the volume mount location:

“Mountpoint”: “/var/lib/docker/volumes/MyLinuxData/_data”,

In this case, the mount location is on the Linux box under the Docker Volumes folder. This means all data can persist on you local machine.

Local Machine Folder

This option seems straight forward as there’s no need to create a Docker Volume. Just use the ‘-v’ switch in the Docker Run command line.

In the following command line I’m activating the Docker container with previously configured Microsoft SQL Server instance. I include the ‘-v’ switch to mount a folder on my local machine.

docker run -p 1455:1455 -v /home/maxt/TempSQLBackups:/home/TempSQLBackups --name sql2k19ctp23_v02 -d sql2k19_ctp2.3_sandbox:CTP2.3-Version02

Notice in this case, to verify that my SQL Server container has mount to my the local machine folder, I can execute the following command:

docker exec -i sql2k19ctp23_v02 ls /home/TempSQLBackups

Using “docker exec -i <containerid/name> ls <containerfolderlocation” will display the results of all the files back to the screen. Now, anything you add to that local folder will be accessible to the container.

Summary

This is a good starting point when learning how to work with Docker data in containers. You’ll still go thru trails-and-errors while learning how to build container images, and make data persist for your application. But, it’s much faster and easier to rebuild images. This is one of a most to learn technology.

References

Check out the following blog post as it help me understand about “Persistent Storage”:

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

Getting the latest Tools for PowerShell SQL Server Automation

You all know how important is to have the tool that can make our life easy do our system administration, and become a hero in our organization. Here’s a startup helper guide to get you going with some PowerShell and SQL Server tools.

What is available for automation!

For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)

Here are some important PowerShell Modules to use for SQL Server management scripting:

  • *SQLServer – This module currently can be use on SQL Server 2017 and greater.
  • *DBATools – This a community supported module that will work with SQL Server 2000 and greater.
  • DBAReports – Supports for Windows SQL Server.
  • DBCheck – Support for Windows SQL Server.

*Note: This module is coming popular in cross-platform systems (non-Windows)

All of the above module can be downloaded from the PowerShell Gallery from the PowerShell console using the Install-Module cmdlet.

Install-Module -Name SQLServer -Force -AllowClobber;

Now, when working with older versions of SQL Server (2008->2017), you will find the SQLPS module is loaded during the SQL Server installation.

Just remember, since SQL Server 2017, Microsoft has change the PowerShell SQLPS module to SQLServer module downloadable from the PowerShell Gallery. This module is not available in PowerShell Gallery, only available during the SQL Server installation.

When PowerShell SQL Server Module can’t provide a script?

It won’t hurt to install the SQL Server Management Objects (SMO) library in case you want to be creative and start building your own SQL PowerShell scripts. This library is already available cross-platform, meaning that it will work in Windows, Linux and MacOS environments.

In this case, you can install the SQL Server SMO library “Microsoft.SqlServer.SqlManagementObjects” from the PowerShell Console using the Install-Package cmdlet.

Install-Package -Name Microsoft.SqlServer.SqlManagementObjects -AllowPrereleaseVersions;

Wait! There is more

As you already know, to manage SQL Server in Windows environment, we use the SQL Server Management Studio. But, this
application won’t work cross-platform.

So, the cross-platform option available is Azure Data Studio (February edition):

Don’t forget to include for following extensions:

What about Python?

By now you should already know that Python has been around for many year as cross-platform interpreted object-oriented high-level language. And, its popularity keeps increasing.

I would recommend to take a look at the Anaconda Distribution, and specifically the one with the latest version of Python (v3.7).

Download Anaconda for data science platform:

This installation will include *All* Python packages available to build an application.

And, Python can interact with PowerShell too!

Ah finally Containers!

Yes! Containers has become popular and can’t be ignored. It can be use in both Windows, Linux and any cloud environments. Go ahead to learn how to work and manage Docker containers.

Docker site to Download the Docker CE.

Don’t forget to check Docker Hub to find the latest Docker Container images available for download. And, you will need to create an account before downloading images.  The image below shows how-to search for the SQL Server image.

In Summary

As technology will keep improving, make sure stay up-to-date. This give us the opportunity to improve our job position and be of value for the organization that hire us.

Don’t forget to look for the nearest technology event in your areas, as this is the opportunity to learn for free and gain invaluable knowledge.

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