Using Linux SQL Server SMO in PowerShell Core

Yes! It’s possible. Here’s the information in how to set it up and start doing some PowerShell scripting. But, first understand that everything posted here is still a Work-In-Progress. And, the good news, it’s all Open Source.

I hope you find the following information essential as there’s no really any instruction in how to install these components. So, let’s get started!

Where To Get It!

The Microsoft SQL Tools Service is a set of API that provided SQL Server Data Management capabilities on all system cross-platforms. It provide a small set for SMO dll’s enough to get started.

You can download the file from following Github link: 

Here’s the list of available SMO DLL’s currently include in the “SqlToolsService – ServiceLayer” file:


Keep in mind, this list will continue to grow and we hopefully expect more SMO DLL’s added.

Installation pre-requisites

In my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file

As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.

Just in case, here’s the link to download “.NET Core 2.0 Preview 1“:

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:

Windows Installation

You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

To make *”Sql Tools Services” to work in PowerShell Core, I had to extract all content in the file into the “C:\Program Files\PowerShell\6.0.0-Beta.x” folder. Remember, this will replace any existing DLL’s on that folder.

*Caution: This steps should be done on a test machine as there’s always a possibility that it could PowerShell Core DLL’s.

Don’t forget that all these components are still in development but this should stopped us from trying and even contributing.

The file you’ll need to download for Windows is:

Please, for now ignore the *microsoft.sqltools.credentials*.  If you install the Credentials DLL’s in the PowerShell Beta folder, PowerShell will not work.

Linux Installation

Now, for Linux is a different story as there’s no need to add the DLL’s in the PowerShell Core folder. You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

I would recommend doing the following steps in the Bash Console:

1. At your /home/user-name location, create the sqltoolsservice folder:

maxt@MyUbuntu01:~$ mkdir sqltoolsservice

2. Change directory and Download the file for Ubuntu:

maxt@MyUbuntu01:~$ cd sqltoolsservice/
maxt@MyUbuntu01:~/sqltoolsservice$ wget

3. Continue extract the *tar.gz into the folder:

maxt@MyUbuntu01:~$ tar -xzvf microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz

That’s it for Linux. Now, you are ready to work with SMO and PowerShell.

Testing SMO in PowerShell Core

This is changing my way I script SMO in PowerShell. As my normal way I’ve been scripting SMO in PowerShell doesn’t work in PowerShell Core. Basically, a few more lines need to be added and now I will use the Add-Type to get the SMO assemblies loaded.

Loading SMO Assemblies

The first step is to load the SMO assemblies needed to start working with SQL Server. So, the following line is finally depricated and won’t work:


The old method I’ve been using for a long time will failed because is expecting the “Property Login …” to be set.

The updated way, has been replaced by the Add-Type with the following essential three assemblies:

## - Loadind SQL Server SMO assemblied needed:
$Assem = (
); Add-Type -AssemblyName $Assem;

The above assemblies are required in order to work since SQL Server SMO 2012 and greater. You can have limited use when connecting to SQL Servers version 2005, and possibly 2000.

Prepare connection parameters for Windows Systems

In Windows systems, we use ‘Integrated Authentication‘. But, here’s where things change a bit since SQL Server 2012 SMO. You will need to prepare the connection parameters, and set the *.UseIntegratedSecurity property to ‘true‘ (the default is ‘false‘). At the same time, you’ll need to set the password to ‘null’ in order to connect successfull.

## - Prepare connection strings and connect to a Windows SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'winUsername';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $null);
$SQLSrvConn.UseIntegratedSecurity = $true;
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Now, you can query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Prepare connection parameters for Linux Systems

For Linux systems, we use ‘SQL Authentication’. Here we add the SQL User password, then passing the value to the SqlConnectionInfo class.  And, the *.UseIntegratedSecurity property by the default is ‘false‘.

## - Prepare connection strings and connect to a Linux SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'sqluser01'; $sqlPwd = '$usrpwd01!';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd)
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Again, you can proceed to query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Please notice in the above image, the Windows 10 Insider Build 16215 Bash Console is running PowerShell Core. This list insider release made it possible for PowerShell Core to be functional again.


