Today we have a guest editorial as Steve is on vacation.

I will start with a short explanation for those unfamiliar with this topic. SQL Server has two avenues by which users can access the database and login. One is SQL Server Authentication where a SQL Login or user is created and a password is stored and managed by SQL Server. The other method is to use Active Directory users and groups for login and control user access.

I remember back with SQL 6.5 when SQL Authenticated Users were the only real option. Windows 95 was still the main operating system and many people were still using Novell Netware for networking. There just weren’t a lot of options back then. Next came Windows NT, and soon after Windows 2000, and Active Directory became a way of authenticating users. All of a sudden by SQL Server 2005 everyone was pushing for people to be using windows authentication and network users and groups instead of SQL users to login in to SQL Server.

So, 2005 is already 13 years ago, and I would guess a lot of us are still using and supporting SQL servers that are running in mixed mode (allowing both SQL and AD authentication). Why are we still using SQL Authenticated users? Certainly part of it may be because they are easy to setup. I would guess most DBA’s don’t have access to manage their company’s Active Directory and cannot create new AD groups or users. Also, I suppose there are some AD Domain Admins that just don’t want to create a new group and manage which users are in that group.

All the same, I would argue that the concerns over security should push companies to using AD authenticated users and groups for access to SQL server. We know that SQL authenticated user names and passwords are in clear text in connection strings. These connection strings are often stored in plain text config files. This is not secure and we know since these usernames and passwords are hardcoded it causes us to never, or only with great pain and hardship, change the SQL user’s password.

I would encourage all of you who are managing systems with SQL authentication users to think seriously about trying to change it. I have found very few situations where SQL users should be used. Make sure you are not causing your systems to be less secure just for convenience or because it seems like too much work to fix it.

I admit, the company I currently work for does still run their SQL Servers in mixed mode and there are still a handful of SQL users being used. Still the majority of access is AD authenticated and we are working to remove the last pockets of resistance. How about you? Does your company run their servers in mixed mode? What keeps you from removing SQL authenticated users from your systems?

Join the debate, and respond to today's editorial on the forums

The correct answer is:

This QotD was inspired by the SymmetricExceptWith method of the HasSet class in C#. The same QotD solution by using C# is the following:

And of course there are other solutions by using t-sql for the same problem. Expect them in the comments ;)

References:

click here

click here

click here

» Discuss this question and answer on the forums