SQL Server - How to enable / enable sa user

Speak guys!
All in peace right?

Through this article, I will inaugurate a new category on my blog called “What not to do”, which are some articles of bad database administration practices that I will start to gather here and thus, demonstrate to you everything that should Be done.

To debut this category, I will write here about a bad practice that I see a lot of people using and asking groups how to do it, which is to enable the sa user in the SQL Server instance.

Why is the SA user disabled by default?

As noted in the comments by my friend Edvaldo Castro, if you install SQL Server using Windows Authentication Mode, the “sa” user is disabled by default. If you select the option “SQL Server and Windows Authentication Mode” (also known as Mixed Mode) during installation, you will have to set the password for the sa user and it will be activated by default.

For security reasons, it is strongly recommended that you keep this user disabled as you are an administrator profile user (belongs to server role sysadmin), having permission to perform any action on the instance, and a default user, which comes pre-created in all SQL Server instances. Because of this, most attacks on SQL Server instances are performed through attempts to access the instance using this user.

Unfortunately, we eventually found several systems and applications that insist on connecting to the database using the sa user (Why ?? Why ??), causing a major security breach in the database, making it easier for crackers to hack into the database. your bank and leaving the DBA unsure what to do (especially when it does not have the option to bar that system from going up).

As we can see from the image above, SQL Server allows us to enable an option that generates a failed log on connection attempts, which can help us identify if our base is experiencing brute-force intrusion attempts. However, unless you have active monitoring of these authentication failures (which is not very common), you will only realize that this is happening hours, maybe days, after the attacks. And by then, it may have been too late.

For this reason, a good way to try to make hacking more difficult is to not let the potential attacker know the name of any privileged user in the instance. And activating user SA is just the opposite of that.

I want to take this risk. How to activate user sa?

Well, given the warnings above, and if you really want to take this security risk, I will now demonstrate how to enable the SA user on your SQL Server instance.

If you have not enabled mixed authentication mode (SQL Server and Windows authentication mode) and your instance is only allowing logins using AD authentication (This is a good security practice, but not always feasible), you will need to enable Mixed Mode Authetication.

To do this, just right-click on your instance and select the “Properties” option:

After that, activate the option “SQL Server and Windows Authentication mode”, in the security options:

After committing the changes, your instance is already allowing login using both Active Directory (AD) users and SQL Server users (such as sa). Remember that the SQL Server service needs to be restarted for the changes to take effect.

If you want to apply this change using T-SQL codes, just use this script:

Now that you have enabled login using SQL Server users, it is time to activate the sa user. To do this, expand the “Security” and “Logins” folders in the “Object Explorer” window and right-click on the “sa” user, who will have an icon and a small red “x”, indicating that this user is disabled:

After clicking on the "Properties" button, you will see the screen with the properties of the user "sa". In this screen, you can define a new password for the user “sa” and also define password complexity policies (Enforce password policy), password expiration policies (Enforce password expiration) and define whether the user should change the password in the next login (User must change password at next login).

In the "Status" tab, you can activate / deactivate the login of the user "sa", as shown below:

After confirming the changes by clicking on the “OK” button, the “sa” user will be active and can now be used normally for encroach access your instance.

If you want to enable the SA user using a T-SQL script, just use the script below:

Good guys
I hope you haven't activated the “sa” user in your environment and that you liked this article.

A hug and see you next!