Press "Enter" to skip to content

Author: Kevin Feasel

Reviewing SQL Server Logins

Published 2022-04-08 by Kevin Feasel

Lee Markum takes a look at logins:

You know you need to be thinking about SQL Server security, but maybe you’re not sure where to start. Topics like firewalls and ports and port scanners and such may be dancing your mind. Those are good things to think about, but they are not under your sphere of influence as a data professional in charge of SQL Server. So, what can you do?

Your first place to start is by looking at the Logins, which as I’ve explained in a previous  post, are at the level of the SQL Server instance level.

Read on for two approaches.

Comments closed

Building a Gantt Chart with Power BI Paginated Reports

Published 2022-04-08 by Kevin Feasel

Paul Turley unlocks one of my guilty pleasures:

A Gantt chart is a running list of activities with the duration for each displayed as a horizontal bar depicting the beginning and ending day along a horizontal scale. The challenge is that this is not a standard chart type in either Power BI or SSRS/Paginated Reports. Furthermore, project planners may prefer to see activities as rows in the format of a printed page, as you can see in this example:

I like Gantt charts more than is probably healthy. Paul shows a method which isn’t exactly easy but it does the trick.

Comments closed

Replacing PWDENCRYPT with HASHBYTES

Published 2022-04-08 by Kevin Feasel

Randolph West does a switcharoo:

Dave is referring to the Microsoft Docs page for PWDENCRYPT(), which has been deprecated for some time. Unfortunately, although the recommended replacement is HASHBYTES(), there isn’t an example on either page of how to replicate the functionality of PWDENCRYPT().

So, borrowing from Sebastian Meine who wrote an article titled Hash Algorithms – How does SQL Server store Passwords?, this is how you can replicate the functionality of PWDENCRYPT() to create a login, using the HASHBYTES() function instead:

If this is what you have, so be it…but an algorithm like bcrypt or scrypt would be so much better for this purpose than SHA2 or SHA3. That means using a third party library for it but there are plenty for React, .NET, Python, etc.

1 Comment

Skipping SQLPS

Published 2022-04-08 by Kevin Feasel

Ben Miller avoids SQLPS:

In researching some things for a presentation, I was to give at Intersections, Spring 2022, I came across this documentation page for SQL Server PowerShell that in the SQL Server Agent section it indicates that there is a way to get the Agent Job Step to skip loading the SQLPS module and you get to use PowerShell natively and can load whichever modules you would like.

What? Huge News!

That is good news. SQLPS is so outdated it’s not even funny, so this gives you a much better alternative.

Comments closed

Printing ggplot2 Plots as Receipts

Published 2022-04-06 by Kevin Feasel

Bob Rudis has fun with a Point of Sale printer:

At the end of March, I caught a fleeting tweet that showcased an Epson thermal receipt printer generating a new “ticket” whenever a new GitHub issue was filed on a repository. @aschmelyun documents it well in this blog post. It’s a pretty cool hack, self-contained on a Pi Zero.

Andrew’s project birthed an idea: could I write an R package that will let me plot {ggplot2}/{grid} objects to it? The form factor of the receipt printer is tiny (~280 “pixels” wide), but the near infinite length of the paper means one can play with some data visualizations that cannot be done in other formats (and it would be cool to be able to play with other content to print to it in and outside of R).

Read on for a fun story which gets an entry in my most coveted category. H/T R-Bloggers

Comments closed

Fun with Powershell: A Roundup

Published 2022-04-06 by Kevin Feasel

Robert Cain has a bunch of posts:

Since September 2020 I’ve been posting extensively about PowerShell in a series I’ve titled Fun With PowerShell. In my next post I will begin a series on a new topic, but before I do I wanted to leave with a Fun With PowerShell Roundup” post.

Below you will find a listing of all posts in my Fun With PowerShell series. Note there are other posts I’ve done in this time frame, but since they did not focus on PowerShell I’ve omitted them from this listing.

Click through for a lengthy and good series.

Comments closed

Performance Optimization for Azure Data Explorer

Published 2022-04-06 by Kevin Feasel

Ashok Anand Kumar has some performance tips:

Azure Data Explorer provides the capability to easily fetch telemetry data from a variety of data sources and run complex analytical queries. Azure Data Explorer supports both batch and streaming ingestion to support near real-time latency requirements. Batch ingestion will have latencies based on the batching policy and query frequency from applications. Streaming ingestion can be leveraged for low latency requirements. Data is cached and indexed for faster query performance and optionally exported out to Azure Data Lake in parquet format for batch processing and integration with other Big Data and Machine Learning (ML) services. 

Read on for several tips.

Comments closed

Diagnosing an Always Encrypted Decryption Issue

Published 2022-04-06 by Kevin Feasel

Reitse Eskens does some troubleshooting:

For those who are not familiar with Always Encrypted: it’s a built-in technique in SQL Server where data gets encrypted in a random or deterministic manner with a certificate and an algorithm. Long story short, without the certificate it’s gobbledygook. If you want to read more, check out this link.

As mentioned, the encryption was working perfectly, no-one could read the data in a usable way in the application. The point was that the application should be able to do that. And so we took up the challenge to see where things went wrong.

I’ve found that data is much more secure when nobody can ever see it again. That’s why I store all of my data in /dev/null. It’s also extremely fast.

Comments closed