SQL Server / Windows OS losing connection to Domain Controllers

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

 

A handful of my SQL Servers began losing connecting with the domain controllers after recent Windows Patches.  The only resolution was a reboot of the SQL Server, which obviously incurred downtimes.  The issue hit two non-production VMs and also a Windows SQL Server Cluster.  Oddly, both nodes in the cluster were affected simultaneously, even though SQL wasn’t running on the passive node.  After some troubleshooting with Microsoft, we identified the issue and I wanted to share it here.  A fix is pending, targeted for July.

The issue affects Windows Server 2012 OSes utilizing iSCSI storage and was introduced with KB4012216, a March security roll-up.  The total amount of ephemeral ports on the system becomes exhausted over time.  I won’t spend too much time showing you how to isolate the specific data we collected for Microsoft.  I feel that if you are experiencing this issue after a recent application of patches, and you are running Server 2012 with iSCSI, that is probably proof enough.

You can view some details about ports in use with the following commands, the first being a PowerShell command:

There is no permanent solution, but the following are options for workarounds until a patch is released.

  1. The most obvious would be to uninstall the patches.  We uninstalled all 3 roll-up patches that we applied, but Microsoft indicates that it is part of KB4012216.
  2. You can stop using iSCSI.  Not a viable solution for most.

Increase the number of available TCP ephemeral ports and modify TCP Time Wait Delay to increase the time it takes for the issue to manifest.  Type the following from a command line prompt and restart the server:

I hope you are able to use this information to fix any recurring issues you’ve experienced in your environments.  I spent the last 3 or 4 nights rebooting SQL Servers after hours, but not tonight!

 

Posted in Active Directory, Security, SQL Server, Windows

SQL PowerShell Pros: Submit Your Abstracts for PASS Summit!

Recently, PASS announced their first round of speakers for Summit 2017 speakers and I’m on the list! I’m particularly excited because Summit is the largest SQL Server event in the world and it’s looking like PowerShell will be getting some great exposure this year.

In addition to announcing that I’ll be speaking, PASS also declared that PowerShell is a hot topic for 2017! It’s no surprise, 2016 and 2017 have proven to be pivotal years for SQL PowerShell because of increased investment from both Microsoft and the community.

If you are a SQL Server Pro who loves PowerShell, please answer the Call for Speakers and submit an abstract! The deadline is coming up soon, on Wednesday, May 24 at 21:00 UTC. I’ve got a feeling that this year, there will be a big ol’ PowerShell Party at Summit and it would be amazing to have as much representation as possible.

If you’re concerned that you don’t have enough speaking experience, consider that earlier this year, PASS said that the selection process will be changing. I don’t know the details of the changes, but knowing PASS was going in different direction encouraged me to submit and it worked out well!

So please SQL PowerShell Pros, come to PASS Summit 2017 and represent. The community needs to hear from you!

Posted in PowerShell, SQL Server

PowerShell Gallery Metapackages

Recently, I was approached by supa-famous Joey Aiello of the PowerShell team at my favorite conference ever, PSConf.eu.

So Joey comes up and says “Chrissy, Aaron Nelson has pretty much required me to talk to you. The SQL Community has the #1 PowerShell UserVoice request. We see that – we’ve heard you, The People want Out-DataTable and we agree. Would you be happy if we added it to the PowerShell Gallery first?”

“Uh, no! I want Out-DataTable to be a first class citizen like Out-GridView.”

“But where we’re going with PowerShell — we’re going smaller – to just core files, then you add on from the Gallery as desired.”

“Oh dang, like Linux! I’m liking it, keep talking.”

“To be clear, this is post 6.0. In the 6.0 timeframe, but we want to decouple as many release trains as possible, like PowerShellGet and PSReadline. But we’ll still very well package the ‘uber-complete, awesome devops tool edition’ of PowerShell. In the meantime, you could setup a metapackage for just your database stuff.”

“So it is like Linux patterns! PowerShell Gallery does that? I’m sold.”

Why I’m really happy with this answer

I’ve used Linux for a really long time and appreciate how you install your core files, then pile other stuff on as needed. In SUSE Linux’s Package Manager (and maybe others) these are called patterns.

So for me when setting up my Linux servers, I skip on the GUI patterns and select, for instance, a LAMP pattern. That has all the stuff I need like Apache, PHP, Python, MySQL, etc — but nothing more.

If you take a closer look at the screenshot above, you’ll see some sample patterns include

  • Console Tools
  • Laptop
  • Software Management
  • X Windows System

If you check the Console Tools, you’ll find a number of packages that make it easier to work with the console including alpine, nano and ncftp.

So about Metapackages

PowerShell is similar in this way now that you can create a PowerShell Gallery module that’s made solely of other modules or packages. Recently, I created a metapackage named Database. Currently, this metapackage contains

In the future, I’ll be able to add Out-Datatable (or whatever they name it, perhaps ConvertTo-DataTable) and then any other database related module that makes sense. If you have any suggestions, let me know.

