> Can I use this to run applications as my admin account?
A user with a beard
It is good practice to not log into your work station with an account with admin privileges. In many shops, you will need to open applications that can do administration tasks with another set of account credentials.
Unfortunately, people being people, they will often store their admin account credentials in a less than ideal manner (OneNote, Notepad ++ etc) to make it easier for them, so that when they right click and run as a different user, they can copy and paste the password.
Use the Secret Management module
Again, I decided to use a notebook to show this as it is a fantastic way to share code and results and because it means that anyone can try it out.
The notebook may not render on a mobile device.
Using the notebook, I can quickly store my admin password safely and open and run the applications using the credential
I have to start here. For the longest time, whenever anyone has asked me how I store my credentials for use in my demos and labs I have always referred them to Jaap Brassers t blog post
When people wanted a method of storing credentials that didnt involve files on disk I would suggest Joel Bennett’s t module BetterCredentials which uses the Windows Credential Manager
Sydney t gave a presentation at the European PowerShell Conference which you can watch on Youtube.
Good Bye Import-CliXML
So now I say, it is time to stop using Import-Clixml for storing secrets and use the Microsoft.PowerShell.SecretsManagement module instead for storing your secrets.
Notebooks are as good as blog posts
I love notebooks and to show some people who had asked about storing secrets, I have created some. So, because I am efficient lazy I have embedded them here for you to see. You can find them in my Jupyter Notebook repository
I was talking with my friend Mathias Jessen @IISResetMe on Twitter about notebooks and he said that another great use case was to use them on Stack OverFlow
Now Mathias is an active answerer on Stack OverFlow
and he puts a lot of effort into writing his answers, formatting them, including code and results. Basically exactly the same as a Notebook. However, with a Notebook, you can enable people to run the code as well on their own machines.
Mathias says he will use notebooks to help people when he answers their PowerShell questions on Stack OverFlow. If you are a Stack OverFlow Answerer then you can too.
My last post had a lot of information about the new .NET PowerShell notebooks including installation instructions.
.NET Notebooks are Jupyter Notebooks that use .NET core to enable C#, F# and PowerShell kernels.
Use Cases
One of the main benefits that I see for Jupyter Notebooks for Ops folk is that the results of the query are saved with the notebook. This makes them fantastic for Incident resolution.
If you have an incident at 3am and you know that you will need that information in the wash up meeting the next day instead of copying and pasting results into a OneNote document or a text file, you can simply run the queries in a notebook and save it.
In the meeting, you can simply open the notebook and the results will be available for everyone to see.
Even better, if you have a template notebook for those scenarios and you can then compare them to previous occurrences.
Using Pester
Using Pester to validate that an environment is as you expect it is a good resource for incident resolution, potentially enabling you to quickly establish an area to concentrate on for the issue. However, if you try to run Pester in a .NET Notebook you will receive an error
Describe:
Line |
3 | Describe "Checking Problem ...... by $($ENV:USERDOMAIN) $($ENV:UserName)" {
| ^ The 'Describe' command was found in the module 'Pester', but the module could not be loaded. For more information, run 'Import-Module Pester'.
Fixing it
When you try to Import-Module Pester you get the following error
Get-Command: C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psm1
Line |
94 | $script:SafeCommands['Get-CimInstance'] = Get-Command -Name Get-CimInstance -Module CimCmdlets @safeCommandLookupParameters
| ^ The term 'Get-CimInstance' is not recognized as the name of a
| cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
| verify that the path is correct and try again.
Import-Module: The module to process 'Pester.psm1', listed in field 'ModuleToProcess/RootModule' of module manifest 'C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psd1' was not processed because no valid module was found in any module directory.
Thats odd, why is it failing there? Dongbo Wang from the PowerShell team explains in the issue that I raised
Yes, it was the CimCmdlets module from the system32 module path that got imported (via the WinCompat feature added in PS7). This is because currently the PS kernel don’t ship all the built-in modules along with it … The built-in modules are not published anywhere and are platform specific, it’s hard for an application that host powershell to ship them along. We have the issue PowerShell/PowerShell#11783 to track this work.
The way to resolve this is to Import the CimCmdlets Module from your local PowerShell 7 installation until the issue is resolved
Notebooks – A brilliant way of sharing what you did and the results that you got enabling others to follow along. You can do this with this Notebook. Download it and open it in your Jupyter Lab and you will be able to run it and see all of the errors and the fix on your machine.
— Maria Naggaga #BlacklivesMatter (@LadyNaggaga) February 6, 2020
PowerShell 7 Notebooks 🙂
A notebook experience for PowerShell 7 that sounds amazing. This will enable a true cross-platform PowerShell Notebook experience which is lacking from the Python version as it uses Windows PowerShell on Windows and PowerShell Core on other OS’s
The first thing I asked was – Will this come to Azure Data Studio. I got an immediate response from Sydney Smith PowerShell Program Manager saying it is on the roadmap
Moving this kernel into ADS is on our roadmap! Right now our kernel uses hosted pwsh 7 but we would love to know if you have scenarios that dont work with 7
To be able to run the notebook, you need to install some dependencies. First install the .NET CORE SDK which you can download from https://dotnet.microsoft.com/download This needs admin permissions to install.
You also need a Python installation – You can use Anaconda, which you can download from here https://www.anaconda.com/distribution/ This does not need admin to install
Add Anaconda to Windows Terminal
I have added the Anaconda prompt to Windows Terminal so that I have one entry point for all my CLIs. Open the settings file and add the code below. (It will also give you an icon and background.
{
// Make changes here to the Anaconda.exe profile
"guid": "{0caa0dad-35be-5f56-a7ff-afceeeaa6101}",
"name": "Anaconda",
"commandline": "cmd.exe /K C:\\Users\\mrrob\\Anaconda3\\Scripts\\activate.bat",
"hidden": false,
"backgroundImage": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
"icon": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
"backgroundImageAlignment": "topRight",
"backgroundImageStretchMode": "uniform",
"backgroundImageOpacity": 0.1
}
and it appears in the drop down
With Anaconda installed, check that that the kernel is available on your path. If like me you have Azure Data Studio installed, you will have additional kernels but the important one line here is
Then you can install the .NET kernel in your Anaconda prompt using this command
dotnet interactive jupyter install
Sometimes new things have errors
I had an error when I tried this first time
Could not execute because the specified command or file was not found. Possible reasons for this include: * You misspelled a built-in dotnet command. * You intended to execute a .NET Core program, but dotnet-interactive does not exist. * You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.
This is easily fixed by adding %USERPROFILE%\.dotnet\tools to my path with set PATH=%PATH%;%USERPROFILE%\.dotnet\tools
Running jupyter kernelspec list shows that the .NET kernel is installed for C Sharp, F Sharp and .NET PowerShell
Lets open a Notebook
Now you want to play with it! You can run the lab environment using `jupyter lab`
This opens a browser
You can open existing Azure Data Studio PowerShell notebooks (but not SQL ones)
Sometimes new things have errors Part 2
Unfortunately, I get errors when trying to import Pester which means I can not use my dbachecks notebooks in this blog post. I have raised an issue on the repo here.
Create a New Notebook
But it is easy to create a new Notebook
In the launcher page click the .NET PowerShell button
Which will open a new Notebook in the directory that you launched the lab from. You can then add Code or Markdown as I have described before here.
Then you can add code, markdown and images to create your notebook.
Once you have finished using the notebook lab, you can shut it down in the Anaconda prompt with CTRL + C
Here is a video of running a notebook which anyone can use to create a couple of Docker containers running SQL 2019 and query them with dbatools. You can find the notebook further down this post.
Sharing Notebooks
You can create notebooks to run common tasks. Even better, from the lab you can convert the notebook including the results to a variety of formats to share with other none-technical people. I used this functionality this week to export Azure Data Studio Notebooks to HTML and PDF for a Project manager 🙂
You can find the Export Notebook command in the File menu
Exporting to HTML did not export the images but it does include the results
You can share notebooks via GitHub – Either in a gist like this
This uses Docker to create an interactive Notebook. Create a Github repo like https://github.com/SQLDBAWithABeard/Notebooks (or just clone it) Copy your notebooks into the notebooks folder and push the changes to Github and then go to https://mybinder.org/ and add your URL to the repository.
You can see what it looks like by clicking the button below which Binder creates for you
Unfortunately the kernel only supports Python for the moment but you can see the possibilities 🙂
I showed my silly example PowerShell code to create a PowerShell Notebook that created a PowerShell Notebook to my good friend Nick.
Nick is a fantastic, clever DBA who isn’t active on social media, which is a great shame as if he had time to share some of his fantastic work we would all benefit. He looked at that code and less than an hour later, came back to me with this code and idea which I have replicated here with his permission.
Thanks Nick.
The Use Case
The use case that Nick has is that he is converting some troubleshooting runbooks from their original locations (you know the sort of places – Sharepoint Docs, OneNote Notebooks, Shared Folders, the desktop of the Bastion Host) into a single repository of Azure Data Studio SQL or PowerShell Notebooks.
The idea is to have a single entry point into the troubleshooting steps and for the on-call DBA to create a Notebook from a template for the issue at hand which could be attached to an incident in the incident management solution. I suppose you could call it an Index Notebook.
Work Flow
When the DBA (or another team) opens this Notebook, they can choose the task that they are going to perform and click the link which will
copy the Notebook to the local machine
Rename the Notebook with the username and date
Open it ready for the work.
Once the work has been completed, the DBA can then attach the Notebook to the task or incident that has been created or use it in the Wash-Up/ Post Incident meeting.
This ensures that the original template notebook stays intact and unchanged and it is easy (which is always good when you are called out at 3am!) to create a uniquely named notebook .
Azure DevOps
Nick has placed this code into the deploy step in Azure DevOps which will deploy the template Notebooks from source control into the common folder and then this code will dynamically create the index Notebook each time there is a release.
Whilst the initial use case is incident response, this could easily be adapted for Notebooks used for Common Tasks or Run Books.
Notebooks
There are a number of Notebooks for different issue stored in directories. For this post, I have used the Notebooks from Microsoft that explain SQL 2019 features and troubleshooting which you can find in their GitHub repositories by following this link
The Azure DevOps deploys the Notebooks to a directory which then looks something like this
Some directories of Notebooks in a directory
Create an Index Notebook
Here is the code to create an index Notebook
This creates a Notebook in the root of the folder. It also uses the new -Collapse parameter in New-AdsNoteBookCell that creates the code blocks with the code collapsed so that it looks neater. The index Notebook looks like this in the root of the folder
Three O’Clock in the Morning
It’s 3am and I have been called out. I can open up the Index Notebook, find the set of queries I want to run and click the run button.
A new workbook opens up, named with my name and the time and I can get to work 🙂 I think it’s neat.
Here’s a video
Thanks Nick.
Maybe you can find him at SQL Bits next year. Did you know that SQL Bits 2020 was announced?
The latest update to the ADSNotebook PowerShell module I blogged about here now enables the creation of PowerShell notebooks with PowerShell.
You can install the module with
Install-Module ADSNotebook
or if you have already installed it you can use
Update-Module ADSNotebook
In the latest release, there is an extra parameter for New-AdsWorkBook of -Type which will accept either SQL or PowerShell
Create a PowerShell Notebook with PowerShell Rob
OK!
Here is some code to create a PowerShell Notebook. First we will create some cells using New-AdsWorkBookCell including all the markdown to add images and links. You can find my notebooks which explain how to write the markdown for your notebooks in my GitHub Presentations Repository
$introCelltext = "# Welcome to my Auto Generated PowerShell Notebook
## dbatools
dbatools is an open-source PowerShell Module for administering SQL Servers.
You can read more about dbatools and find the documentation at [dbatools.io](dbatools.io)
"
$SecondCelltext = "### Installation
You can install dbatools from the PowerShell Gallery using `Install-Module dbatools`
"
$thirdcelltext = "Install-Module dbatools"
$fourthCelltext = "### Getting Help
You should always use `Get-Help` to fins out how to use dbatools (and any PowerShell) commands"
$fifthcelltext = "Get-Help Get-DbaDatabase"
$sixthCelltext = "Try a command now. get the name, owner and collation of the user databases on the local instance"
$seventhCellText = "Get-DbaDatabase -SqlInstance localhost -ExcludeSystem | Select Name, Owner, Collation"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
$fourth = New-ADSWorkBookCell -Type Text -Text $fourthCelltext
$fifth = New-ADSWorkBookCell -Type Code -Text $fifthcelltext
$sixth = New-ADSWorkBookCell -Type Text -Text $sixthCelltext
$seventh = New-ADSWorkBookCell -Type Code -Text $seventhCellText
Then we will create a new workbook using those cells
