Press "Enter" to skip to content

Curated SQL Posts

Delegating Authentication using Managed Service Accounts

Published 2020-01-08 by Kevin Feasel

Jamie Wick helps us solve the classic Kerberos double-hop problem:

If the Report Server service doesn’t have permission to delegate to the SQL Server, it will try to connect anonymously (step 4 in the diagram above). Which results in this login error:

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided. [CLIENT: <Client IP Address>]

Historically report server and SQL server services, that needed the ability to delegate authentication to other servers, were configured to run using an Active Directory user account. Enabling delegation on these accounts was simply a matter of setting the Trust level on the Delegation tab of the account’s properties (with Active Directory Users & Computers).

But Jamie is here to show us a better way.

Comments closed

Generating an Email List from Active Directory Users

Published 2020-01-08 by Kevin Feasel

James Livingston takes us through an interesting solution to a common problem:

If you’ve ever performed some impactful maintenance on a SQL Server, you probably notified users. If you’re great at documentation and already know exactly who to contact, this script isn’t for you. If you don’t have a user email list, this script will create it for you!

I used to manage 500 SQL Server instances and there was daily maintenance\changes going on constantly. I wrote this PowerShell script to automatically create an email list for me. This PowerShell script gathers the login information from an instance of SQL Server and then pulls their email address from Active Directory.

Read on to see the script in action.

Comments closed

The Costs of Virtualization

Published 2020-01-08 by Kevin Feasel

David Klee points out that virtualization, configured correctly, should not harm SQL Server performance much:

A wonderful reader of my blog sent me a note (thanks Jess!) about a single line notation in the latest SQL Server release notes. The notes is as follows.

Running SQL Server on a virtual machine will be slower than running natively because of the overhead of virtualization.

The question was simple. Why would Microsoft add this disclaimer? It was being used as a negative talking point towards SQL Server virtualization, and holding the DBA team back from getting the benefits of virtualization.

David gives us some rough numbers on what that means. Spoiler alert: if you set up your environment right, it’s not much.

Comments closed

Controlling IoT Devices via Databricks

Published 2020-01-06 by Kevin Feasel

Saeed Barghi takes us through building an interesting solution:

A few weeks ago I did a talk at AI Bootcamp here in Melbourne on how we can build a serverless solution on Azure that would take us one step closer to powering industrial machines with AI, using the same technology stack that is typically used to deliver IoT analytics use cases. I demoed a solution that received data from an IoT device, in this case a crane, compared the data with the result of a machine learning model that has ran and written its predictions to a repository, in this case a CSV file, and then decided if any actions needs to be taken on the machine, e.g. slowing the crane down if the wind picks up.

This was a really interesting article.

Comments closed

Truncating Paths in Powershell Prompts

Published 2020-01-06 by Kevin Feasel

Jeffery Hicks shares a method for shortening your displayed path in Powershell:

As you can see, I don’t have a lot of space left at my prompt. Usually, if I am staying in one location say for demos, I’ll create a PSDrive with a shorter name. I use the New-PSDriveHere command from the PSScriptTools module. But another option is to truncate the prompt. So instead of the long path you see here it might be C:\Users…\HelpDesk. I modified the default PowerShell prompt function to do just that.

Click through for the script and a few demos.

Comments closed

Checks After a DBA Leaves

Published 2020-01-06 by Kevin Feasel

Thomas Rushton has some thoughts on a resiliency plan for DBA departures:

Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes.

Read the whole thing.

Comments closed

On Self-Signed Certificates

Published 2020-01-06 by Kevin Feasel

Sean Gallardy isn’t too concerned about self-signed certificates in SQL Server:

This happens at startup and is completely transparent to both end users and administrators alike, apparently not so much to your security team utilizing the latest and greatest security tools available (yes, that’s sarcasm). One day you may get a notice that “SQL Server is using a self-signed certificate (issue #1) and that the key length/algorithm isn’t what our internal security team allows (issue #2). You must fix asap or be out of compliance!” Oh no, the dreaded compliance threat.

Sean is…not impressed with these issues and explains why.

Comments closed

Using the OUTPUT Clause

Published 2020-01-06 by Kevin Feasel

Eduardo Pivaral takes us through the OUTPUT clause:

Even when the code is easy to read, but if you use this pattern over all your codebase, maintain it can become difficult if you have to change object names or implement it on another system.

T-SQL language provides the OUTPUT clause, that allows you to retrieve information from a DML statement in the same batch.

This is pretty useful for performance tuning in some scenarios, but also for simplifying multi-step processes.

Comments closed

Evaluating Classification Models

Published 2020-01-03 by Kevin Feasel

Dan Fitton takes us through some of the useful techniques and measures for evaluating classification models:

The confusion matrix is perhaps the most important thing to look at when evaluating a classification model. It contains a large amount of insight for such a small sized table. Despite its name, the confusion matrix is actually quite simple. It is a matrix that visualises the count of actual class instances against predicted class instances. This allows you to quickly see the amount of correct and incorrect predictions for each category, and whether any bias exists, and if so, where it is.

The example is specifically around Azure ML, but applies across the board. I think people get a little bit too hung up on accuracy and forget about important measures like positive and negative predictive value.

Comments closed

Time Series + Power BI Aggregations

Published 2020-01-03 by Kevin Feasel

Shabnam Watson answers a couple of questions around aggregations and time series in Power BI:

I have received a couple of questions about Aggregations in Power BI and whether they can be used to cover time series calculations such as Year to Date, Quarter to Date, and Month To Date. The answer is yes. Since time series calculations break down into calculations over a series of days, an aggregation table defined at day level with the basic summarization methods (min, max, sum, count) and the right relationship with a Date dimension, can answer Year to Date, Quarter to Date, and Month To Date calculations.

Let’s take a quick look at one such calcualtion and how it can be covered with an aggration. I am going to use the same version of AdventureWorks sample database and Power BI model that I used in my previous blog post on aggregations, with a few changes.

Read on for a demonstration.

Comments closed