TSQL Tuesday

T-SQL Tuesday #152 – The crazy login client

This month’s T-SQL Tuesday is hosted by Deborah Melkin(b|t), and she has an interesting topic. She wants us to write a rant on a scenario we encountered at a client that has the common ‘it depends’ stance go right out of the window..or in other words, something that is so bad that there is no way it should be sustained practice, ever.
Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins. We would get an excel spreadsheet with *hundreds* of names, and what kind of server/database/level of access was needed by the person. We then had to get on the server and painfully create these logins with the right access. We had to set up the password to be changed to something else on first login and set up the login itself to expire in a week. This was an entire day, sometimes two days’ worth of work to set up. And they *paid* us to do that! We tried some degree of automation, like writing some code to read the excel sheet and automatically create these logins. But, it didn’t work too well – because there would be some access needed that wasn’t the norm before, or some new database, or something or the other. Surely, if you want to outsource your work and care that much about security – there could be better ways? Some of the conversations/reasoning we had with this client, trying to explain *why* this approach was so problematic even if they were crazy enough to pay us to do it – as below.
1 Outsourcing work to a place with so much turnover can cause a ton of problems. No one person knows or is responsible for what came before them.
2 Having this many SQL Server logins is also a huge problem – there is no guarantee that people aren’t sharing their passwords (we actually caught several people doing this).
3 Having two third-party companies (one was us and the other was the contracting company) – with you the customer in the middle is in itself problematic. Ultimately your data and its security is your problem, not either of these company’s.
4 The ‘it depends’ clause does not belong at all in a scenario like this. Invest in paying people well and they will take good care of your data. Operate on principle of least privilege and your data will be more secure. Don’t pay someone to do trashy work with the line assuming that they will do it for money.
Needless to say I was really happy moving on from doing this sort of work. And am extra careful when I hear of companies outsourcing their stuff for cheap – you can run into all sorts of ridiculous scenarios like we did here. Thanks for reading.

2 thoughts on “T-SQL Tuesday #152 – The crazy login client

  1. Sometimes we are extremely limited by the imagination of our managers. “In a hierarchy, every employee tends to rise to his level of incompetence.” (Laurence J. Peter, The Peter Principle). Thanks for writing!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.