As we can see, this opens new opportunities to build cross-platform PowerShell scripts solutions working with SQL Servers in Linux, Windows, and others.

This is very exciting to start experiencing first hand these upcoming changes. I can’t deny that’s it’s challenging but you can’t turn down an opportunity to gain more skills.

Please, take advantage and subscribe to Microsoft Azure. Build, test, and start deploying solutions. Don’t be afraid to be creative. We all learn thru trial and errors!

This is a good time to keep up with what’s going on with technology.

Additional References:

Microsoft Azure:
Microsoft Windows Bash Shell:
Microsoft Academy:
Microsoft Channel 9:
Microsoft MVP Blog:
Microsoft SQL Server Docs:
Microsoft PowerShell Blog:

PowerShell, and SQL Server Working with Anaconda

On my previous blog “PowerShell – Working with Python and SQL Server“, I show how to install Python 3.5 so we can be build python scripts to connecting to SQL Server and use them with PowerShell.

Now, since the release of SQL Server 2017 and the integration of Anaconda (ie. Python 3.6), we need to know what it takes to successfully install Anaconda on your developer system(s) both Windows and Linux.

Installing Anaconda in Windows

In Windows the installation is simply done through the SQL Server 2017 setup process. During the SQL Server installation process, select the “Machine Learning Services (In-Database)” option and this will automatically install both “R” and *”Anaconda” on your system.

*Note: Installing Anaconda (Python 3.6) will redirect any previous version of Python to version 3.6. So, you may need to manually revert back to use older version.

Installing Anaconda in Linux (Ubuntu)

There are few more steps to complete the installation on *Linux. First, verify which is the latest version available by going to the following link:

Then follow these steps in bash console:

1. Change directory to where you want to store the installation file:

$ cd Downloads

2. The “curl” command for the latest version available:

$ curl -O

3. Run the installation command:

$ bash

4. Enter “Yes” to Accept the license agreement.

6. Then, you can select the location where Anaconda will be installed. The default is the user home folder.

5. Add the Anacona path to user profile in the “.bashrc” file by answering “Yes” and this will force to open Python on version 3.6.

6. Finally, to activate Anaconda, type the following command:

$ source ~/.bashrc

If you want to use any previous version, then you’ll need to manually type the PythonX.x executable. Try the following commands to open other versions of python previously installed in Ubuntu: python3.5, python2, or python2.7.

*Note: These steps can be applied to WSL Windows 10 Bash.

Using “update-alternatives” Linux Command

You could also setup the “update alternatives” command to swapt between the different versions of Python. This command need to be executed under super-user privilege “sudo su“.

Below is the series of commands use with “update-alternatives“:

##-> Install python for 'update-alternatives' command use:
$ sudo su
# update-alternatives --list python # will not display python

##-> To setup to use different versions:
# update-alternatives --install /usr/bin/python python /usr/bin/python2.7 5
# update-alternatives --install /usr/bin/python python /usr/bin/python3.5 1
# update-alternatives --install /usr/bin/python python /home/Username/anaconda3/bin/python3.6 2

##-> To list all installed pythons:
# update-alternatives --list python

##-> To change Python version, then select which version
# update-alternatives --config python

##-> You can use the --remove parameter to get rid of any lines added:
# update-alternatives --remove python /usr/bin/python3.5

Remember, in Ubuntu Linux, the system default version of Python is 2.7.

It would be a bad routine, when using the “update-alternatives” command, to change back to the default version as all running scripts during the system updates will need run on Python 2.7.

Additional Package for SQL Server

During the Anaconda installation, you’ll notice that it will load lots of python packages for data science and including “tk” which provide the ability to create GUI applications.

But, there’s one package missing, “pyodbc” will be needed in order to create python scripts to connect with SQL Server.

I did install PYODBC in both Windows and Linux, run the following command at the console:

conda install pyodbc

Then, to test this package was loaded, open *python and type:

import pyodbc
## - Connect to database:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,51417;DATABASE=master;UID=sa;PWD=$SqlPwd01!')
cursor = cnxn.cursor()

