SQL Server - How to enable / enable sa user

Views: 2.354
Reading Time: 4 minutes

Hey guys!
All in peace right?

Through this article, I am going to open a new category on my blog called “What not to do,” which are some database management bad practice articles that I will start to gather here and thus, show you all that you shouldn't 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 CastroIf you install SQL Server using Windows Authentication Mode, the "sa" user is disabled by default. If you select the "SQL Server and Windows Authentication Mode" option (also known as Mixed Mode) during installation, you will have to set the sa user password and it will be enabled 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, enable the "SQL Server and Windows Authentication mode" option 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 enable user sa. To do so, expand the “Security” and “Logins” folders of the “Object Explorer” window and right click on the user “sa”, who will have an icon and a small red “x”, indicating that this user is disabled:

After clicking the "Properties" button, you will see the screen with the user properties "sa". In this screen you will be able to set a new password for the sa user as well as set Enforce password policy, Enforce password expiration policy and set if the user should change the password next time. login (User must change password at next login).

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

After confirming the changes by clicking the "OK" button, the user "sa" 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 have not activated the user "sa" in your environment and that you enjoyed this article.

A hug and see you next!