Category: Bash

Using Linux SQL Server SMO in PowerShell Core

Posted on by admin

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

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

[sourcecode language=”text”]
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
[/sourcecode]

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“: https://www.microsoft.com/net/core/preview#windowscmd
https://www.microsoft.com/net/core/preview#linuxubuntu

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:
https://github.com/PowerShell/PowerShell/releases

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: microsoft.sqltools.servicelayer-win-x64-netcoreapp2.0.zip

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:

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ mkdir sqltoolsservice
[/sourcecode]

2. Change directory and Download the file for Ubuntu:

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ cd sqltoolsservice/
maxt@MyUbuntu01:~/sqltoolsservice$ wget https://github.com/Microsoft/sqltoolsservice/releases/download/v1.0.0-alpha.34/microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz
[/sourcecode]

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

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ tar -xzvf microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz
[/sourcecode]

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:

[sourcecode language=”powershell”]
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”)
[/sourcecode]

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:

[sourcecode language=”powershell”]
## – Loadind SQL Server SMO assemblied needed:
$Assem = (
“Microsoft.SqlServer.Management.Sdk.Sfc”,
“Microsoft.SqlServer.Smo”,
“Microsoft.SqlServer.ConnectionInfo”
); Add-Type -AssemblyName $Assem;
[/sourcecode]

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.

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

Now, you can query the PowerShell Object $SQLSrvObj.

[sourcecode language=”powershell”]
## – Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;
[/sourcecode]

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

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

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

[sourcecode language=”powershell”]
## – Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;
[/sourcecode]

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.

Conclusion

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: https://azure.microsoft.com/en-us/
Github: https://github.com/
Ubuntu: https://www.ubuntu.com/
Microsoft Windows Bash Shell: https://msdn.microsoft.com/en-us/commandline/wsl/about
Microsoft Academy: https://mva.microsoft.com/
Microsoft Channel 9: https://channel9.msdn.com/
Microsoft MVP Blog: https://blogs.msdn.microsoft.com/mvpawardprogram/
Microsoft SQL Server Docs: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
Microsoft PowerShell Blog: https://blogs.msdn.microsoft.com/powershell/

PowerShell, and SQL Server Working with Anaconda

Posted on by admin

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: https://www.continuum.io/downloads

Then follow these steps in bash console:

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

[sourcecode language=”bash”]
$ cd Downloads
[/sourcecode]

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

[sourcecode language=”bash”]
$ curl -O https://repo.continuum.io/archive/Anaconda3-4.3.1-Linux-x86_64.sh
[/sourcecode]

3. Run the installation command:
[sourcecode language=”bash”]
$ bash Anaconda3-4.3.1-Linux-x86_64.sh
[/sourcecode]

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:

[sourcecode language=”bash”]
$ source ~/.bashrc
[/sourcecode]

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“:
[sourcecode language=”bash”]
##-> 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
[/sourcecode]

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:

[sourcecode language=”bash”]
conda install pyodbc
[/sourcecode]

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

[sourcecode language=”python”]
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()
[/sourcecode]

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

[sourcecode language=”python”]
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/libmsodbcsql-13.1.so.6.0’ : file not found (0) (SQLDriverConnect)”)
>>>
[/sourcecode]

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:

[sourcecode language=”sql”]
sp_configure ‘external scripts enabled’, 1
reconfigure
[/sourcecode]

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:

[sourcecode language=”sql”]
execute sp_execute_external_script
@language = N’python’,
@script = N’
import sys
print(“Hello SQLServer, I am Python Version:”)
print(sys.version)
‘
[/sourcecode]

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.

[sourcecode language=”powershell”]
$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])]
trecord.append(datarow)
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):
print(rec);

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;

mainloop()
‘@;

python -c $runpy;
[/sourcecode]

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:

[sourcecode language=”bash”]
$ sudo gedit ~/.bashrc
[/sourcecode]

 

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

