T-SQL Tuesday #107 – Death March

This month’s T-SQL Tuesday is hosted by Jeff Mlakar and he asks us to write about a project that went horribly wrong. My story isn’t really worthy of the name “death march” but it was a pretty rough project.

The project started sometime in mid-2003. I was working as a web developer (Classic ASP) for an insurance company and they wanted to modernize the underwriting process with a web-based “workflow” application.

A platform was selected, the team was picked, and we set about customizing the devil out of it (it was more of a “framework” than turnkey application) and integrating with our existing systems. Of course, being an insurance company everything ultimately had to land in the mainframe.

The tech team was pretty large – upwards of a dozen and a half programmers across various disciplines. Four of us were kind of off on our own building the ASP front end and COM-based middleware – myself and Ned on the ASP side, Tony & Phil (all names changed) on the other side of the cube walls working on COM objects. The four of us worked really well together, with Phil & I each taking the lead in our respective disciplines. Our PM was great; we got along well and she knew the right questions to ask me to nudge me in the right direction.

As Phil & I dug into the platform API, we realized we were going to be implementing more features with our own code than expected as they weren’t actually built into the API yet as we had expected.

We brought a few consultants from a large company you’ve certainly heard of to help us work out a UI design. After 5 weeks, we presented our design proposals. Every – I mean every – proposal put forth by the consultants and me was shot down. The response was “those are very nice, but we want what we described to you 2 months ago, so build that.” I was flabbergasted. I asked the consultants if they’d ever seen anything like that and they admitted that they hadn’t. We just spent five weeks and a bunch of money to produce nothing.

February was approaching and while Phil, Tony, Ned and myself were firing on all cylinders, others weren’t doing as well. We were supposed to launch in March, but it was becoming apparent that we’d miss that date. The release date slipped to mid-April.

The four of us spent the last half of February and all of March tightening up our code. We had some performance concerns but hadn’t figured out where they were coming from yet. Acceptance testing hadn’t started yet; the project was expected to be “nearly complete” before that’d start.

Acceptance testing started in late March. Ned & I knew our code inside and out, and I could suss out pretty easily if a reported issue was there or in the COM objects so I could throw those bugs over the wall to Tony & Phil as necessary.

I returned from lunch on the last Friday of March and Ned said to me “I’m leaving at the end of the day.” Um…OK, thanks Ned, I am too. Then he clarified. See, Ned was a contractor and he he was ending his contract at the end of the day – and starting a new gig in Miami on Monday! We were unable to bring in a replacement, so I was flying solo for the rest of the project.

I spent April fixing bugs as fast as I could, re-implementing some features because they sounded good on paper but once seen in action, they weren’t acceptable. Mid-April came around and the project release date was pushed back by another month.

I went to the PM and expressed my concerns about the release date changes. I reminded her that I was getting married Memorial Day weekend and I’d be unavailable for 2 weeks due to that and the honeymoon. She assured me that this was the last reschedule, and even if that turned out to not be true, I wouldn’t be asked to change my plans.

Early in May was the final blow for me. We had an all-project meeting with several people from the highest levels in the company in attendance. The missed deadlines, the incredible strain placed on not only the development team but their families, the importance of the project to the company, all brought up. People laid some very personal feelings out. The response from one of the top-level people was cold, harsh, uncaring, impersonal, tone-deaf, and completely disheartening.

The project went live 10 days before I left for my wedding and honeymoon.


No project implementation is perfect out of the gate. Right before we went live, Phil figured out what was causing our performance issues and over the next month or two, more or less re-implemented that portion of the vendor’s API himself. We pushed out an updated version as quickly as we could; users were complaining about performance from day one. We got a lackluster “yeah, I guess it’s better now” but no real acknowledgement of the improvement Phil had made so quickly. It was too late; the perception was that this system is slow and that wasn’t going to change, now matter how fast we made it.

