dbatools

Clean up your dbatools

It’s easy to get the latest version of the dbatools PowerShell module:
Update-Module -Name dbatools
However, this does not remove the old versions. Those stay around until you manually do something about it.

To see all the versions installed, this command is helpful:
Get-Module -Name dbatools -ListAvailable | Select-Object Name, Version, Path

Why clean them up?
1) Remove clutter and free up space
2) Eliminates questions about exactly which version you’re running
3) Removes duplicates in the ISE Commands tab like this…

Clean it up
The process is made simple by a script included in dbatools, cleanup.ps1. It is not exposed as a public function, so you need to run it from the module directory.
You could get the full file location or change directory into the path listed for the latest version installed in the above Get-Module output and execute the cleanup.ps1 script.

There is a simpler and repeatable one-liner method that I found in one of Shawn Melton‘s GitHub comments.
It can be run with this command, in an Administrative PowerShell command window, ISE is not supported:
& "$((Get-InstalledModule dbatools).InstalledLocation)\cleanup.ps1"
It will show you the current versions, and then ask you to confirm the removal action.
Then you just have to wait for the cleanup to run (some 3rd party Anti-Virus products can slow down this step)

Confirm it
Then you just run that Get-Module command again:
Get-Module -Name dbatools -ListAvailable | Select-Object Name, Version, Path

A much cleaner set of modules!

Troubleshooting

SSRS and Kerberos with a Cluster

I recently came across an error involving SQL Server Reporting Services (SSRS) that I recognized immediately but still took me some time to resolve due to intricacies of this particular environment.

This involves a scenario with a Web App calling into SSRS which then connects to a separate SQL Cluster.

The admin of the Web App reported this error when calling SSRS:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source ‘DataSource’. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

As usual, not a lot of details. So I went into the Report Manager web interface (from a workstation) and tested the datasource, which turned out to be using Windows integrated security. I got

Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

I had a feeling this was Kerberos related right away. The Web App admin, agreed and mentioned adding SPNs.

After much research, I found that the needed SPNs are

  • HTTP / SSRS server, with no port
  • SQL cluster, with SQL port

In my environment the SSRS server is accessed by its machine name but if it had a separate DNS entry or was load-balanced, this would have changed the name I needed to register.

We also had multiple nodes in the SQL cluster but the report datasource used the cluster name, so it is the name that matters for the “second hop”.

The solution that worked for us was like this:

setspn -a HTTP/ReportServer mydomainsqlreportsvc setspn -a HTTP/ReportServer.mydomain.local mydomainsqlreportsvc setspn -a MSSQLSvc/SqlCluster:1433 mydomainsqlEngineServiceAcct setspn -a MSSQLSvc/SqlCluster.mydomain.local:1433 mydomainsqlEngineServiceAcct 

In this example the SSRS machine name is “ReportServer” in the “mydomain.local” domain running with a services account of “sqlreportsvc”. The multi-node SQL cluster had a cluster name of “SqlCluster” and it was running on port 1433. Names have been changed to protect the innocent.

Shout out:
I have to say Scott Stauffer’s chapter on Kerberos in SQL Server MVP Deep Dives was an excellent resource for me during the problem solving and fix of this issue.

Community

Little fish, big pond

The SQL Server Community feels large. There are so many resources online at my disposal that I may even say it is bit overwhelming.

There so are many that greats that came before me which leads me to ask, what could I possibly contribute by having yet another blog?

The answer is quite simple – it is a challenge to myself. A challenge to begin sharing and contibuting more often. I have been a dark matter DBA for far too long!

The quest begins online now. Eventually, I may put together material that is interesting enough and work up the courage to present in person at an event such as a SQL Saturday, Code Camp, or other user group.

In writing this, I’m looking forward to the grand SQL Server event of them all, PASS Summit. From past experience, I shall be returning all insipred, fired up, and ready to apply all the great new nuggets that are learned.

To all the SQL Family, hope to see you around, both online and at live events.