[sourcecode language=”bash”]
$ conda list
[/sourcecode]

3. Upgrading Anaconda to newer version:

[sourcecode language=”bash”]
## – Windows:
conda update –prefix ‘C:\Program Files\Anaconda3’ anaconda
## – Linux:
$ conda update anaconda
[/sourcecode]

Additional Resources

* Don’t forget to check out Microsoft Data Amp Technical Sessions at: http://tinyurl.com/lmuquxu
* Check What’s new about SQL Server 2017? https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017
* Getting started in SQL Server on Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-get-started-tutorial
* Download Anaconda: https://www.continuum.io/downloads

Drawback PowerShell Open Source Alpha18 on Bash on Windows 10

Posted on by admin

As we all know that PowerShell Open Source is a “work-in-progess” as it still in the alpha stage. So, previous version Alpha17 was a breakthru as it finally clear some bug in the Bash console. But, unfortunately it fell back with a different bug at the console level.

This is on the latest Windows 10 Insider build 16170.rs_prerelease.170331-1532.

It seem that when you’re typing to skips to the next line. And, sometimes I’ve seen the scroll up failing to display previous information.

The good thing is, everything work when using the Ubuntu Desktop under Windows X-Server. Also, the progress-bar issue got cleared and the Update-Help works.

Keep in mind, to use the “Update-Help -force“, you need to have admin privileges. So, use the following command:

sudo powershell

Patience is the key!

It will soon be fix. Remember to go to Github.com to post any PowerShell Open Source bugs: https://github.com/PowerShell/PowerShell

 

PowerShell, SQL Server, and Linux Oh My!

Posted on by admin

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: http://www.fladotnet.com/codecamp/Agenda.aspx

I will be presenting the following 3 sessions:

1. 09:50am – 11:00am – Working 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: http://www.fladotnet.com/codecamp/

IDERA Geek Synch Webinar – Wednesday February 22nd, 2017

Posted on by admin

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: https://www.idera.com/events/geeksync

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

Posted on by admin

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: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-february-2017-tickets-31689059831

Windows 10 Bash and PowerShell Redux

Posted on by admin

In my previous blog “http://www.maxtblog.com/2016/10/running-powershell-in-windows-10-bash-workaround/“, I was able to find a workaround to how work with PowerShell Open Source in Windows Bash subsystem.  But now, I found a better way using the Ubuntu Desktop component.

It’s a known fact, if you install PowerShell Open Source in Windows 10 Bash subsystem, that it won’t work correctly. As soon as start typing $PSVersionTable and press enter, the cursor goes to the top of the screen. And, you keep typing and it gets very ugly.

Now, what if I tell you, I found the way to run PowerShell Open Source without any of these issues. Just like running it like it was installed in a Linux environment. No issues with the cursor going crazy and able to page up and down.

The following are the steps required to run PowerShell Open Source.

Windows 10 Requirements

You *MUST* use the following Windows X Server application. Just proceed to Download and install VcXsrv Windows X Server:
https://sourceforge.net/projects/vcxsrv/?source=typ_redirect

Also, proceed to Download (to be install later) PowerShell Open Source. Go to the following Github link, and pick the Ubuntu 16.04 version: for Ubuntu 16.04:
https://github.com/PowerShell/PowerShell/releases/tag/v6.0.0-alpha.14

For best results, make sure to be on Windows 10 Insider Build 15007.rs_prerelease.170107-1846.

If previously installed, reset your Windows 10 Bash Subsystem installation, by executing to following commands on either DOS or PowerShell prompt:
1. Uninstall command: lxrun /uninstall /full
2. Clean Installation: lxrun /install

*Note: I’m assuming you’ve been recently checking out Bash in Windows 10. If not, go to “Control Panel | Programs and features” then go to “Turn Windows features on or off” and enable “Windows Subsystem for Linux (Beta)“. By the way, you must have “Developer mode” enabled under “Settings | Update and Security | For Developers” section.