The project was started in the summer or early Fall of 2003 and went live in the last third of May 2004. By the end of the first quarter of 2005, the company had decided to go in a different direction and the system was on track to be mothballed; no new business was being scanned into it, and once the last piece of business that was in it was processed, everything was shut down.

The system spent more time under development than it did in use.

T-SQL Tuesday #104 – Code You Would Hate To Live Without

This month’s T-SQL Tuesday is hosted by Bert Wagner and he asks us to write about code we’ve written that we would hate to live without.

First off, “hate” is a pretty strong word so let’s go with “code you couldn’t bear to live without”. The first bit of code I couldn’t live without is reviled in some circles. More often it’s misunderstood and lamented, or…well, I’ll just show it to you.

Yes, you read that right. It’s an SSMS Snippet that generates a cursor. I use cursors so often that I decided to create a snippet so I don’t have to rewrite them or copy/paste from a template file all the time.

Yes, really. Don’t @ me, come at me bro, whatever it is the kids are saying these days. I am dependent upon cursors every day and would be lost without them.

Wow Andy, you must be pretty bad at your job if you’re running cursors all the time! Don’t you know that’s terrible for performance? What’s up with that?

If we’ve met, either in-person or virtually on the SQL Community Slack, you probably know that I manage an instance hosting mumblemumble thousand databases. I don’t mean “a couple” thousand databases; we’re looking at Michael Swart’s 10 Percent Rule in the rearview mirror. I regularly have to look for a particular value across the same table in a few dozen/hundred/thousand databases, or pull a report across as many databases, or run the same data change for a big list of databases. Most often, I’ll be given a list of databases or be asked “run this for all the databases that meet these criteria.” And the only way to do that easily is via a cursor because I have to first collect the list of databases from another table. There are 3rd party tools I could use but doing the setup to run against an arbitrary list of databases is tedious, error-prone, and I haven’t quite worked out a way to improve it yet.

Processing a table or result set RBAR is a performance concern. But to crank through a long list of databases and execute the same query against each it’s the only way to go, as far as I know. sp_msforeachdb doesn’t cut it for my purposes because I don’t want to hit every database on my instance.

My second piece of code is more of a technique or design pattern. In a stored procedure or large script with dynamic SQL, I’ll often create two variables (they’re parameters, in the case of stored procedures – choose a sensible default!) called @Debug and @ImSure. They’re just bit types but I use them to control the output of debgging information and code execution.

By doing this, I don’t have to comment/uncomment sections of code all the time just to see what dynamic SQL I’m generating. I also have a failsafe which prevents changes from being executed until I’ve made sure that everything is solid.

Those are probably the two pieces of code that I can share which I couldn’t be without. Honorable mentions which I didn’t write but find indispensable:

  • QUOTENAME – pretty basic T-SQL function but with all that dynamic SQL I’m writing, I need it to keep my SQL clean and safe.
  • dbatools – I’ve written about this PowerShell module quite a bit here but suffice to say for doing bulk administrative tasks, collecting metadata about the environment for analysis, and moving databases or entire instances around, it’s a lifesaver.
  • Brent Ozar Unlimited’s First Responder Kit – I run sp_blitzcache & sp_blitzindex daily looking for places we can tweak our code or indexes to make things run better.
  • Adam Machanic’s sp_whoisactive – Gives me a great lightweight snapshot of what’s going on right now.

Thanks for joining me on this T-SQL Tuesday!

T-SQL Tuesday #99 – Dealer’s Choice / sqlibrium

This month’s T-SQL Tuesday from Aaron Bertrand gives us a choice:

And I’ve got one, maybe two posts in progress on the first topic. Alas,

Thanks to Eugene Meidinger (b|t) for nudging me in the direction of posting this. Ever since this month’s T-SQL Tuesday was announced, work and non-work has been a complete whirlwind and I have failed miserably at reaching sqlibrium.

I’ll expand on this in later posts and link back. Really.

T-SQL Tuesday #96: Folks Who Have Made a Difference

