Security is a very important part of every DBA’s role. If you are not clued up on security best practices or don’t secure your SQL Servers, you should be concerned as it could end up costing you your job!
I have worked in organisations where the painstaking task of managing SQL Server security was my responsibility and others where thankfully we have made use of Windows Authentication. This in my opinion is a much better approach as it reduces the administrative overhead weather the task be carried out by an IT Operations team or in most cases by you as DBA’s.
There are many factors to consider when deciding whether to use SQL or Windows authentication and in some cases even both, the biggest win for me however is the administrative overhead as I mentioned before and I will show why below;
Take the following example of a small development team comprising 9 developers as below;
DevUser1
DevUser2
DevUser3
DevUser4
DevUser5
DevUser6
DevUser7
DevUser8
DevUser9
The developers are split into 3 teams as below;
Development Team 1
DevUser1
DevUser2
DevUser3
Development Team 2
DevUser4
DevUser5
DevUser6
Development Team 3
DevUser7
DevUser8
DevUser9
Development Team 1 comprises of the most experienced and trusted developers, Development Team 2 comprises of competent developers who have some experience but not as much as those in Development Team 1 and Development Team 3 comprises of new or junior developers who are still learning the ropes.
There are 3 SQL Servers in the company as below;
A Live SQL Server - LIVESQL1
A UAT SQL Server - UATSQL1
A Dev SQL Server – DEVSQL1
Each SQL Server has 3 databases as below;
DBX
DBY
DBZ
The company requires the developers to have the below access;
Development Team 1
DEVSQL1 – membership in the db_owner fixed database role for every user database.
UATSQL1 – membership in the db_datareader and db_datawriter fixed database roles for every user database.
LIVESQL1 – membership in the db_datareader fixed database role.
Development Team 2
DEVSQL1 – membership in the db_owner fixed database role for every user database.
UATSQL1 – membership in the db_datareader and db_datawriter fixed database roles for every user database.
LIVESQL1 – NO ACCESS
Development Team 3
DEVSQL1 – membership in the db_datareader and db_datawriter fixed database role for every user database.
UATSQL1 – NO ACCESS
LIVESQL1 – NO ACCESS
Now let’s consider what would be required for SQL Authentication;
DEVSQL1
9 Logins
27 Users
36 Role memberships
UATSQL1
6 Logins
18 Users
36 Role memberships
LIVESQL1
3 Logins
9 Users
9 Role memberships
TOTAL
18 Logins
54 Users
81 Role memberships
And for Windows Authentication;
We create a Global Security Group in Active Directory for each of the Development Teams as below;
Development Team 1
Development Team 2
Development Team 3
We then add each of the developers to their respective groups. Once this is done we would require;
DEVSQL1
3 Logins
9 Users
12 role memberships
UATSQL1
2 Logins
6 Users
12 role memberships
LIVESQL1
1 Logins
3 Users
6 role memberships
TOTAL
6 Logins
18 Users
30 Role memberships
As you can see from these figures making use Windows Authentication means we have 65% less administration to deliver a security solution for our SQL Servers, based on this data we decide to go with Windows Authentication. Moving forward as part of the IT new user process for developers we inform the IT Operations team that they are to add new developers to the Development Team 3 Active Directory group. This means that they automatically have permissions on SQL server.
Now consider the following scenario; over the next 6 months the company’s size explodes and the number of developers rises from 9 to 99. There are no new servers or databases required. Since we decided to go with Windows Authentication there is zero administrative overhead for us as DBA’s providing the Teams / Active Directory structure remain the same (which for the purpose of this post assume they have).
But what if we would have gone with SQL Server authentication? Well let’s look at the figures, 90 additional users equate to an additional….
DEVSQL1
90 Logins
270 Users
360 Role memberships
UATSQL1
60 Logins
180 Users
360 Role memberships
LIVESQL1
30 Logins
90 Users
90 Role memberships
TOTAL
180 Logins
540 Users
810 Role memberships
This is a lot of additional work which could be reduced by using scripts yes, but is still additional work. I am currently defining standards for my current employer and will be using these statistics as one reason of many reasons why we should use Windows authentication over SQL authentication!
Cheers
Chris
