SQL SERVER – Windows Authentication or System Admin Account (SA)

I like to say that I am SQL Server Performance Tuning expert but often during the conversation, I end up discussing security as well. Recently I was asked about my preference for Windows Authentication or System Admin Account (SA) during one of the recent Comprehensive Database Performance Health Check.

My opinion about this subject is very much clear – try to use Windows Authentication as much as possible. Usage of mixed mode authentication is recommended for legacy applications and non-Windows users. I recommend this because when multiple users use the same login, it becomes difficult to know which “NT User” actually did an operation on the server. I recommend this for SQL Server Administration too, instead of using the default SYSADMIN SA account. Try not to share the SA account across multiple users.

SQL SERVER - Windows Authentication or System Admin Account (SA) securityquestion

We can change the authentication type on the Server Properties > Security > Server Authentication; this setting can be found as shown in the above figure.

You can also disable the SA login inside SQL Server. This is because the user account can be easily guessed by anyone working with SQL Server. Ideally, rename or disable this account and use some other account to administer your SQL Server instance. The following command can help you DISABLE / ENABLE the SA user account.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – Enabling Older Legacy Cardinality Estimation
Next Post
SQL SERVER – How to Invoke SSMS Help From Command Prompt?

Related Posts

Leave a Reply