T-SQL Tuesday logoIt’s time for T-SQL Tuesday and this month’s edition is hosted by Ewald Cress (b|t). It’s non-technical this month because we’re all recovering from PASS Summit. Ewald asks us to:

give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data.

This post is both difficult and easy. Difficult because there are so many people in the community whom I’ve learned from. But easy because there are a

Allen White (b | t)

I met Allen in the winter of 2011. Allen was a consultant and my employer engaged with his firm for help with a SQL Server migration/upgrade and DBA services. Allen introduced me to PASS, the SQL Server community, and helped me discover a passion for the field. I had previously dabbled with SQL Server, and tried to pick up what I could on my own and from random internet searches, but I spent a lot of time fumbling around and learning bad habits. Allen got me pointed in the right direction and drew me into the #SQLFamily. He is one of the first people I look for every time I go to SQL Saturday Cleveland or PASS Summit.

Kendal Van Dyke (b | t)

A little over a year after I met Allen, Kendal joined his team and I started working with him. Kendal became our primary DBA and when we had difficulties with SQL Server (or, more accurately, our application was giving SQL Server agitation) Kendal was our first call. I worked on a number of issues with Kendal; he knows SQL Server inside and out, and I was deeply familiar with the application that was giving it trouble so we made a good combination and I learned a lot about troubleshooting/debugging and some creative ways to resolve/work around application issues that can’t be fixed at the application layer. Kendal even hosted my first-ever blog post! Every now and then, something pops up on Twitter and we reminisce about the good ol’ days.

Matt Slocum (b | t)

I met Matt when I started attending my local user group meetings (or maybe it was at PASS Summit 2012, after which I started going to the local meetings). He somehow talked me into helping him run the local SQL Saturday, then talked me into taking over running SQL Saturday. As I described in my post earlier this year about how I became a DBA, he gave me that final nudge to say “yes, I want to be a DBA now. It’s time to make it happen.”

Chris Sommer (b | t)

I met Chris through our local user group and it turns out we know a lot of the same people despite never having worked together; there aren’t a lot of SQL Server folks in town! Chris took a new job around the same time I changed jobs earlier this year so we’ve been out for quite a few lunchtime runs and walks where we discuss various technical (and non-technical) challenges we face at work.

Chrissy LeMaire, Rob Sewell, Constantine Kokkinos, Shawn Melton

Newcomers to this list. This group (and really, I can’t single any one of them out) has welcomed me into the dbatools team and been very patient with me as I’ve learned GitHub, learned how to work within the goals/parameters of dbatools, and let me fire off a lot of pull requests just to do things like spelling corrections. It’s a whole new piece of the SQL Server community they’ve helped me get exposure to, and at Summit this year I was shocked when I had people coming up to me saying “hi, I wanted to meet you” or “hey, you work on dbatools? I need some help…” My Impostor Syndrome has been working overtime here; it’s been a few months now and I still can’t believe they let me work on this stuff. I’ve been honing my own PowerShell skills, learning more about dbatools, and reinforcing what I thought I already knew by reading so much of their code as I work on my various fix-ups – and even making real code changes!

To everyone named here, and the whole SQL family, thank you. If you have ever attended or organized a SQL Server-related event – SQL Saturday, Summit, User Group meeting, webinar, or blog party – you have had an effect on me. My career would not be where it is today without you.

T-SQL Tuesday #94 – Automating Configuration Comparison

tsql2sday-300x300This month’s T-SQL Tuesday is hosted by Rob Sewell and he’s posed the following question:

What are you going to automate today with PowerShell?

I’m cheating a little bit in that this is something I did a couple weeks ago, but it was immensely helpful. I’d been working on building out a new instance to migrate our test databases onto, but the developers had an urgent need to do some testing in isolation so they “borrowed” that new instance. But we had an additional requirement – the configuration needed to match production as closely as possible, more than our current test instance. Of course, I reached for Powershell and dbatools.