Here’s how I did it (copy/paste/change guid)

First, you must ensure that you’ve installed the modules that you want to require. Initially, I got an error when trying to Publish-Module and because I needed to install the DSC resource, xSqlServer, prior to attempting to include it in the metapackage.

Next, create new directory that’s the same name as your module, then add a psd1 to that directory. That’s all you need. Just one module manifest (.psd1) in one folder. So for the Database module, mine was C:\github\Database\database.psd1.

This is what the actual psd1 looks like – note that the important line is:

RequiredModules = @(‘sqlserver’,’dbatools’,’dbareports’,’PowerUpSQL’,’xsqlserver’)

If you’d like to create your own metapackage, you can copy/paste and then change the GUID and info. To get a new GUID, just use New-Guid or [guid]::NewGuid() to generate your own unique GUID.

Publishing

Publishing your metapackage to the PowerShell Gallery is like publishing any other PowerShell module. First, you sign up to the PowerShell Gallery, then get your NuGetApiKey key, then… Actually, just follow this tutorial by Adam Bertram :).

I stored my database.psd1 in C:\github\Database, so I published it by executing

Publish-Module -NuGetApiKey xyz-xyz-xyz-xyz-xyz -Path C:\github\Database

Installing

Once your module has been published to the Gallery, it can be installed like any other module from the Gallery.

My own package contains the SqlServer module which has a naming issue on some machines, so if you’d like to install the Database metapackage, just throw in -AllowClobber at the end.

Install-Module database -AllowClobber

Check out how cool this is — it automatically downloads all the required modules :D Nice work, PowerShell Team!

Hope that’s been helpful. If you have any questions about metapackages, hit up me or Joey on Twitter (and follow him while you’re at it. He needs a couple followers ;)

Posted in Linux, PowerShell, SQL Server

SSMS Complains of “Inconsistent Line Endings” with PowerShell Output

If you’ve used Out-File, Set-Content or Add-Content to create a SQL file, you may notice that SQL Server Management Studio complains about normalizing the line breaks.

If you see this, simply using -Encoding UTF8 for your output instead of ASCII, the default, will solve the problem.

Posted in PowerShell, SQL Server

T-SQL Tuesday: The Cloud, Globalization and Automation

TSQL2SDAY-150x150

Today’s blog post is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a monthly blog party on the second Tuesday of each month. Everyone is welcome to participate.

This month’s T-SQL Tuesday, hosted by Koen Verbeeck (blog | twitter), is all about how technology has changed a lot in the past years and the potential impact these changes will have on SQL Server Pros.

In Koen’s invitation, he specifically called out the cloud, globalization and automation.

The Cloud

When it comes to the cloud, I think on-prem is still a safe bet and will be for at least another decade.

My first bill from Amazon Web Services arrived nearly ten years ago on June 1, 2007 (it was for $0.61, not bad). Since then, I’ve played in the cloud off and on, and was even the technical editor for a book about Automating Microsoft Azure with PowerShell. When it comes to work work, however, everything is still on-prem. Similarly, my awesome homelab is tucked safely in a storage room at my house.

Ultimately, when it comes to the cloud, I think it’ll be at least another 10-15 years before a majority of organizations fully move away from on-prem. And as they do, I’ll go along with them, but like many organizations, I’m in no rush. The “Datacenter” in my Cloud and Datacenter Management MVP is strong.

At the same time, I know that my varied systems background lends itself to the confidence that I can adjust to cloud movement with no issues. I’d encourage DBAs with a weak systems background invest more time into understanding their whole environment and not just SQL Server itself.

Globalization

Is this offshoring? If so, I’ve seen situations where it’s worked, and situations where it hasn’t. I’m not really concerned.

Automation

Y’all know what I’m gonna say here! I love automation and PowerShell. I know for a fact that PowerShell and T-SQL together are the future of SQL Server administration. As someone who often presents about dbatools, the popular SQL PowerShell community project, I’ve seen the excitement and relief that PowerShell automation brings to SQL Server Database Pros.

From making it way easier to migrate entire instances to automating backup testing and verification, PowerShell makes it straight up more enjoyable to be a DBA.

Backups

For years, I’ve used Ola Hallengren’s Maintenance Solution, but I always found it stressful to know that at each location, I’d have to spend a chuck of time working on the process to perform restores as required.


A whole instance of Ola backups

What happens if an entire instance goes down? Now, thanks to Stuart Moore, restoring the most recent database backups to another instance is as easy as

Get-ChildItem \nas\sql\sql2016 | Restore-DbaDatabase -SqlInstance sql2016a -MaintenanceSolutionBackup

What about logins with their passwords, SIDS, permissions and all that? I export every login within my estate every day to a text file using Export-SqlLogin.

What about jobs? Well, we don’t export those yet, but they’re on the list ;) Here’s a video of Restore-DbaDatabase to keep you occupied in the meantime.