Then, on the Windows menu, look for the “Bash on Ubuntu on Windows” icon and open the console. Make sure Bash is installed and working in Windows 10.

Bash Subsystem Requirements

Now, we need to start installing some additional components in our Bash environment.  Please notice I can use PowerShell console and open “Bash“.  Also, while installing these Linux packages, make sure to open any console “as Administrator“.

Then, in Bash prompt, use the Super User privilege command “sudo su” before installing these packages.

sudo su

I recommend to use the following two command for the purpose of getting both available updates and upgrades to Bash installed packages. You could use these command on a regular basis to keep you subsystem up-to-date.

sudo apt update

sudo apt upgrade

The following commands will install the Ubuntu Desktop and takes about 1hr to complete. Patience is a virtue!

sudo apt install ubuntu-desktop

In some cases, at the end of the installation you will either see no errors, or it will end with the following message: “E: Sub-process /usr/bin/dpkg returned an error code (1)“.

Now, the next package installation install the desktop setting manager application you will need to use later;

sudo apt install compizconfig-settings-manager

So after all is done, use the “exit” command to get out of the Super User session and get back to your user session.

exit

Installing PowerShell

Now, we are ready to install PowerShell Open Source. Remember, if you follow above Windows 10 Recommendation section, you have already downloaded PowerShell. This file will be located in your “..Username\Downloads” folder.  From the Bash prompt, you’ll probably be seen “username@SystemName:/mnt/c/Users/username$” when you open the console as a regular user (non-Admin privilege).

The following commands change folder location to where the PowerShell installation package was saved and the execute the package.

cd Downloads
sudo dpkg -i powershell_6.0.0-alpha.14-1ubuntu1.16.04.1_amd64.deb

This package installation should end without any errors. Don’t open PowerShell yet!

The Ubuntu Desktop

First, in Windows 10, we need to start our “VcXsrv Windows X Server” application by clicking the Windows X Server “Launch” icon. Pay close attention to the field labeled “Display number” should have the value ‘0‘ and pick the “One large window“. Then clicking “Next” to accept all default values.

This will open a blank Windows X Server display.

Now, open “Bash on Ubuntu on Windows” prompt, and at this point there’s no need to open “as Administrator”. Type the following commands to start the “Ubuntu Desktop” in Windows 10:

export DISPLAY=localhost:0

This command will allow the any gui application to interact with our Windows X Server service.

Then, run the command ‘ccsm‘ to configure the Ubuntu Desktop settings: (This is a one time setup)

ccsm

Click “OK” to continue making settings changes following the images below.

After updating the settings, click “Close”

To proceed to start the Ubuntu Desktop, just execute the following command:

compiz

You will notice there are some applications available to use.

Let’s concentrate of the ‘File’, ‘Text Editor’, and ‘Terminal’. You can manually execute some gui applications from the bash command prompt such as:
1. For File Browsing execute: nautilus or ‘sudo nautilus’ with elevaded security.
2. For Text Editor execute: gedit or ‘sudo gedit’ with elevated security.
3. For Internet browsing: firefox

Now, we are ready for PowerShell.

PowerShell in Windows 10 Bash

At the Ubuntu Desktop just right-click and pick “Open Terminal”. Then, type “powershell” and press enter.

Then execute the following powershell commands:

$psversiontable
dir

As you can see we, now PowerShell is working in Windows 10 Bash thank to Ubuntu Desktop. But, keep in mind, Ubuntu Desktop is not supported in Windows 10 Bash subsystem. At least, you can give it a test drive.

By the way, you can use your favorite PowerShell Editor

BONUS Section

Important Path to Remember, in case you’ll find where things are in Windows 10 Bash Subsystem.

Windows 10 BASH folders:
C:\Users\mtrinidad\AppData\Local\lxss
C:\Users\mtrinidad\AppData\Local\lxss\home\”Linux_UserName”
C:\Users\mtrinidad\AppData\Local\lxss\rootfs\usr\local\bin
C:\Users\mtrinidad\AppData\Local\lxss\home\”Linux_Username”\PowerShell