I started with Get-DbaSpConfigure to retrieve the settings available from sp_configure as these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either jumping through hoops or using SQL Authentication, I passed -SqlCredential (get-credential -Message "Prod" -UserName MySQLLogin) so I’d be prompted for that password instead of using Windows Authentication.

My configurations saved for reference, I can now look at one of the objects returned to see which properties need to be compared:

ServerName            : TEST1
ConfigName            : AdHocDistributedQueriesEnabled
DisplayName           : Ad Hoc Distributed Queries
Description           : Enable or disable Ad Hoc Distributed Queries
IsAdvanced            : True
IsDynamic             : True
MinValue              : 0
MaxValue              : 1
ConfiguredValue       : 0
RunningValue          : 0
DefaultValue          : 0
IsRunningDefaultValue : True

Looks like I want to be checking out ConfigName and RunningValue. ConfigName is the same name that you’d pass to sp_configure. PowerShell comes with a handy function Compare-Object which (you guessed it!) lets you compare two objects and reports the differences.

Hmm…that’s no good. I know there are differences between test and production – for one, production has about 24 times the amount of RAM test has. I took to the SQL Community Slack for help, and was reminded that Compare-Object by default doesn’t do a “deep” comparison on PSCustomObjects, so you have to specify which property(ies) you want compared. In this case, RunningValue. So, passing both ConfigName and RunningValue into Compare-Object (the former so that I’d know what was being compared), then sorting the output, I was able to readily see the differences.

The value corresponding to the left-pointing arrow is what came from the reference object, and the right-pointing arrow is the value from the difference object (which instance is the “reference” in this case isn’t terribly important, as long as you remember which is which). So MaxDOP and MaxServerMemory are both higher in production – which is expected.

If we really want to get crazy, we can even make this a one-liner. But I don’t recommend it.

Running this against my second test instance as well let me quickly deliver the news to the developers that the instances were configured as closely as possible, with any differences being limited to the hardware/environments they were in which is not something we were in a position to address.

T-SQL Tuesday #92: Lessons Learned the Hard Way

tsql2sday-300x300This month’s T-SQL Tuesday is hosted by Raul Gonzalez and he’s asked everyone to share things we might be a bit embarrassed about:

For this month, I want you peers to write about those important lessons that you learned the hard way, for instance something you did and put your systems down or maybe something you didn’t do and took your systems down. It can be also a bad decision you or someone else took back in the day and you’re still paying for it…

  • In the stress/performance testing portion of an upgrade of a critical system, we were short on disk space. So, rather than having a separate set of VMs for the performance testing (as we needed to be able to get back to functional testing quickly), we decided to just take VM snapshots of all the servers. Testing was delayed a day or two – but we didn’t switch off the snapshots. Then we started testing and performance was terrific…for about five minutes. Then everything came to a screeching halt. Panicked, we thought we were going to need a pile of new hardware until the VMWare admin realized that our disks were getting hammered and we still had those active snapshots.
    Lesson learned: If you take VM-level snapshots of your database server and let them “soak” for an extended period, you’re gonna have a bad time. Unless you need to take a snapshot of the host OS or instance configuration itself, use a database snapshot instead of a VM-level snapshot.

  • A couple of times, I’ve had under-performing VMs running SQL Server. As I hadn’t been involved in the configuration, I thought everything had been provisioned properly. Turns out…not so much. Memory reservations, storage configuration, power profiles, all set up for suboptimal performance.
    Lesson learned: Ask your VMWare admin if they’ve perused the best practices guide and review things yourself before going down the rabbit hole of SQL Server configuration & query tuning. If the underlying systems aren’t configured well, you’ll spin your wheels for a long time.

  • In doing a configuration review of a rather large (production) instance, I noted that at least one configuration option was still set to the default value – Cost Threshold for Parallelism was stuck at 5. Running sp_BlitzCache, I found that I had quite a few simple queries going parallel and huge CXPACKET waits. CXPACKET isn’t bad per se, but if you’ve got a low-cost query that’s going parallel and waiting on threads where it could be running faster overall single-threaded (verified this was the case for several of the top offenders), increasing the cost threshold can help. I did some checking, verified that it was a configuration change I could make on the fly, and set the value to 50.
    And then everything. Slowed. Down.
    When I made this configuration change on the test instance, it wasn’t much a problem. But that was a much smaller instance, with much less traffic. What I failed to fully comprehend was the impact of this operation. I overlooked that changing this setting (and a number of others I wasn’t aware of) blows out the plan cache. In the case of this instance, about 26Gb of plan cache. Not only was performance impacted while the plan cache was re-filled, we took a hit while all the old plans were being evicted from cache.
    Lesson learned: Even if it seemed OK in test, those “low impact” changes can have a much larger impact on production unless you can make test mirror production in every way. So plan when you make these changes accordingly.

