I have been on holiday for the past two weeks but just before I left the subject came up with a client regarding authentication methods in SQL Server. Whether you are logging in using SQL Server Management Studio (SSMS) to make queries or configuring an application to work with SQL Server the authentication method is important to consider.
By authentication we mean a process a user must complete to gain access to a system. SQL Server roles and rights fall under authorisation – determining what access a user has in a system – which I’ll discuss another time.
When you install a fresh SQL Server instance on Windows you’ll be prompted regarding the authentication mode to have it in:
- Windows – Default and enables Active Directory authentication only.
- Mixed – Enables both Active Directory and SQL Server’s built in authentication method
You also get to specify what AD users and groups to add to the sysadmin role. When I do this for clients I make sure to identify the users or DBA groups to add. Adding the domain admins group I would advise avoiding unless as a last resort because given that sysadmin has read permissions on data in all databases there should still be a separation of roles between an IT admin and someone in an organisation who has a both requirement & trust to access that information.
If you change your mind later on after the install you can switch between modes in the instance properties in SSMS or using the registry.
I recommend if possible that an SQL Server instance uses Windows Authentication. This is because it’s simply put the most secure form of authentication of the two.
Windows Authentication makes use of an organisation’s existing Active Directory user directory. This mode is always active and is often referred to as “Trusted”. I’ll not go into the exact workings of Active Directory authentication in this post but the critical point is that SQL Server is not performing the authentication itself. Instead the authentication is done against Active Directory by passing back authentication tokens. A slight downside would be that this creates a dependency on a working and redundant Active Directory Domain Services setup (read: ideally two servers with AD replication working and healthy).
When using an SQL Server authenticated login this will use SQL Server’s own authentication scheme which is much weaker than Active Directory. An example of how much weaker this is would be that in the communication between the client and SQL Server instance you’ll find the password sent in plain text.
Special Note On The sa Account
There’s a default account in SQL Server which by default and on installation is named ‘sa’. This is the most powerful account in any SQL Server instance and has the SID 0x01.
I often get asked the question either in handover or in a support query about the sa account and my answer is always that I don’t use it. Applications, MSPs and consultants that ask for this account lose a few points for doing so. Using the sa account to configure an application or for general use is an absolute no.
I highly recommend leaving the sa account disabled. It’s also recommended to rename this account as an attacker can usually assume there is an account named sa on the instance to try for.
As a safer alternative giving users named logins is highly recommended for accountability purposes instead of a shared account.
Note that if you install SQL Server in Windows Authentication mode the sa account is still created but with a random password and left disabled.
