Those of us that write software for SQL Server or administer SQL Server instances are in the middle of the password challenges for security. We often require some sort of password for our users, storing this inside our database, but we also deal with the hassles of managing our own passwords on the platform, for administrators, applications, and services. We may handle one set of passwords differently from others, or perhaps even handle every type of password differently.
When I started working with computers, most of the systems were single user. We didn't have logins on the old Vic-20s or Apple IIs or TRS-80s. In fact, security was keeping control of the floppy disks or tapes with our code on them. Over time, I moved to multi-user systems in university, where we needed a login and password, but often we used the same random password we were assigned all semester. A few systems asked us to change periodically, which later became a requirement (and best practice) in various companies. In fact, at one company, the general password policy was 120 days for users, but 30 days for sysadmins, including service accounts.
Over the last decade, I've seen various advice and changes in how platforms and software handles passwords. We have two factor authentication (2FA) taking hold for some, longer password fields for many, complexity requirements that are all over the board, biometrics being implemented in mobile systems and certainly still many poor practices highly visible in large organizations. There are many designs and practices talked about, but I rarely see a good, comprehensive list of well thought out recommendations.
Until this week. Troy Hunt wrote a great piece on modern authentication guidance, and it's one that got me thinking. We should be both allowing users more freedom, and preventing stupidity, like reusing cracked passwords. We shouldn't be forcing changes, allowing password managers, and getting rid of specific complexity requirements. He gives reasons and provides references from large national organizations that study the issues. Some of the choices aren't ones I've made in the past, but they are ones I'd like to see implemented in the future.
I'd really like to see a shorter list of best practices for software authetnication that developers can reference easily, with supporting documentation and better examples. This is one of the issues I've had with Microsoft's Secure Coding site. If a developer wants to get started and upgrade their code, they don't have good examples. Just lots of descriptions of what is good, but not a framework of schemes that help them actually start writing code.
Certainly applications should separate their authentication schemes and be able to upgrade them, especially as our guidelines change. If we needed to implement 2FA, how easily could any of you upgrade your authentication? Perhaps more of a question, if you wanted to have limited access when there are red flags during the login, can you change your security model? To me, that's a hard one, but one that we might want in more software.
I would also argue that SQL Server should implement more flexible security schemes in its own framework. Why shouldn't we have 2FA? Or perhaps a better way of enforcing passwords outside of what Windows allows. Could we attempt to verify a sysadmin login, perhaps limiting them to read only informaiton, or system functions outside of databases until some second verification is performed? For that matter, why can't we have a sysadmin that doesn't have the ability to read from any tables in any databases? I've love to see a security model that implemented those restrictions.
Security, authentication, and controlling data access is always a challenge, but I'm hopeful that we will see more resource and options, as well as guidance and sample code, in the future. We rely more and more on data in our digital databases, so let's try to provide more security as we move forward.