We learn the most from our mistakes. We can learn almost as much from the mistakes of others. Learn from mine.

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

tsql2sday-300x300This month’s T-SQL Tuesday is hosted by Brent Ozar and he’s asked everyone to find interesting bug or enhancement requests in Microsoft Connect related to SQL Server.

The Connect item doesn’t have to have anything to do with T-SQL – it could be about the engine, SSRS, R, the installer, whatever. Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

Confession time: At the moment, I’m not a DBA (I’m a dev who occasionally does some DBAish stuff), and I don’t spend a lot of time on Microsoft Connect.

However, I do spend a bunch of time in Powershell, fetching data to process/analyze/mangle for people on an ad-hoc basis, running scheduled tasks, or doing  research trying to find things in an application’s code (stored in the database). And I frequently find myself using Invoke-SqlCmd.

I found two open items which, although addressed by Invoke-SqlCmd2, would be of benefit to the community at large if resolved in the SqlServer module.

  1. Powershell invoke-sqlcmd should support parameterized queries – It’s 2017 and we’re still reading about SQL injection attacks caused by developers improperly escaping text input. Parameterized queries have been around for a very long time and go a long way toward mitigating this vulnerability. Unfortunately, with Invoke-SqlCmd‘s current state it can only accept a string as the query to be executed (or an input file), and that query is frequently created by Powershell users by either concatenating strings or doing text replacement. Even in cases where I’ve created all of the content being concatenated or replaced, I still don’t fully trust my input.
  2. Invoke-Sqlcmd does not seem to meaningfully support the -Verbose or -Debug switches – Most Powershell cmdlets allow you get additional information about the cmdlet’s execution sent to the Verbose and Debug output streams, but Invoke-SqlCmd does nothing. Seeing the connection string and other details about the query’s execution would be helpful in some cases. I’d like to see this request expanded to add the -WhatIf switch as well so that when called from scripts that properly support SupportsShouldProcess, the query isn’t actually executed if that switch is specified.

I had planned on logging a Connect item very similar to Mike Fal’s (b|t) request for passing a connection string to Invoke-SqlCmd, but logging a duplicate would be pointless  that’s already been implemented (I warned you that I don’t spend a lot of time on Connect, I didn’t even remember it was out there)! I still wouldn’t mind being able to pass in just an Application Name to append to the connection string, but that may lead down a path of making too many options to fiddle with in the cmdlet. It’s a tough balancing act when you have an audience as large as the SQL Server team’s.

T-SQL Tuesday #83: Why Leave Well Enough Alone?

It’s 2016. So why are we still dealing with T-SQL code and design patterns that were designed 7 versions ago?tsql2sday-300x300


In the 15 years I have been using databases professionally, we’re still dealing with:

  • Peoples’ names are split into first name, last name and middle initial fields. Ignoring that this falls afoul of several of the myths programmers believe about names, the first name column was defined as CHAR(10) in a standard installation. How many characters are in the name Christopher (hint: I had to take off a shoe to count them all)?
  • Other arbitrarily short column sizes which cause problems as the system scales out in usage. For example, an event ID field that’s 8 characters: 2 letters and a 6-digit number which is used as a sequence. Guess what happens when you hit the millionth event in that sequence.
  • Processes originally developed as transactions (for good reasons), but not designed in such a way that they scale to today’s demands.
  • NOLOCK hints everywhere. It’s even in newly-developed code for this application.
  • Cursors used anytime a set of records has to be updated with a small bit of conditional logic built in. A set-based operation with appropriate CASE statements would work much better.