Bash file path to Windows folders:
/mnt/c
/mnt/c/Users/”Windows_UserName”/Downloads
/home/”Linux_UserName

Where PowerShell Open Source folder are located in Bash Subsystem:
/usr/local/bin/PowerShell

Additional Information:
Information about installing Ubuntu Desktop in Windows Bash:
— See link for info: https://github.com/Microsoft/BashOnWindows/issues/637

 

PowerShell Open Source – Windows PSRemoting to Linux with OpenSSH

Posted on by admin

I have to admit that I was waiting for this for some time. Finally, PowerShell 6.0.0-Alpha_13 can do remoting between Windows to Linux. This is Awesome!

microsoft-loves-linux

I’m going to give you the top-to-bottom scope in order to avoid going back-and-forth between link. So, it’s all here and including tips and print-screens.

Instructions for Windows OpenSSH installation

[sourcecode language=”dos”]
cd ‘C:\Program Files\OpenSSH’
[/sourcecode]

  • Install sshd and ssh-agent services by using the following command line:

[sourcecode language=”powershell”]
powershell -executionpolicy bypass -file install-sshd.ps1
[/sourcecode]

  • Setup SSH host keys (this will generate all the ‘host’ keys that sshd expects when its starts).

[sourcecode language=”powershell”]
.\ssh-keygen.exe -A
[/sourcecode]

winlinuxremoting_07_2016-12-02_11-23-28

  • Open Firewall (Windows PowerShell 5.x) Server: (This command works in Windows 10)

[sourcecode language=”powershell”]
New-NetFirewallRule -Protocol TCP -LocalPort 22 -Direction Inbound -Action Allow -DisplayName SSH
[/sourcecode]

winlinuxremoting_08_2016-12-02_11-23-28

  • Or for a Workstation:

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

winlinuxremoting_10_2016-12-02_11-23-28

  • Then, to set sshd in auto-start mode and open up firewall:

[sourcecode language=”powershell”]
Set-Service sshd -StartupType Automatic
Set-Service ssh-agent -StartupType Automatic
[/sourcecode]

winlinuxremoting_06_2016-12-02_11-23-28

  • Edit the sshd_config file at the location where you installed Win32 Open SSH and make sure to enabled the following lines:
    Port 22
    PasswordAuthentication yes
    RSAAuthentication yes
    PubkeyAuthentication yes

winlinuxremoting_02_2016-12-02_11-23-28

Also, add the following line to the Subsystem section:
Subsystem powershell C:/Program Files/PowerShell/6.0.0.13/powershell.exe -sshs -NoLogo -NoProfile

Finally, you must add the following line in the system path: “C:\Program Files\OpenSSH”

winlinuxremoting_09_2016-12-02_11-23-28

Failure to add this path line will cause the following error because it can’t execute sshd.exe:

winlinuxremoting_03_2016-12-02_11-23-28

If there’s a need to restart Windows SSHD services, just use the following PowerShell commands:

[sourcecode language=”powershell”]
Stop-Service sshd
Start-Service sshd
[/sourcecode]

Instructions for Ubuntu SSH installation:

  • Download using Ubuntu apt repository using sudo:

[sourcecode language=”powershell”]
$ sudo apt install openssh-client
$ sudo apt install openssh-server
[/sourcecode]

  • Make changed to Ubuntu sshd_config file to enabled some lines below:

[sourcecode language=”powershell”]
$ sudo gedit /etc/ssh/sshd_config
[/sourcecode]

  • Again, make sure to enabled the following lines:
    PasswordAuthentication yes
    RSAAuthentication yes
    PubkeyAuthentication yes
  • Add the following line to the Subsystem section:
    Subsystem powershell powershell -sshs -NoLogo -NoProfile

