Press "Enter" to skip to content

Category: Powershell

Creating a Self-Signed Certificate in Powershell

Published 2022-06-21 by Kevin Feasel

Tom Collins says ‘trust me’:

Use the Powershell cmdlet New-SelfSignedCertificate

Warning: I only ever use this method for testing purposes – never use for Production environments. A typical test would be for  SQL Server Network Encryption SSL Certificate Management

Encryption strategy  should be part of a wider review of Database Security Countermeasures against hacker attacks

For more details check the Microsoft documentation but here are some notes on some of the parameters  attached to the Powershell cmdlet – New-SelfSignedCertificate.

Read on to see how you can create the certificate and use it in SQL Server. Connections will complain (and rightfully so) about the self-signed certificate but you’ll be able to try functionality which requires a certificate in an environment in which you don’t have one available for whatever reason.

Comments closed

Creating Calculation Groups in Power BI Desktop via Powershell

Published 2022-06-20 by Kevin Feasel

Phil Seamark helps those who are stuck lacking tools:

I recently shared on Twitter a 7-module learning path on MS Learn that teaches all you need to know about calculation groups in Power BI. This learning path is an excellent course, and I highly recommend it. However, as part of this exchange, I received a reply from someone lamenting that calculation groups are unusable in organisations that will not allow non-Microsoft applications. Power BI Desktop does not currently have UX enabling you to create/manage calculation groups in a Power BI Model, so the most common method today is to use 3rd party tools such as Tabular Editor.

This exchange is not the first time I have heard this feedback, so I decided to share a technique showing how you can use Microsoft tools. The approach used in this article uses PowerShell but can quickly get translated to VS Code or other scripting environments.

Click through if you’re in that unfortunate situation.

Comments closed

Searching for SQL Server Backup Locations

Published 2022-06-17 by Kevin Feasel

David Fowler can’t remember where those backups went:

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

Read on for a Powershell script which can help out with this task.

Comments closed

Reading the SQL Server Error Log

Published 2022-06-17 by Kevin Feasel

Lee Markum has two ways to read the SQL Server error log:

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Click through for those techniques.

Comments closed

Finding Assigned Synapse RBAC Roles in Powershell

Published 2022-06-15 by Kevin Feasel

Charith Caldera wants to know your access level:

One of the key use cases that most customers face difficulties while retrieving or assigning the role-based access control in Azure Synapse Analytics, that they cannot find the correct usernames, group names or the service principal names using the PowerShell cmdlet “Get-AzSynapseRoleAssignment”. The PowerShell cmdlet only provides limited information and it’s difficult to understand since that contains the object IDs. 

Read on for a script which helps with this problem.

Comments closed

Changing Default Powershell Behavior via Commands or Proxies

Published 2022-06-08 by Kevin Feasel

Jeff Hicks gives us a choice:

I’ve often told people that I spend my day in a PowerShell prompt. I run almost my entire day with PowerShell. I’ve shared many of the tools I use daily on Github. Today, I want to share another way I have PowerShell work the way I need it, with minimal effort. This specific task centers on files and folders.

As you might expect, I am constantly creating, editing, and managing files. I do all of this from a PowerShell prompt. I rarely use the start menu to find a program to launch. My challenge has always been finding the files and folders I’ve recently been using. Get-ChildItem is naturally the PowerShell tool of choice, but I’ve finally gotten around to making it work the way I need.

Not having done either of these before, I’m not sure which would be my preference, as I’d like to make sure it’s easy for me to remember later how I got to this non-standard state in case I need to replicate it elsewhere or if somebody else is at my keyboard. That’s one nice thing about the .bashrc file: it’s just there and well-known enough that people can look for changes there.

Comments closed

Pre-Staging Log Shipping Backups with dbatools

Published 2022-05-26 by Kevin Feasel

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

Reversing Strings with Powershell

Published 2022-05-11 by Kevin Feasel

Jeff Hicks solves a challenge:

The warm-up challenge was all about manipulating strings. That alone may not sound like much. But the process of discovering how to do it and wrapping it up in a PowerShell function is where the true value lies.

Beginner Level: Write PowerShell code to take a string like ‘PowerShell’ and display it in reverse.

Intermediate Level: Take a sentence like, “This is how you can improve your PowerShell skills.” and write PowerShell code to display the entire sentence in reverse with each word also reversed. You should be able to encode and decode text. Ideally, your functions should take pipeline input. For bonus points, toggle upper and lower case when reversing the word.

Click through to see both of these solutions in action.

Comments closed

Getting Cluster File Share Witness Sharepath via Powershell

Published 2022-05-11 by Kevin Feasel

Tom Collins needs some information:

I normally use the Windows reg utility to get the  Cluster File Share Witness sharepath information. This is an example command line on a server returning the File Share Witness details

reg query \\myCluster\HKEY_LOCAL_MACHINE\Cluster\Resources /s /f sharepath

I want to start integrating these sorts of tasks into automated information gathering procedures and migrate this task into the Powershell library. Could you share some Powershell code to get the Cluster File Share Witness sharepath  information

Click through for a Powershell one-liner which gets this information.

Comments closed