The primary system I deal with on a daily basis was originally developed as a DOS application and several of the above examples are drawn from it. Looking at the core tables and columns, it’s easy to identify those that began life in those early days – they all have 8-character names. Time moved on and the system grew and evolved. DOS to Windows. Windows to the web. But the database, and the practices and patterns used in the database, haven’t come along for the ride.

Data schema conversions can be hard and disruptive – you need to update your application, your stored procedures, and provide customers/users with a clean migration path. Code changes require testing. Complexity and cost grows every time you introduce changes. I get that.

But by not keeping up with the advancements of the platform your data resides on and ignoring the evolution of how to work with your data, you do your customers, users, partners, colleagues and yourself a disservice.

How do you improve this? I’m certainly not advocating for scrapping everything and rewriting all of your code. Complete rewrites are generally a bad idea.

What I am saying, however, is:

  • You need to be constantly watching the state of the platforms your software runs on. If you drop support for a particular version (say, dropping SQL Server 2005 support as Microsoft no longer supports it), start evaluating the 2008+ features that are now open to you.
  • Drop support for old versions of SQL Server. Don’t let the past shackle your future.
  • Get outside opinions from trusted sources. Whether it be from your local user group, a short consulting engagement, or bringing in new people. But most importantly, when you seek advice, make use of it. Don’t ask for advice and then ignore it.
  • Don’t accept the status quo. Anytime you’re working in a piece of code, review the whole thing. Can this section be cleaned up? Is it even needed anymore? Has the system scaled in usage/data volume that it needs to be re-thought entirely? Have you learned something new from your favorite SQL Server blog or a SQL Saturday event that you can apply to it?

That last point is where everyone responsible for an application or database can make the most impact. To co-opt Baden-Powell’s last message to the Boy Scouts of the world: Leave the code a little better than you found it. If you do this every time you touch a component of your database, you’ll make enough incremental updates that these 15 year old problems will go away and stay away.

T-SQL Tuesday #61 – Giving Back

T-SQL Tuesday LogoWayne Sheffield (b|t) is hosting this month’s T-SQL Tuesday and his topic is Giving Back to the SQL Community. More specifically, he’s asking how each of us is planning on giving something back to the SQL Community in 2015. He offers up a few suggestions, so I’ll start by addressing those and then move on to additional ideas.

  • Are you going to start speaking at your local user group?
    Yes, I expect that by the end of 2015 I will have spoken to our local chapter at least once. I spoke to various groups at work in 2014 and plan to continue doing so in 2015 as well.
  • Perhaps step up and help run your local user group?
    I was named the Vice President of our local chapter a couple months ago, and I will continue in that capacity.
  • Do you want to start becoming an active blogger – or increase your blogging?
    Yes! At the time of this writing I’ve only published 7 posts here, and I have 6 others in various stages of preparation. I have some ideas brewing, I just need to get things written and then actually press that Publish button. Part of it is fear/insecurity, and I need to get out of my comfort zone a little and Just Do It.
  • Do you plan on volunteering your time with larger organizations (such as PASS), so that SQL Training can occur at a larger level?
    If I have the opportunity to attend PASS Summit in 2015, I will volunteer at the event. When the call for pre-event volunteers go out, I’ll look at what’s needed and try to step a little out of my comfort zone & do something there as well.
  • Other ways of contributing
    • For the 3rd year, I will be helping to organize and run SQL Saturday Rochester in 2015. If you’re reading this, you probably know about SQL Saturday, and have probably even been to one. Next time, bring a friend!
    • I’ve been promoting PASS and our local chapter for a while at work and will be a more vocal in 2015. There are a lot of people with knowledge and experience they can share who aren’t even aware that PASS and the local and virtual user groups exist. I want to help bring those people into the community.