Unfortunately, in Ubuntu Linux, the connection string will fail giving the following error:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,51417;DATABASE=master;UID=sa;PWD=$SqlPwd01!')
Traceback (most recent call last):
File "", line 1, in
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/' : file not found (0) (SQLDriverConnect)")

Strangely enough, this error is only on Ubuntu Linux and not Windows installation. So, Python 3.6 will work on Windows to build your scripts to work with SQL Server while Microsoft and/or Anaconda figured this one out.

*Note: This sample connection string to SQL Server is done thru SQL Server Authentication.

Configuring Anaconda in SQL Server 2017

This is only available in SQL Server 2017 and SQL Server Management Studio v17 with the feature of integrating Anaconda (Python 3.6) with SQL Server is to be able to execute the python script(s) from SQL Server Stored-Procedure.

The following steps need to be complete to enable SQL Server to execute Python scripts as an external script from SSMS SQL Query or within a stored-procedure.

1. Execute the following T-SQL command:

sp_configure 'external scripts enabled', 1

2. Then, SQL Server Service will need to be restarted for the changes to take place.

3. Proceed to execute a python script from SSMS SQL Query panel:

execute sp_execute_external_script
@language = N'python',
@script = N'
import sys
print("Hello SQLServer, I am Python Version:")

Unfortunately, I haven’t been successful to run the SSMS SQL query connected to a SQL Server on Linux. So, apparently there’s still a limitation in Linux.

What with PowerShell!

So the main purpose of integrating Anaconda (Python 3.6) with SQL Server is to be able to execute the script from SQL Server Stored-Procedure. But, one of Anaconda installed packages is ‘tk‘.

The ‘tk‘ package allows you to create GUI application in Python. This opens opens opportunities to develope and integrating some solution with PowerShell. For example, PowerShell v6 Alpha doesn’t have the Out-GridView command available yet.

So, here’s a raw with limited functionality of a python Out-GridView look-a-like. The following sample code will access some data from SQL Server, use PowerShell to manipulate the information, and then use Python ‘tk’ component to display it in a GUI datagrid.

$runpy = @'
import pyodbc
from tkinter import *

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,1738;DATABASE=master;UID=sa;PWD=$Adm1n!')
cursor = cnxn.cursor()

#Execute T-SQL Query:
trecord = []
tsql = 'SELECT Name, Location, Hire, HireDate FROM SampleDB1.dbo.HolidayEmployees;'
if cursor.execute(tsql):
row = cursor.fetchone()
while row:
datarow = [str(row[0]),str(row[1]),str(row[2]),str(row[3])]
row = cursor.fetchone()

## - list to screen list of data and will get number of rows in the list:
i = 0;
for i, rec in enumerate(trecord):

for i, rec in enumerate(trecord):
col = 0;
for c in rec:
Label(text=c, relief=RIDGE, width=15).grid(row=i, column=col)
col = col + 1;


python -c $runpy;

As you can image, there’s a lot of room to grow for integrating technologies such as PowerShell and Python. Just be creative!

Additional Tips

1. To edit, or commented out, the Anaconda Path, in the .bashrc file:

$ sudo gedit ~/.bashrc


2. To find out all installed packages in Anaconda, use the following command:

$ conda list

3. Upgrading Anaconda to newer version:

## - Windows:
conda update --prefix 'C:\Program Files\Anaconda3' anaconda
## - Linux:
$ conda update anaconda

Additional Resources

* Don’t forget to check out Microsoft Data Amp Technical Sessions at:
* Check What’s new about SQL Server 2017?
* Getting started in SQL Server on Linux:
* Download Anaconda:

PowerShell, SQL Server, and Linux Oh My!

The South Florida Code Camp 2017 is ON! Come and join me in this event on Saturday, March 11th 2017 all day event.

Check out the event speaking agenda:

I will be presenting the following 3 sessions:

1. 09:50am – 11:00amWorking with SQL Server for Linux Cross-platform:
I will be showing the latest build of SQL Server vNext for Linux. Everything you need to install and connect to SQL Server. Then, what tools are available to use from either Linux and Windows. Sample on querying for database information using Python and PowerShell between two environments. This will be a demo intensive session.

