Kevin3NF

We’ve all seen them.

Login failed for user ‘MyDomainBob’ (password issue)
Login failed for user ‘MyDomainNancy’ (default database issue)
Login failed for user ‘blah, blah, blah…’

But what about Login Failed for user ‘Insert Chinese characters here’, Reason, An attempt to logon using SQL Authentication failed.

Wait…nobody in the company has a username with Chinese characters.   And we don’t have SQL Authentication turned on….

Do not just let these messages pass you by!

These come with a client IP address at the end.  I did a ping -a on the one I got, and found:

Somebox.qualys.morestuff.mydomain.com, along with 4 replies.   So at least it was a valid internal IP address.

From here, I noticed Qualys in the machine’s FQDN.  As luck would have it I was recently on a Vulnerability Management team (elsewhere), and Qualys was the name of one of the scanning tools we used to look for Vulnerabilities on the servers, routers, etc.

Now…I can make assumptions, but I’m not going to when it comes to something like this.   I checked all the SQL Servers in my area of responsibility and found this on all but one of them.

I wrapped all of the data and findings in a nice package and sent it off to the boss to engage the security team for proper investigation and remediation.  I suspect the Qualys server has a problem…this doesn’t look like one of its checks, but I’m not the expert on that.

So the point of this is not to teach you about all the ways to trouble shoot login failed messages, but rather to make sure you are investigating who is failing to log into your SQL Server and WHY.

• If you are logging successful logins, quit it.   You’re filling the ERRORLOG.
• If you are not logging failed logins, start now.   Don’t ignore possible hacking attempts.
• If you are not investigating login failed messages, start now, or you could be setting yourself up for this:  “Yeah, that data breach of user and HIPAA information was missed by our DBA.”

That is a serious RGE and CLM you don’t need.

That is all for today.

Waffle fries for lunch 🙂

Kevin3NF
The OnPurpose DBA

• Not buying storage, especially on a maxed out server
• Easy to query full dataset across on-prem and Azure stretch
• Ummm…all editions is a good thing instead of Enterprise only
• Nope.  That’s it.

• “You are billed for each hour the Stretch database exists, regardless of activity or if the database exists for less than an hour, using the highest rate that applied during the hour.”
• Lowest performance rate is $1.25/hr or just under $1K/mo. Only goes up from there
• “Stretch Database currently does not support stretching to another SQL Server. ” Azure only
• Lame/minimal filters…you have to roll your own functions, and they must be deterministic…no “Getdate() – 30”. This GUI is only slightly better than the horrible nightmare that was Notification Services…

I was participating in an email interview about my SQL career and opinions and one of the questions was basically “What’s the worst SQL thing you’ve inherited?”

I gave them this list:

Really….all that on one server!

Upgrade your fries broseph!!!

Kevin3NF

Plain ‘ol English….

Blocking:

You walk into Starbucks and there’s 1 guy ordering.  He finishes, then you order.  That’s blocking, and its perfectly normal, expected and acceptable.

You walk into Starbucks and there’s 1 guy ordering for 15 people in his office.  You’re willing to wait, but not forever.  That’s moderate blocking.

You walk into Starbucks and there’s 1 guy ordering, 7 people in line and they are all ordering drinks with names too long to remember….you walk out.  That’s excessive blocking with a failed transaction (you).

Blocking in SQL Server is just transactions that need the same resource lining up to take their turn.  Its by design and is a good thing.   Excessive blocking needs to be fixed.

Deadlocking:

You have 2 children.  Each has a toy, and at the same time they each grab for the toy the other one has, but won’t let go of the first one.  This is a Deadlock.   SQL Server has a built-in process to resolve this.  They gave it an official name, but I call it “Mom.”   Mom steps in and breaks it up…automatically.  This is a code issue much of the time…profile it, trace flag it, whatever..but find and fix your application code.  If its not your app code, it could be just performance related…tune or rebuild some indexes may get you there.  Start HERE for troubleshooting deadlocks.

Does that help?

They are NOT the same thing…Deadlock is bad, Blocking is part of the engine…to a point.

Next up (maybe): Replication and Log Shipping are not the same thing…quit using them interchangeably, especially when you mean “Clustered”

Have a nice day!

Kevin3NF

Short one today:

I tell the upper level manager that ALL of his data is at risk due to one root issue.   Got him good and scared…he totally believed me.  And did absolutely nothing to fix it, or even explore possible fixes, which would have been a good exercise.

Swept it under the rug.

Seriously…one disgruntled employee with minor hacking skills and this place goes away.

Oops.

No fries for you.

**  This was a long time ago, so no…you can’t guess who I’m talking about by reading my LinkedIN profile 😀

With apologies to Nat King Cole 🙂

Database backup failing

Write on “D:backupMyDatabaseMyDatabase_20150208104103883_D.bak” failed: 665(The requested operation could not be completed due to a file system limitation) [SQLSTATE 42000] (Error 3202) 

Hey, customer…let not put backups on O/S compressed drives please.

Oh…and why do you have your MDF/LDFs on the same drive…also compressed?

Resolution – punt it back to them and tell them SQL Server on compressed drives is unsupported config by Microsoft and by me.   Uncompress and expand or add a drive and uncompress.

Sheesh.  How many worst practices can you get in one server?