SQL Server - How to disable “sa” login minimizing impacts

Views: 595
Reading Time: 6 minutes

Speak, my dear readers!
In this article, I would like to extend the security issue a bit and share with you how to disable “sa” login to minimize impacts. As I already commented in the article SQL Server - How to enable / enable sa user, it is a good security practice to keep user “sa” deactivated and renamed, since that user is in role sysadmin (cannot be removed), cannot be deleted from the instance and is a default user, ie is present on any instance of SQL Server, which makes this user the preferred target for potential attackers. In fact, this is one of the security features of FREE SQL Server Checkup that Fabrício Lima is offering to want to know the company.

For a few days I left public for the Web, an instance of SQL Server and Power BI Report Server to do some testing. After a few days, I saw in the SQL Server error log that they were already trying to break into my base. Imagine which user you tried most to use to access my bank?

Want to see how these statistics look in your instance? Just run the script below:

Now that I have briefly summarized why this user should be disabled and renamed, I will explain how we can do this in your database. In theory, it is extremely simple:

Or even through the Management Studio interface (SSMS):

The problem is that many people are insecure to do this operation in their database and end up impacting the environment. My idea in this post is to help you identify and minimize these potential impacts so you can apply these security settings to all of your environments.

Application using SA

The first item to check is if there is any application that uses the user "sa" to connect to the database (believe me, it exists). The easiest way to identify this is by questioning the application vendor or development team.

One way that can help you validate this information and even be able to identify if this scenario is occurring in your environment is by consulting DMV sys.dm_exec_sessions:

Result:

Databases where SA user is owner

This is a very common concern among DBAs that if you deactivate the SA user and rename them, there may be a problem with databases where the SA user is the owner. However, you can rest assured that there is no problem doing this. I've done a lot of testing in many test and production environments, and you disabling the SA user has no impact on that.

To prove what I am saying, here is a demonstration:

Result:

Jobs where SA user is owner

As with the SA user who owns a database, there is no impact on running SQL Agent Jobs by renaming and disabling the “sa” login. They can be carefree and do not have to quit by changing the owner of all jobs to rename / deactivate sa.

Query used for demonstration:

Result:

Linked Server

This is probably the most difficult validation to do in the environment, although it is also quite unusual. If there is a Linked Server that points to your instance, where the connection to the bank is made using the “sa” user, fixed in LS, you may have problems disabling and renaming that user.

This is difficult to validate because you have to go into all instances of your environment that might have some linked server pointed to that particular instance and validate if this scenario is occurring:

Using Linked Server as a fixed user on the connection is not very common, as any user on the remote instance could run ANY COMMAND on the target instance, since the connection is arriving as “sa”, which is sysadmin, but it is a validation that should be made to ensure that the change will not impact.

You can use this query to make this check easier:

Objects with IMPERSONATE using SA

It is quite common to see procedures and objects that use the EXECUTE AS command to perform tasks as if they were other users, especially to allow a low-privileged user to execute routines and commands that would require more privileges than they have, thus elevating them. of privilege. If you would like to dig deeper into IMPERSONATE and elevation of privilege, I recommend reading my article. SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user).

To reassure you, please be aware that you cannot use EXECUTE AS USER and impersonate the “sa” user as he is a “special” user, which is less of a concern to you.

Other services

And lastly, so that you can make your change in peace of mind, I recommend that you validate whether there are any Windows services, Reporting Services reports, any Power Shell routines, monitoring tools, or any processes outside of SQL Server that might be using the sa user in your environment.

Because these are external processes and may be specific to your company, there is not much “cake recipe” to handle these situations, but it is not common for a DBA to allow a service to use the “sa” user for such operations, so it must be very Hard to find this scenario.

Rest assured that you disable and rename the “sa” user as this is a good security practice for your environment. Remember to monitor the SQL Server log after this change to see if login failure messages are occurring using “sa”. I made available a query that can even automate this at the beginning of this post 😉

If you identify a problem, you may have discovered a hacking attempt or a non-standard process in your company that must be urgently changed to use another user to connect to the database.

Well guys, that's it!
I hope you enjoyed this tip, rename and disable the user "sa" in their environments and until next time!