2. 11:10am – 12:20pm Using Windows 10 Bash with PowerShell Open Source:
We’ll be taking a tour into Windows 10 Bash Linux subsystem. I’ll be sharing some tips on how to work with Bash, and the workaround to make PowerShell in side BASH. This is the perfect environment to start learning about Linux while you work in windows. We’ll be take advantage of how-to use cross-platform Open source product(s). All this and more from Windows 10.

3. 01:20pm – 02:30pm Using PowerShell Open Source across multiple Platforms:
PowerShell is Open Source now! Come see how you could use PowerShell cross-platform between Windows and Linux sharing scripts to assist in admin task automation. I’ll be walking on how to make this work with existing tools. Also, interacting with SQL Server for Linux.

To register go to the following link:

IDERA Geek Synch Webinar – Wednesday February 22nd, 2017

Topic: Using PowerShell with Python and SQL Server

Description: Just as PowerShell is argued as the main technology for automation in Windows Datacenters Infrastructure, it is equally important in other non-Windows Operating Systems. According to Maximo Trinidad, Windows Administrators have an advantage working with PowerShell just as Linux Administrators have an advantage with Bash / PHP / Python.

Webinar starts at: 11:00AM – 12:00PM (CDT) / 12:00pm – 01:00pm (EST)

Register at:

Florida PowerShell User Group Online Meeting – Thursday February 23rd, 2017

Topic:  Understanding Bash On Windows 10

Description: Come and learn how to setup and use Bash On Windows 10. Learn the tips and tricks to use PowerShell and Python together. At the same time, there are some differences in working with script files between Bash and Windows systems. We’ll be seen how to use efficiently use Windows apps with Python scripts on Bash. Lots of demo!

Meeting starts at: 6:30pm(EST)

Register at:

Running PowerShell In Windows 10 BASH (workaround)

Yes! It’s possible. There’s a workaround make PowerShell to work in Bash for Windows. The only drawback, you still can’t do a Clear-Host (or clear or cls), at the PowerShell prompt.(yet)

(Updated 11/03/2016):  You can use Ctrl-L to clear the screen while using both ‘xterm’ and ‘screen’ while working at the console only.  At least is better than nothing!


The original behavior, and still is, the cursor goes to the top of the screen and it making hard to work.   This issues has been logged in Github PowerShell site:

But, let first install PowerShell for Bash on Windows.

Installing PowerShell

To identify which version of PowerShell to install, you’ll need to find what’s the Windows Bash version. Run the following Linux command:

username@hostname$ cat /etc/issue


This command will show the Linux OS is Ubuntu Bash version is 14.04. Next is to  proceed to Github PowerShell and select to download the Ubuntu 14.04 version:

Look for and click on “powershell_6.0.0-alpha.10-1ubuntu1.14.04.1_amd64.deb” to initiate the download.

Please understand that this file type is *.deb and will only install on the correct Linux OS version. This file will be download to your Windows “Downloads” folder and you won’t be able to execute from Windows File explore.


Now, confirm you downloaded and can see the file under Bash subsystem by doing the following Linux command:

$ ls /mnt/c/Users/mtrinidad/Downloads


To installing the PowerShell version for Ubuntu 14.04, do the following commands:

$ cd /mnt/c/Users/mtrinidad/Downloads
$ sudo dpkg -i powershell_6.0.0-alpha.10-1ubuntu1.14.04.1_amd64.deb
$ sudo apt-get install -f

Now, you can run PowerShell from the Windows 10 BASH prompt. But, it won’t be pretty useful. Yet!

What’s the workaround PowerShell in Bash for Windows

Basically, there are two application you can try:
In my case, I didn’t have to installed these applications.  But, if you need to install either application, the use the following command lines and answer “y” to install:
$ sudo apt-get install screen 
$ sudo apt-get install xterm
Now, there is one more application that might be useful to install using the same command format (see above). Its the GUI editor “gedit”:
$ sudo apt-get install gedit
This is a very practical text editor that remind me of Notepad.  Keep in mind, I’m coming from a Windows Ecosystem.  Of course, Linux expert may go for Vim or Emacs editors.

Xserver for Windows 10

 Two of these applications: xterm and gedit that will need Xserver running in Windows 10.  I’m currently using “VcXsrv” and can be downloaded from:
