Monday, August 12, 2019

Connect to SQL with Powershell

Connect to SQL Server with Powershell used to feed a powershell dashboard

Script post colors

# Check if SqlServer module is installed and install it
if (!(Get-InstalledModule sqlServer)) {Install-Module -Name SqlServer}

# Param input 1 for facilites and 2 for providers, option 3 to set password
[string] $Server= "SQLServer"
[string] $Database = "myDatabase"
[string] $UserSqlQuery= @"
select * from my table

[string] $UserSqlQuery2= @"
select * from my table2

$outfile = "C:\support\scripts\dashboard\data\my-data1.xml"
if ($dataType -eq 3){ # Save password creds to file, must be set per machine
$c | Export-CliXml -Path 'C:\support\scripts\dashboard\data\cred.xml'

if ($dataType -eq 2)
$outfile = "C:\support\scripts\dashboard\data\my-data2.xml"

$resultsDataTable = New-Object System.Data.DataTable
$credential = Import-CliXml -Path C:\support\scripts\dashboard\data\cred.xml

#Run SQL Command
function run-SqlQuery ($sqlServer, $sqlDatabase, $sqlQuery, $dbCreds ) {
$dbUser = $dbCreds.UserName#.Replace("\","")
$dbPass = $dbCreds.GetNetworkCredential().password
$dbDataSet=invoke-sqlcmd -query $sqlQuery -database $sqlDatabase -serverinstance $sqlServer -username $dbUser -password $dbPass
return $dbDataSet

$resultsDatatable = run-sqlQuery $Server $Database $UserSqlQuery $credential

$resultsDatatable |Export-Clixml -Path $outfile

Thursday, August 8, 2019

Azure VPN to WebApp

The Azure Website Application (WebApp) to VPN is used to allow a publicly facing WebApp to connect securely to back-end on primes local resources without exposing them to the public internet.
The configuration options for the WebApp to Virtual Network (VNet) to Virtual Private Network (VPN) are continuously evolving as Microsoft improves and adds new functionality.  In 2014 Azure introduced resource management as a way to manage, automate, and scale objects. This is now in general release and is called Azure Resource Management or ARM

As of March 2016 Azure has two web portals.  The Classic portal using the “manage” url and the ARM portal using the “portal” url. There is also an extensive collection of PowerShell scripting tools to manage and automate Azure. These PowerShell tools also have some different capabilities between the classic and ARM portal. At some point the classic portal will go away.

Microsoft has documented all the Azure functionality of the classic and ARM portals. Also Microsoft has some free structured training on the Microsoft Virtual Academy (MVA) site.
Links and Resources:

Classic Portal url:
ARM Portal url:
Microsoft Virtual Academy:

The below diagram shows the main parts of WebApp to VNet to VPN configuration. There are two VPN technologies used; From the Azure WebApp a Point to Site (P2S) VPN is used to connect to the VNet. From the Azure VNet to the local on premise network a Site to Site (S2S) VPN is used.

<<Diagram Here>>
Azure virtual network or VNet is a cloud based networking technology with its foundation in Ethernet and the internet protocol version 4 (IPv4).  IPv4 can be represented in the standard doted notation ie with a gateway of and the subnet mask of To help with routing and to simplify the setup Azure VNet uses the Classless Inter-Domain Routing (CIDR) format to define the networks and address spaces. Azure VNets can only use the IPv4 private network address spaces;,, or

<<Diagram Here>>
With the Azure VNet you can fully control the IP address blocks, DNS settings, security policies, and route tables.
A VNet can have multiple address spaces and multiple subnets. Each VNet can have only one gateway.
Address spaces and Subnets are defined with CIDR notation. defines a “10.21” address space. With this space multiple subnets can be defined.
Subnet 1:
Subnet 2:

There are three ways to connect to the Azure Cloud; Point to Site VPN, Site to Site VPN and ExpressRoute.  ExpressRoute is not a VPN but another method to connect a local corporate enterprise or Colo network to the Azure cloud. Azure limits the VPN to around 100Mbps where ExpressRoute is a high speed network connection exceeding 1Gbps. ExpressRoute will not be covered in this document.

Azure VPN Gateway FAQ:

Point to Site (P2S) is a client based VPN technology where each client connects to the network with a shared certificate. This is how the WebApp connects to the VNet and the (S2S) VPNs.
Site to Site (S2S) is a router based VPN technology where networks are joined via routers using IP security (IPsec) (IKEv1/IKEv2) and pre-shared key security.  
What is IKE or Internet Key Exchange?
There are two types of S2S VPN technologies: Policy Based and Route Based
Route Based (IKeV2) VPN more secure dynamic routing technology using IKEv2
Supported with WebApp to VPN Integration
Policy based (IKeV1) VPN is an older Static Routing technology using IKEv1
(Not Supported with WebApp to VNet integration)
When configuring S2S VPN refer to the latest compatible/validated devices at the Azure website.

Microsoft PowerShell is a powerful scripting language that extends the task automation and configuration management capabilities of the Microsoft windows desktop and server operating systems. PowerShell is built on top of the Microsoft .net programing framework using the Windows Component Object Model (COM) binary interface and the Windows Management Instrumentation (WMI) interface objects are used to access the computer’s hardware and the interworking of the windows operating system. PowerShell uses cmdlets (pronounced command-lets). These are pre-built tools used in the PowerShell scripting language to perform many tasks and functions. These cmdlets exposed most common .net functions and tools. When a specific function is not available the user can directly access the complete .net framework, WMI, and COM objects.

Microsoft has extended PowerShell into Azure with an ever growing set of command line scripting tools to manage and automate the Azure cloud service.

These tools are not part of PowerShell and must be downloaded from Microsoft. Microsoft is always adding new functionality so update when needed. But also be aware some updates can break any scripts you have built, so test the new updates on a sandbox system before updating your automation server.

Azure PowerShell command-line tools download and documentation:

After you have downloaded the tools you can access your Azure environment.  There are two login process depending on if you are using the classic or ARM portal tools.

1.       Open your PowerShell terminal as administrator.
2.       Login to Azure with the classic or ARM method. This will pop up an Azure login window.
3.       Change Subscription if needed.

PowerShell Prompt: PS C:\>

PowerShell Classic Portal login
Select-AzureSubscription "MySubscription"

PowerShell ARM Portal login
Set-AzureRmContext -SubscriptionId 'xxxx-xxxx-xxxx-xxxx'

The IT System Diagram

The value of the system diagram
      system flow
      information reference

Convert origin_timestamp to PST in Excell

Using Microsoft Exchange Online Message Trace with downloaded results are in UTC in a CSV file.




To convert in Excell  from UTC to PST is - 7 hours


Wednesday, August 7, 2019

Internet free speech

You have the right to post whatever you want and accept the responsibility of how others interpret it. 

Lawrence Billinghurst
Mon 12/1/2014 8:28 PM, PST

Chat GPT Says:

This statement is not entirely accurate. While it is true that individuals have the right to express themselves freely, this right is not absolute and is subject to certain limitations. For example, individuals have a responsibility to not engage in speech that is defamatory, threatening, or incites violence. Additionally, individuals may be held accountable for their words if they are found to have engaged in libel, slander, or other forms of speech that are deemed harmful to others.

Furthermore, while individuals have the right to post whatever they want, they do not have the right to control how others interpret their words. Others are free to interpret an individual's words in their own way, and the individual has no control over this. As such, it is important for individuals to be mindful of the potential impact of their words and to accept the responsibility for any harm that may result from their speech.