Category: PowerShell

PowerShell & Windows Admin Blog Archives, by Peter Whyte (SQL Database Administrator). Includes a lot of WSL posts & more…

  • Create New Files and Folders in PowerShell

    Create New Files and Folders in PowerShell

    The New-Item cmdlet will create new files or folders in PowerShell. Amend the -ItemType parameter to File or Directory and include a Name for the file or folder.

    This blog post will walk through the process of creating a new file and a new folder using PowerShell. I add an extra tip at the end on adding text to a file with PowerShell too.

    For more examples and up-to-date information on creating files with New-Item, we can refer to Microsoft Documentation. They have PowerShell New-Item command examples for creating PowerShell profiles and symbolic links.

    As described, the following is contained within this demo:
    # Create New Folder in PowerShell
    # Create New File in PowerShell
    # Add Text to File PowerShell

    Create Folder PowerShell

    To create a new folder in PowerShell, run New-Item -ItemType Directory -Name dirName, amending the name as desired.

    This will create a folder in the directory you are currently navigated to within the PowerShell Terminal. So for the example below, the new folder is being created in c:\users\pete\

    # PowerShell create new folder
    New-Item -ItemType Directory -Name Test_Stuff
    Create New Folder PowerShell

    Create File PowerShell

    To make a new file in PowerShell, run New-Item -ItemType File -Name testFile amending the name as desired. This creates a file within your current working terminal directory.

    This is the same command we ran above to create a folder, but this time changing the ItemType parameter.

    # PowerShell new file
    New-Item -ItemType File -Name Test_File.txt
    Create File PowerShell

    Add Text to File PowerShell

    Now for a bonus PowerShell tip that you did not search or ask for. How to add text to a file in PowerShell.

    To add text to a file in PowerShell we can use the Add-Content cmdlet, and we can verify with Get-Content to check the text in a file.

    # PowerShell add text to file
    Add-Content .\Test_File.txt -Value 'Hi hi!... just a wee message here.'
    
    # PowerShell show file content
    Get-Content .\Test_File.txt
    PowerShell Add Data to File
  • Install oh-my-posh On Windows

    Install oh-my-posh On Windows

    Oh-my-posh is a theme engine for PowerShell that’s separate from your default PowerShell console. It’s powered by posh-git and has status indications for git, failed, and admin commands.

    Oh-my-posh requires and runs in Windows Terminal which was my previous blog post.

    This post is just a quick guide on installing oh-my-posh and showing off my current favourite theme.


    Install oh-my-posh

    Installing using Chocolatey here and you’ll also need to install posh-git too. This can also be done with the Import-Module cmdlet, an example of which is included in the docs.

    choco install oh-my-posh

    Once installed pick a theme – see this link in the oh-my-posh documentation for screenshot examples for each of the themes

    Set-Theme Zash

    Looking good now…


  • How to Install Windows Terminal

    How to Install Windows Terminal

    Windows Terminal is a more modern command-line tool that has many additional features. A major benefit of WT is the multiple tabs feature.

    Windows Terminal multiple tabs

    Other benefits of Windows Terminal include Unicode / UTF-8 character support, GPU-accelerated text rendering, and the ability to create your own themes & shortcuts.

    This blog post contains a demo on how to install Windows Terminal, with an added tip on how to open PowerShell as Administrator using Windows Terminal.

    # Install Windows Terminal (WT)
    # Run PowerShell WT Session as Administrator

    Install Windows Terminal (WT)

    I’m installing using Chocolatey, the Windows package manager. Note, a reboot is required post-install.

    # install windows terminal chocolatey
    choco install microsoft-windows-terminal

    Search and open Windows Terminal.

    Click the drop-down to open new tabs and you’re good to go.

    Run PowerShell WT Session as Administrator

    Run the command below and a new Windows Terminal will open.

    # Run PowerShell as administrator with Windows Terminal
    Start-Process -Verb RunAs cmd.exe '/c start wt.exe -p "Windows PowerShell"'

    And here’s a typical command that would require admin elevation.


  • Install posh-git on Windows

    Install posh-git on Windows

    Posh-git is a PowerShell module that integrates with Git to show git status information on the command line, and it comes with tab completion for common commands.

    This post is a quick guide on installing this using the Chocolatey package manager.

    Install Posh-Git

    With Chocolately the posh-git install command is:

    choco install poshgit

    How Posh-Git Looks

    After install navigate into a local git repo. It’s showing me to be on the master branch.

    Below is a quick demo of it working – I’m creating a new branch, creating a new text file and staging the file.


  • Install Chocolatey on Windows

    Install Chocolatey on Windows

    This post is a guide on installing the Chocolatey package manager on Windows by running a PowerShell command.

    Chocolatey allows you to manage software packages on your system with simple ‘choco’ commands. In this post after I install Chocolatey I’m installing a GUI to view all installed packages.

    # Install Chocolatey using PowerShell
    # Install Chocolatey GUI


    Install Chocolatey using PowerShell

    Run the following script within PowerShell (as Administrator):

    Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

    Check the version of choco to verify the install, or go ahead and install a package (e.g. choco install git).


    Install Chocolatey GUI

    Installing a package is done with ‘choco install <packagename>’. There’s about 8,000 maintained packages at the time of writing this. Some of the most popular packages include Chrome, Firefox, Java, Flash, Notepad++, 7zip, Git & Python.

    choco install chocolateygui

    Now open the Chocolatey GUI application. There will be a shortcut for this in your Start Menu too.

    You can uninstall/reinstall packages from the GUI.


  • Enable Windows Subsystem for Linux (with Ubuntu 18.04)

    Enable Windows Subsystem for Linux (with Ubuntu 18.04)

    WSL allows Windows users to develop and run applications within a GNU/Linux environment. Previous to this feature release we needed VM’s or a dual boot setup.

    For more information, as always have a look at the Microsoft Docs. There’s a huge amount of tutorials and documentation on this area.

    This post is a simple guide and includes:
    # Enabling Windows Subsystem for Linux (WSL)
    # Downloading a Linux Distro
    # Installing a Linux Distro


    Enabling Windows Subsystem for Linux (WSL)

    Open PowerShell as Administrator and run the following to enable WSL:

    Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
    PowerShell Enable WSL

    You’ll then be prompted to restart your local computer.

    PowerShell Enable WSL Restart Prompt

    Downloading a Linux Distro

    You can find all the Linux Distributions on the Microsoft Store and download from there. This may be restricted by Group Policy if you’re on a Domain, so you can download manually from this list on the documentation page.

    For this guide I’m downloading using Invoke-WebRequest:

    cd c:\your_download_folder
    Invoke-WebRequest -Uri https://aka.ms/wsl-ubuntu-1804 -OutFile Ubuntu1804.appx -UseBasicParsing
    PowerShell Invoke-WebRequest Ubuntu 18.04

    Installing a Linux Distro

    We have to rename the .appx file I downloaded above to .zip, decompress and then run the .exe file to install Ubuntu.

    Rename-Item .\ubuntu1804.appx .\ubuntu1804.zip
    Expand-Archive .\ubuntu1804.zip .\ubuntu1804
    .\ubuntu1804\ubuntu1804.exe
    WSL Ubuntu Install

    The terminal should scope into the Linux instance. Type ‘exit’ to get back to PowerShell.


  • Remove Quotes from a CSV File with PowerShell

    Remove Quotes from a CSV File with PowerShell

    This is a post on how to remove quotes from a CSV file using PowerShell.

    If your dealing with a large CSV file, running the Import-CSV cmdlet below will consume a lot of system resources (RAM mostly). Also, if there’s a set of double quotation marks within the CSV file there may be some issues with the ordering of columns.


    Create Test File

    I’m creating the TestData variable and piping that out into a file using Out-File here. I can then check what’s in the file using Get-Content.

    # Create variable with CSV data.
    $TestData = @'
    "first_name", "surname", "dob"
    "pete", "whyte", "01/01/2000"
    "joe", "broke", "05/12/2001"
    '@
    
    # Output data as CSV file in working directory.
    $TestData | Out-File .\TestData.csv
    
    # Show contents of new CSV file.
    Get-Content .\TestData.csv

    Remove Quotess

    Amend import and output file names and then Get-Content to check the file after since it’s a tiny test file.

    # Import CSV file and output as new file with quotes removed.
    Import-Csv .\TestData.csv | ConvertTo-CSV -NoTypeInformation | % { $_ -Replace '"', ""} | Out-File C:\CSV_Tests\TestData_NoQuotes.csv -fo -en ascii

    As mentioned above in this post performing this on a large data file might not work out well. Monitor the performance of your machine and give the data a thorough visual check after.

    If you encounter issues I’d probably ask why this is needed, as there are many options for handling such delimitated files in the more recent versions database tooling.


  • “.ps1 cannot be loaded because running scripts is disabled on this system” PowerShell Error

    “.ps1 cannot be loaded because running scripts is disabled on this system” PowerShell Error

    When attempting to run a PowerShell script you may receive the following error:

    cdk.ps1 cannot be loaded because running scripts is disabled on this system.
    For more information, see about_Execution_Policies at https://go.microsoft.com/fwlink/?LinkID=135170

    cannot be loaded because running scripts is disabled on this system

    This post is here to help you resolve this issue. The reason this is happening is due to the default ExecutionPolicy not allowing this action. We need to change it from Undefined to RemoteSigned or Unrestricted.

    Resolution: Set Execution Policy to RemoteSigned

    To resolve this “script cannot be loaded because running scripts is disabled on this system” error message:

    1. Open PowerShell as Administrator.

    2. Check current Execution Policies: Run Get-ExecutionPolicy

    Get-ExecutionPolicy -List

    3. Amend Local Execution Policy: Run Set-ExecutionPolicy.

    Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy RemoteSigned
    Set-ExecutionPolicy PowerShell

    All should work fine after the above.

    Last updated: 29/09/2022

  • Testing Connectivity to Remote Server Ports with PowerShell

    Testing Connectivity to Remote Server Ports with PowerShell

    All admins need a tool to test connectivity to remote servers on TCP ports. In Windows, this is commonly done using PuTTy or PowerShell.

    This post is a note on my favourite way of testing remote TCP connections in Windows, which is using PowerShell:
    # Check a Port is Open (Pre Win08/Svr2012)
    # Check a Port is Open (Test-NetConnection)
    # Troubleshooting Remote Ports

    Important ports to remember in the life of a DBA may include:
    # SQL Server (1433)
    # RedShift (5439)
    # PostgreSQL (5432)
    # MySQL (3306)
    # Oracle (1521)

    Check a Port is Open (Pre Win08/Svr2012)

    This is for when you’re on a legacy server running an old version of PowerShell. I managed to spawn a Windows Server 2008 R2 box from the AWS Marketplace for this demo.

    # Check a port is open (pre Win08/Svr2012)
    $Ipaddress= Read-Host "Enter the IP address:"
    $Port= Read-host "Enter the port number to access:"
    $t = New-Object Net.Sockets.TcpClient
    $t.Connect($Ipaddress,$Port)
    if($t.Connected)
        {"Port $Port is operational."}
    else {"Port $Port is closed."}

    Enter IP address and port number when prompted.  

    Test-NetConnection PowerShell

    Below is an example of no connectivity, it’s failing to connect on port 1433. The server I’m testing does not have SQL Server installed, so there was nothing listening on that port.

    TCP Port Test PowerShell

    Check a Port is Open (Test-NetConnection)

    I’ve used Test-NetConnection frequently for years. It’s built-in to recent Editions of Window Server and is easier to use. We can also use ‘tnc’ as displayed in the example code below.

    # Test remote port is open
    Test-NetConnection -ComputerName lab-sql1.whyte.net -Port 1433
    
    # Same as above command using alternative syntax/ip
    tnc 172.31.18.100 -port 1433
    Test-NetConnection

    We can see from the screenshot above this test passed as TcpTestSucceeded came back true.

    Note:
    The traffic between you and another server may be flowing through various components that can include; local/internal/external firewalls, NAT Gateways, Security Groups/NACLs, load balancers & more.
    Diagnosing connectivity issues can be very complex. This is a simple test and might not be reflected in certain network traffic logs – if you’re troubleshooting maybe run your second port test with Putty.

    Troubleshooting Remote Ports

    If connectivity is failing, a few things to check may include:
    # There has to be something ‘listening’ on the remote server port.
    # Network (Inc. DNS) configurations & Security Groups.
    # Firewalls (at the Infrastructure level or local host config).

  • Forcing Encryption in SQL Server

    Forcing Encryption in SQL Server

    If you’re ever planning to allow external connections to a SQL Server, one of the first things to ensure is that the data is encrypted at rest and in transit.

    SQL Server has an option where we can force all connections on a SQL Server to be encrypted, which ensures us that we’re achieving encryption for our SQL connectivity.

    This post follows Microsoft Docs – Enable Encrypted Connections to the Database engine. It’s quite a complex piece so a full read of this would be necessary before planning this change.

    # Create Certificate (PowerShell)
    # Import PK to Trusted Root Certificates
    # Import Certificate into SQL Server
    # Force Encryption in SQL Server
    # Verify SQL Server Connectivity is Encrypted


    Create Certificate (PowerShell)

    The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer.

    The above is a little note in amongst a bunch of pertinent info, found here; Microsoft Docs – Enable Encrypted Connections – Remarks

    I’m using a New-SelfSignedCertificate for this tutorial and creating it with PowerShell.

    Before running the following PowerShell script, amend .pfk output file location & DNS Names.

    New-SelfSignedCertificate -DnsName lab-sql1.whyte.net -CertStoreLocation cert:\LocalMachine\My -FriendlyName lab-sql1-cert -KeySpec KeyExchange -NotAfter (get-date).AddYears(99)
    $thumbprint = $(Get-ChildItem Cert:\LocalMachine\My).thumbprint
    $Pwd = ConvertTo-SecureString -String "Str0ngePassword1!" -Force -AsPlainText
    Export-PfxCertificate -Cert "Cert:\LocalMachine\My\$thumbprint" -FilePath "C:\temp_certificates\lab-sql1pk.pfx" -Password $Pwd -Force
    PowerShell New-SelfSignedCertificate
    Certificate File Windows Explorer

    Import PK to Trusted Root Certificates

    Open MMC and add the Certificates (Local Computer) Snap-in.

    MMC Local Computer
    MMC Console

    Expand Trusted Root Certification Authorities, right-click & select All Tasks > Import…

    Trusted Root Certificate Import

    Navigate to the .pfx file.

    Import pfx File

    Enter a password & continue.

    Private Key Password

    Finish up the wizard.

    Certificate Import Wizard
    Certificate Import Wizard

    Have a look at the new certificate within MMC.

    MMC

    Import Certificate into SQL Server

    Open SQL Server Configuration Manager, then right-click Protocols for MSSQLSERVER and select Properties.

    SQL Server Protocols

    Open the Certificate tab and you should be able to view & select the new certificate from the drop-down menu.

    Click to Apply & Ok out of the window – I’m doing the Forcing of Encryption separate (below).

    Protocols for MSSQLSERVER

    You’ll get this prompt.

    Any changes made will be saved, however, they will not take effect until the service is stopped and restarted

    Restart the SQL Services from the configuration manager. If your services don’t start back up again, then ensure the service accounts have the appropriate permissions. For this demo, my AD Service Accounts are members of the local Administrators group.  

    SQL Server Configuration Manager

    Force Encryption in SQL Server

    Right click Protocols for MSSQLSERVER, select Properties and within the Flags tab enable the Force Encryption option.

    SQL Server Force Encryption

    Restart the SQL Services.

    SQL Server Service Restart

    Verify SQL Server Connectivity is Encrypted

    Open a local and/or remote query session. You may have to ensure the Encrypt connection & Trust server certificate options are checked.

    SSMS Connection Properties

    Now query sys.dm_exec_connections to check the encryption_option of all SQL connections.

    SELECT * FROM sys.dm_exec_connections
    sys.dm_exec_connections