As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

Do the different ways of accessing SQL Server (Windows Athentication, SQL Server authentication, certificate or key) have differing authentication and authorization performance? If so, can you order the list?

From my experience, there is no performance difference between the different methods for authentication.  That said, there are factors in any environment that can affect performance.  The number of logins on a server, the workload for the Active Directory server, the network congestion between the SQL Server and Active Directory server, the volume of activity on the SQL Server, the amount of memory pressure on the SQL Server instance, and probably dozens more.

If your application is tuned to the point where the speed of authentication is the performance bottleneck, then there are probably other things to look at besides the speed of the authentication performance.  Namely, are the applications authenticating more often than necessary.  Or in other words, is connection pooling in use and properly configured?  If not, then this is having a much bigger impact on authentication performance than the 1 or 2 ms that might be garnered by shifting from one authentication mode to another.

When deciding how to authenticate an user or application to the SQL Server instance, use the authentication method that makes sense for the use case.  When considering which authentication method to use, look at some of the following to guide you on the correct authentication method:

• Windows Authentication: If the user or application is within the domain, then default to using Windows Authentication.  This makes it easiest on the users and passwords don’t need to be managed besides the one for the domain.  Also, users are less likely to share access, since it is tied into their network logon access.
• SQL Server Authentication: Best for use with third-party applications or access through non-SQL Server data sources.  When an application cannot utilize a Windows User for a service account or a user is not part of the domain, then SQL Server authentication is the route to go.  When compared to previous versions of SQL Server, SQL Server authentication is much more secure than it used to be.
• Certificate: Certificates provide database level security control.  A certificate can be created and then linked to a logon; which can in-turn be used to provide bypass security to elevated permissions.  For instance, a stored procedure can be signed with a certificate to allow a user to access commands such as DBCC CHECKDB on a single database, while preventing access on other databases.
• Symmetric/Asymmetric Keys: Keys are use in SQL Server for encrypting and decrypting data that is being transmitted from one place to another.  The transmission might be for column-level encryption, ensuring that while on disk data is encrypted.  It can be used for database mirroring to ensure that packets between the two instances of SQL Server cannot be intercepted or corrupted.  Whether the transmission is within the SQL Server or between instances, keys provide the information necessary to access the received data.

As the descriptions note, only Windows and SQL Server authentication will be used by users to access SQL Server instances.  The other two are for use cases outside of connectivity for users.  But, as described, they are extremely useful and necessary for properly securing a SQL Server environment.

Summary

The performance of the authentication methods isn’t something that we necessarily need to be concerned about.  Much more important than that is using the proper authentication method for the use case.  By doing that, you’ll have SQL Server properly secured and can focus on other areas of performance that will provide a much higher return on investment.  What do you think, should there be a focus on authentication performance?