Author: Nick Gorveatt

Home / Author: Nick Gorveatt

Azure PowerShell

July 31, 2019 | Azure, PowerShell | 1 Comment

I was thinking of creating some PowerShell scripts to take care of some repetitive tasks when using the UI, normal thoughts when your wanting to script something out.

Microsoft has a new Az Module that is replacing the AzureRM Module, I wanted to make sure I was going to be using the newest module so the script commands would be supported longer under the new module.

I’ve written a few snip it scripts using the AzureRM, I wanted to take a simple one and migrate it to Az.

My old script had a basic task of doing a query look up. It logged, it ran the query and saved output. It looked something like this:

###### Old Script
Login-AzureRMaccount
$MyQuery = 'LongQueryHere-blah-blah-blah | where TimeGenerated between(datetime("2019-07-24 17:37:30.999") .. datetime("2019-07-25 17:37:32.999"))'

$MyQueryResponce = Invoke-AzureRmOperationalInsightsQuery -WorkspaceID "0e000e00-0aa0-0ee0-a0aa-0000000000ee" -Query $MyQuery

Write-Output $MyQueryResponce.Results > C:\Folder\MyFileNameHere.txt

Now there are quite a few pages that help you get started on the Microsoft website in their documentation. Started on this page and made my way around the links:

https://docs.microsoft.com/en-us/powershell/azure/overview?view=azps-2.4.0

When I’m doing research like this, I have a habit of opening a new tab if the page I’m on has good information but is also linking over to another page. To give you an idea about how much looking around I did, after reading the pages and completing my migration of this script I had about 10 tabs still open.

Now I personally had no luck uninstalling the old AzureRM as recommended. I following the steps and tried a few different ways but kept getting access denied errors (even when running as admin)

I ended up skipping the uninstall and knew I would just need to avoid using the commands from AzureRM.

#Azure PowerShell needs PowerShell 5.1 or higher on Windows 
# (or 6.0 on other platforms)
$PSVersionTable.PSVersion

#Install Az
Install-Module -Name Az -AllowClobber

After trying to uninstall AzureRM, making sure I had the right versions installed and installing the Az Module. I started off on the first part:

Logging into Azure. This was simple and strait forward.

Connect-AzAccount

Boom. Easy. Just as easy as logging into AzureRM. I even found how to login to other Azure environments, such as AzureChinaCloud and AzureUSGovernment.

Connect-AzAccount -Environment AzureUSGovernment

Now the next step was to find the new matching OperationalInsightsQuery command. Microsoft had a sample on how to do this:

Get-Command -Verb Get -Noun AzVM* -Module Az.Compute

This returned 30 commands at the time. There was not really any notes on how to use this command, but after looking at the list and then back at the command I could see this was all the command for AzVM in the Az.Compute, it did take a couple tries to notice the Az.Compute was filtering more then what I wanted to filter so I changed the command to return all AZ items… and I quickly saw why they filtered… it returned over 2500 commands. 

 (Get-Command -Noun Az* -Module Az.*).count

Re-filtering my command and it became clear that the new command was almost the same as the last one.

Get-Command -Verb Invoke -Noun *Query*

Name
----
Invoke-AzOperationalInsightsQuery
Invoke-AzureRmOperationalInsightsQuery

There is my old command and my new command. This was a straight forward fix, replacing just a couple commands. Ran the script and it worked.

### New Script
Connect-AzAccount

$MyQuery = 'LongQueryHere-blah-blah-blah | where TimeGenerated between(datetime("2019-07-24 17:37:30.999") .. datetime("2019-07-25 17:37:32.999"))'

$MyQueryResponce = Invoke-AzOperationalInsightsQuery -WorkspaceID "0e000e00-0aa0-0ee0-a0aa-0000000000ee" -Query $MyQuery

Write-Output $MyQueryResponce.Results > C:\Folder\MyFileNameHere.txt

Over all it was not bad. The hard part is finding the commands you want to run and then knowing how to use them, but in my case it was much of the same. I reviewed the breaking changes list and it did not look to bad from my usage. I like naming over the old way, and so far it’s working smoothly.

Read More
By Nick Gorveatt

Reset SQL SA Password

May 3, 2019 | PowerShell, SQL | No Comments

For SQL Server Password Recovery – Reset SQL SA user if password is lost.

So you can’t login to your SQL Server, or you have lost your SA password. Don’t worry, all is not lost, there is a way to recover. Log onto the server hosting the SQL as an admin then follow along:

Here is the quick strait forward answer you are looking for, optionally there is a deep dive below as well.

Run PowerShell as Administrator
Run these commands: (it’s ok to leave the comments)

net stop MSSQLSERVER #This stops the SQL Server service
net start MSSQLSERVER /m"SQLCMD" #This starts the SQL Server in Single User Mode and with SQLCMD enabled to run SQL at the command line
SQLCMD #This opens a SQL CMD prompt
ALTER LOGIN sa WITH PASSWORD=N'SetYourStrongPassword!!11!'
GO
exit
net stop MSSQLSERVER #This stops the SQL Server service
net start MSSQLSERVER #This starts the SQL Server service in a normal state

If you just needed a quick reset, then that is it, you are welcome.

A Deep Dive

So you want more details, or maybe something is different about your setup and cause an error. Let’s walk through each step and get you more details on what’s happening and what we can do to get you back in your SQL Server.


Make sure to Run PowerShell as Administrator

Tip: Make it easy on yourself, open PowerShell, right click it on the Taskbar, Pin to Taskbar it. Then right click PowerShell on the Taskbar and chose Run as Administrator

Note, when stopping the MS SQL Server with net stop MSSQLSERVER you may get message about a dependency services needing to stop too. This is normal, for example you may have a service running to allow SQL jobs to be automated, this of course relies on the SQL Server to be running to run the jobs. You will need to stop those dependency services too, the command line will prompt you a yes/no if this is the case for you.

If you are getting error about stopping the service in general, more details should be logged to the Windows Event Log.

If you are getting an error about starting the service, make sure the service is stopped. You can’t start a service that is already running, also more details about a start error should be logged to the Windows Event Log.

Note, I’ve seen a lot of other how-to use a -m”SQLCMD” vs using a /m”SQLCMD”, (highlighting the -m vs /m), but I have not had any luck with -m as the SQLCMD command then tries to start as the domain user you are logged in as and gives  access errors.

At this point you can basically run any normal TSQL, as you would in the Microsoft SQL Server Management Studio (SSMS). In this example we are resetting the password the default SA (system admin) account. You could create a new user or add a domain user or group if the SQL server set to Windows Authentication only. Just don’t forget to give them sysadmin permission.

It is important to run a GO after entering all your TSQL, or else the commands won’t actually run.

Exit the SQLCMD

Just another stop and start to restart the SQL Service, alternately you could restart the server at this point to get a clean start (Hey, you could even do it the PowerShell way with the command: Restart-Computer)

Putting it all together, here is an example of a successful reset.

Read More
By Nick Gorveatt