After installation, double-click on the XLaunch icon and select how-to display the program.
Then, go back to the Bash prompt and type the following command the set the Xserver popup window, follow by the application. In this example will be executing “gedit“.
$ export DISPLAY=localhost:0
$ gedit /mnt/c/Users/mtrinidad/Documents/test.txt
In order to use application(s) in *Xserver, its only one application at the time per Bash console open. In other word, to run two applications, you’ll need to open two Bash console to run each individually.
*Note: You will notice  Warning messages after exiting the application which can be ignored.
Now, we got all we need to start working with PowerShell in Bash for Windows.

PowerShell – Using xterm

Now, to use “xterm“, you’ll need run the Xserver application “VcXsrv” as previously explained. Follow the steps: (In my case, I’m opening PowerShell Console in Administrator mode)

  • Open PowerShell Console (in Administrator mode)
  • Execute Bash
  • Then, at the Bash prompt type: $ export DISPLAY=localhost:0
  • Follow by: xterm

This will open a popup window for xterm application. In the xterm prompt, run powershell and you can start working with PowerShell.

$ powershell


But wait!! You’ll notice that there’s no scroll bar to page up or down. I found the following link that fix the issue and you can enable the scroll bar using the mouse.

This is where you use gedit text editor to add the code to make the scroll bar to work with the mouse.


Now you can use PowerShell to xterm application.

PowerShell – Using screen application

This is another workaround to work with PowerShell by using the “screen” application without the need of using Xserver program.

Just open the PowerShell console (in Administrator mode) then type “screen” and press enter.


Oops! This application will need to be executed as super user:


Keep pressing enter to bypass the screen information prompt.


You will ended up at a # prompt, then you can execute PowerShell:

# powershell

And start working with PowerShell. But, again you’ll notice, you won’t be able to scroll up and down (again).  There’s not scroll bar!

Here’s how to fix the “screen” scroll issue: press Ctrl-A and then ESC. Now, the scroll up and down feature will be enabled.


And, at the same time, both the arrows up/down keys are working during the remaining of your session.


Now, exiting the “screen” application, you will ended up type the “exit” command a few times. About 3 time to get to the starting point.


There’s no excuse not to use PowerShell In Bash for Windows. I have to admit it was little painful to gather this information and work around. But I think is very useful.  This is Awesome!

 Useful Resources

Make sure to check the GitHub incident email threads, mention in the beginning of this blog, which leads to make this workaround possible: #933 and #988, Community contribution made this possible.

Here are some addition links that help me push through this workaround:

** Xterm

** screen

** Finding Linux version:


PowerShell and BASH setup walkthru

Yes! I finally got the latest Windows 10 Build 14328 which includes BASH Ubuntu Linux subsystem.  I took me few days since my organization did a Windows 7 upgrade to Windows 10 Version 1511. Then, thru Windows Insider, getting the latest version with Bash.


If it worth knowing that in order to get Bash, it’s a feature you need to installed it first. The following is the series of steps I use to enabled and install Bash on my desktop. And, after enabling Bash, I started using it under the PowerShell Console.

Set bash Feature On

Go to Control Panel, under Programs click “Uninstall a program” then click on “Turn Windows features on or off“.  Under the “Windows Features” popup windows Look and Enable “Windows Subsystem for Linux (beta)”. Click OK to accept changes.


Using PowerShell Console and Set Developer Mode

Open PowerShell Console but make sure you already have set the “Execution Policy” to at least “RemoteSigned“.  Type and execute “bash” but it won’t work because you need to have your Windows 10 set to Developer Mode.


The following screen show how to go thru the “Setting” panel to enable Windows Developer Mode.

WindowsBash_03 WindowsBash_04 WindowsBash_05 WindowsBash_06

Ready for Bash

At the PowerShell prompt type “bash” and press enter. This will ask to enter “y” to initiate the installation of Ubuntu on Windows.

WindowsBash_07 WindowsBash_09

After the installation is completed the next step is to create a User-ID and Password.


Now, you can start using Linux within PowerShell.


To get back to PowerShell just type “exit“. How cool is that!!