T-SQL Tuesday #58 – Passwords

T-SQL Tuesday LogoThis month’s T-SQL Tuesday topic is passwords. I’m neither a DBA nor server/system admin, so the only passwords I get to manage are my own. But there’s still lots to talk about. Passwords (or rather, weak passwords) have been in the news a lotover the past two weeks, so it’s timely.

This is the password story I’d like to tell my kids, but they’re too young to understand yet.

What’s Your Password?

I can count on both hands the number of accounts I have actually memorized the password for.

  • Personal Laptop
  • Personal email (2 accounts)
  • Active Directory (work)
  • One non-production service account
  • 2 non-AD-integrated applications
  • Amazon
  • 1Password

Pretty much everything else is a very random string that I have no hope of memorizing. For example, n9;r27LBL8x2x6=X. It’s that last item above that lets me get away with it. Between password complexity rules, the increasing sophistication of attackers, and the frequency of major data breaches, it’s almost impossible to get by without some kind of password manager. You need to be changing your passwords regularly, and using strong ones. 1Password helps me with both of those; it shows me how old each password is, and it generates good, random passwords as seen above. All I have to remember is my master password. I rarely type a password now; it’s copy/pasted from 1Password, or automatically entered thanks to the browser extension.

But that’s not enough. A lot of websites insist upon providing more information for account “security” such as the name of my first pet, or what my first car was. But answering those questions truthfully doesn’t provide as much security as one might think, so I use 1Password to generate random strings for these answers and store those Q&A pairs along with the credentials.

Is that enough? No! Even if you did all of that, your credentials were still easy to capture for about two years thanks to the Heartbleed SSL bug.

More Layers

Security should be like an ogre…er…onion. You need layers. Passwords aren’t enough. More and more websites and services are offering Two-Factor Authentication (2FA) now, but they aren’t making it very well-known. Google, Dropbox, WordPress, Evernote, Facebook, Microsoft and GitHub (and that’s just the list I’ve got registered on my phone) will let you further secure your account by requiring you to enter a second code after your password, either one sent to your phone via text message (or phone call) or automatically generated via an app like Google Authenticator (not unlike a SecurID token). It’s an extra step, but it makes things a lot safer. Even if someone were to get your credentials in a Heartbleed-type attack, they’d be pretty useless with 2FA enabled – at least on that site.

Be Careful Out There

Many years ago when I was in college, I thought my password was safe. I wasn’t sharing it with anyone, and it was reasonably complex – for the time. Then over one Christmas break, my account was compromised. I was checking my mail via an unencrypted POP3 connection. The password got sniffed, and someone got into my account. Fortunately, no damage was done but the lesson was learned. With so much of our identities, personal & financial lives kept behind these virtual doors, it’s vital that we take every possible precaution in securing those accounts (unfortunately, I know too many people who are still not doing this). Sure, POP3 was quick & convenient, but it was extremely dangerous.

There are still websites/services that aren’t so careful. If you see a website that limits your password to anything less than about 30 characters, is not case-sensitive, or doesn’t allow you to use certain characters, there’s a chance they’re not storing passwords safely (I won’t get into that with this post, as I’m sure another T-SQL Tuesday author will dive in and do it better than I would). And if any website sends you your password (the one you created for the site) via email, run away (and drop a line to Plain Text Offenders) because they aren’t doing anything to protect you.

Wrap Up

  • Make sure the login form you’re using is properly secured
  • Use strong passwords
  • Use Two-Factor Authentication everywhere you can
  • Watch out for careless site operators
  • Don’t use weak passwords & skip other security measures because “it’s not convenient”

But just so we’re clear: Unlike the hundred-plus celebrities who were compromised, there’s nothing on my iCloud account that anyone should be subjected to.