Security - SQL vs Windows

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