When done, in Ubuntu, just restart sshd service :

[sourcecode language=”bash”]
sudo service sshd restart
[/sourcecode]

If you want to verify Ubuntu SSH is working, just type the following command:

[sourcecode language=”bash”]
ps -A | grep sshd
[/sourcecode]

This should display a line showing sshd. If is blank, then is not on.

Connecting Linux to Windows

This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.

[sourcecode language=”powershell”]
Enter-PSSession -Hostname earth2 -Username max_t
[/sourcecode]

winlinuxremoting_11_2016-12-02_11-23-28

Connecting Windows to Linux

Again, I’ve seen the same behavior. It will fail the first time and the it will work.

[sourcecode language=”powershell”]
Enter-PSSession -Hostname orion -Username maxt
[/sourcecode]

winlinuxremoting_05_2016-12-02_11-23-28

What’s next?

After all have been tested, you can now connect from Windows to a Linux system. Then, you can execute your Linux commands from the Linux PowerShell session at your Windows Desktop. Is that amazing!!

Keeping things Simple!

Additional tips

This tips may not hurt to do:
1. winrm quickconfig
2. Run the ./InstallPsRemoting.ps1

winlinuxremoting_04_2016-12-02_11-23-28

Also, if you don’t have a domain set up, then set a workgroup environment, and adding the IP-Addresses, computer names for either Windows and Linux host file:
In Windows the *hosts file can be found under “C:\Windows\System32\Drivers\etc“, and in Linux Ubuntu can be found under “/etc/hosts“.

winlinuxremoting_12_2016-12-02_11-23-28

The editors need to be open “Run as Administrator” in order to make changes to this file. In Windows, open Powershell “Run as Administrator” the execute notepad:

[sourcecode language=”powershell”]
notepad C:\Windows\System32\Drivers\etc\hosts
[/sourcecode]

In Linux, use the sudo then the editor of choice;

[sourcecode language=”bash”]
sudo gedit /etc/hosts
[/sourcecode]

I’m hoping this information will get you all going in doing PowerShell Remoting between Windows and Linux.

Additional Resource Information

OpenSSH Instructions at:
https://github.com/PowerShell/Win32-OpenSSH/wiki/Install-Win32-OpenSSH
https://github.com/PowerShell/PowerShell/blob/309bf603f9aff9009dce7e725d42b2d4f99f58c9/demos/SSHRemoting/README.md

For Ubuntu questions:
https://help.ubuntu.com/community

Running PowerShell In Windows 10 BASH (workaround)

Posted on by admin

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!

poshbash_01_2016-10-02

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

poshbash_00b_2016-10-02

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: https://github.com/PowerShell/PowerShell/releases/tag/v6.0.0-alpha.10

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.

poshbash_00c_2016-10-02

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

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

poshbash_00d_2016-10-02

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: https://sourceforge.net/projects/vcxsrv/
After installation, double-click on the XLaunch icon and select how-to display the program.
poshbash_00e_2016-10-02
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
poshbash_00f_2016-10-02
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.
poshbash_00h_2016-10-02
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

poshbash_00i_2016-10-02

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.
http://beforewisdom.com/blog/tech/xterm-with-a-scrollbar/

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

poshbash_00j_2016-10-02

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.

poshbash_00k_2016-10-02

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

poshbash_00l_2016-10-02

Keep pressing enter to bypass the screen information prompt.

poshbash_00m_2016-10-02

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.

poshbash_00n_2016-10-02

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

poshbash_00o_2016-10-02

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.

poshbash_00p_2016-10-02

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
http://beforewisdom.com/blog/tech/xterm-with-a-scrollbar/

** screen
http://www.saltycrane.com/blog/2008/01/how-to-scroll-in-gnu-screen/
http://neophob.com/2007/04/gnu-screen-cheat-sheet/

** Finding Linux version:
http://www.lostsaloon.com/technology/how-to-check-and-find-your-linux-os-version/