SPNs

These used to be a pain, but they aren’t anymore. Now, thanks to Drew Furgiuele, dbatools offers a suite of SPN-related commands that help make Service Principal Name management way easier.

Test-DbaSpn tests for SPNs that should exist and can be piped to Out-GridView and then to Set-DbaSpn. So easy, it makes SPN management fun!

We also have a quick 5 minute video that gives a tour of the four SPN commands.

Migrations

As previously mentioned, migrations can be automated and even scheduled. Check out this video where an entire instance is migrated with a single command.

Finding things

Ever needed to find a stored procedure that contains specific text? When I did development, I had to all the time. dbatools offers an easy, awesome way to do this. You can even search using regular expressssssions! What?!

And it’s smoking fast, too. I once evaluated 37,545 stored procedures on 9 servers in less than seconds!

We’ve even made it easy to find databases, orphaned files, user owned objects, jobs, unused indexes and more. Hell, we even have a command for finding dbatools commands!

In conclusion, automation is life

Intrigued? Check out our whole catalog of commands that automate common (and not so common) SQL Server tasks.

Thanks for joining me for this month’s T-SQL Tuesday.

Posted in PowerShell, SQL Server

A Suggested Fix for Thunderbolt Display Buzzing

About a month ago, my beloved Thunderbolt display began buzzing. After some research, it seemed that there could be all sorts of solutions. The one that seemed to almost always work was sending off the monitor to get the power supply fixed.

Apparently, there’s an issue with the power supply that can be confirmed if you crank up the display brightness. I cranked up my brightness and sure enough the sound was louder.

The buzzing didn’t start, however, until I was on my computer for a while (20-30 mins?) so each day when I came in, the silence would offer me some dim hope that maybe it fixed itself.

The Solution

I figured that if it was quiet at first, the reason it began buzzing was because it got too hot. If I could lessen the burden placed on the power supply, and thus reduce the heat, it should stop. I recently started powering an old Macbook Pro with my cable (tho the display displays what’s on my Mac mini). To solve the buzzing, I stopped using my Thunderbolt display to power my laptop. That reduced the heat and there’s no more buzzing.

Note that this worked, then it didn’t for like a day or two, but then it did again for good. Now, my Thunderbolt Display hasn’t buzzed for a full week. Let’s hope it keeps going. I love this thing and I’m totally distraught that Apple discontinued Thunderbolt production.

But it stopped working when..

Maybe it’s coincidence, but I was buzz free for like a whole month, then I lit a candle near my monitor and boom. Buzzing. Next day, no candle, no buzzing. WTF.

Also

I’d suggest that, in general, people don’t use the built-in power cable if possible as it seems to lessen the lifespan of older power supplies. I didn’t have this issue until I powered that laptop for a couple months straight :(

Posted in OS X & iPhone

Loading SMO in Your SQL Server-centric PowerShell Modules

In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as “Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”.

Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

Now, however, we must use the fully qualified name and this presents a problem for people who use SMO because we can’t be sure what version of SMO exists on the user’s system unless we’re specifically targeting an SMO version like Microsoft’s official module, SqlServer.

The Problem

When I tried publishing dbatools with just RequiredAssemblies = @(‘Microsoft.SqlServer.Smo’), I received the following error:

Publish-Module fails with “The specified RequiredAssemblies entry ‘Microsoft.SqlServer.Smo’ in the module manifest ‘…\dbatools.psd1’ is invalid. Try again after updating this entry with valid values.

I looked at the official SqlServer module to see how they handled RequiredAssemblies, and as mentioned previously, noticed that they addressed only the 2016 version of SQL Server SMO. I then looked through GitHub for how other people used RequiredAssemblies, both with and without SMO. Nobody else appeared to have a solution that addressed requiring one of an array of possiblities.

The Solution

I ended loading my assemblies in the psm1 file instead of using the ModuleManifest :3

What you can see in the code is that I iterate through all of the versions of SQL Server, including vNext, 2016, 2014 and so on, all the way down to 2005. If none are found, the module throws an informative exception. If SMO does load, the script then attempts to load the other associated assemblies.

Some assemblies such as Management.XEvent don’t exist in lower versions of SMO and will throw an error that I don’t care about – we handle version checks later on in our specific scripts such as Copy-SqlExtendedEvent.

Oh, and don’t worry about this loaded allll of those assemblies. It only takes 11-40 ms on my machine to run the above script.

So feel free to copy the code above to your own modules that use SMO or if you have a better way, let me know. Happy Holidays!

Posted in PowerShell, SQL Server

Find Every Parameter for Every Command in a PowerShell Module

We’re currently working on standardizing our documentation for dbatools and I needed a list of all of the parameters we use. Here’s how I did it (well, I did it an uglier way then Aaron made it prettier ;))

Get-Command -Module dbatools -CommandType Function | ForEach-Object { $_.Parameters.Values.Name } | Sort -Unique

Posted in PowerShell