Executing a health check analysis of the SQL Server instance Security category – Accounts

When it comes to using the SQL Server instance, a DBA must ensure no one unauthorized, or outside of the organization, should have any privileges that will allow him to change any SQL Server configuration. Preventing this scenario will provide an additional layer of security that will result in decreasing the level of SQL Server’s vulnerability and its exposure to malicious attacks.

Following health check rules will test the existing SQL instance accounts and its permissions:

  • SQL Server sysadmin account
  • Unsafe permissions assigned to Public account
  • Guest user access
  • Public or guest have fixed roles
  • SQL Server Agent sysadmin permissions
  • Databases owned by sysadmin have trustworthy bit off
  • sa Account should be renamed or disabled
  • User-created ‘sa’ account should not exist
  • BUILTIN/Administrators have sysadmin privileges
  • Foreign domain logins
  • Blank passwords
  • SQL Authentication
  • sa Account Password Policy
  • SQL Logins Password Expiration
  • DISTRIBUTOR_ADMIN Login
  • Must_Change not configured for SQL Logins
  • xp_cmdshell Enabled
  • xp_cmdshell Proxy Account Exists

Executing the listed set of SQL Server instance rules is rather an easy task. Simply, from the Health tab within the ApexSQL Manage, click the Run button, and in the Run health check window, choose SQL instance(s) to be examined. From the Rules tab, check the box in front of the appropriate SQL Server account rules and click the OK button:

Choose SQL Servers and rules

Once the selected set of health check rules is executed, the Result summary window is shown with health check statistics of the examined SQL Server instance(s). Click the Close button to exit:

Result summary in ApexSQL Manage

The following is a brief explanation of the mentioned rules.

SQL Server sysadmin account

This health check rule checks if the SQL Server has at least one sysadmin account.

Since sysadmin role members have irrevocable administrative privileges on all SQL Server databases and resources, it is always advisable to be extra cautious who ends up granted with this “mighty” permissions.

However, the best practice here is to have at least one sysadmin account on the SQL Server, since at least one capable individual should be managing a SQL Server instance environment:

SQL Server sysadmin account rule

Unsafe permissions assigned to Public account

This rule checks if the public account has been granted dangerous permissions via transact SQL scripts or stored procedures.

The best practice in this specific situation is to remove all permissions from a public account, except for “Connect” and “View any database” privileges:

Unsafe permissions assigned to Public account rule in ApexSQL Manage

Guest user access

This health check rule checks if a guest user has proper access.

The following SQL Server system databases: master, msdb, and tempdb databases should have guest user enabled, while the model database should have it disabled.

If this rule fails during the high-level analysis on the targeted SQL instance, then fix SQL script is available to remedy this issue and grant the specific guest user with access:

Guest user access rule in ApexSQL Manage

Public or guest have fixed roles

This high-level analysis SQL Server instance rule checks if public or guest user accounts are members of any fixed database roles: db_owner; db_securityadmin; db_accessadmin; db_backupoperator; db_ddladmin; db_datawriter; db_datareader; db_denydatawriter; db_denydatareader.

The best here would be to avoid usage of guest accounts, or at least grant them with minimal permissions:

Public or guest have fixed roles

SQL Server Agent sysadmin permissions

This health check rule determines if the SQL Server Agent service account is a member of the local Administrators group.

If the SQL Server Agent account isn’t a member of local administrators, it may have insufficient permissions to execute agent jobs, which would take some time for DBA to resolve the issue.

This rule ensures such issues are resolved in a timely manner.

If this rule fails, a user is granted with the following message:

“SQL Server is not a member of local Administrators group”

The best practice, in this case, would be to award the SQL Server Agent service with local system administrator:

SQL Server Agent sysadmin permissions rule

Databases owned by sysadmin have trustworthy bit off

This high-level analysis SQL Server instance rule determines whether there are any trustworthy databases that are owned by system administrators.

The TRUSTWORTHY Database property indicates whether the instance of a SQL Server trusts the database and its content.

Furthermore, if the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server.

The best advice here is to have the TRUSTWORTHY property to OFF. If the rule fails, the property is ON, and then the script is called to change the property to OFF:

Databases owned by sysadmin have trustworthy bit off rule in ApexSQL Manage

sa Account should be renamed or disabled

This health check rule checks if the ‘sa’ account is renamed or disabled.

If the SQL Server instance ‘sa’ account exists and has not been renamed, the proper message is displayed.

The advice in this situation would be to rename the default ‘sa’ account along with changing the initially set password if it was enabled during the SQL Server setup.

However, to avoid potential security risks, the best practice is to disable the ‘sa’ account entirely:

sa Account should be renamed or disabled rule in ApexSQL Manage

User-created ‘sa’ account should not exist

Third-party applications can create a ‘sa’ account during deployment. It is considered best practice to check if there are user created SQL Server instances ‘sa’ accounts, so this health check rule determines if a user willfully created the ‘sa’ account. If this is the case that the message is displayed stating that the examined SQL Server is vulnerable. However, if the rule successfully passes, then the following message is shown:

“User account named ‘sa’ has not been created. No issues found.”

User created 'sa' account should not exist rule

BUILTIN/Administrators have sysadmin privileges

This health check rule determines if BUILTIN/Administrators are a part of the sysadmin role.

To further explain, not everyone should be granted with SQL Server instance sysadmin fixed role since this role grants all its members with full permissions on the entire database engine.

The best practice here is to deny windows BUILTIN/Administrators with sysadmin privileges. If this rule fails, then the proper fix SQL script drops BUILTIN/Administrators login:

BUILTIN/Administrators have sysadmin privileges rule

Foreign domain logins

Running this rule will provide a list of all added domain accounts on the SQL Server upon which the user can determine who needs to have access and who does not.

By default, during the SQL Server instance installation setup, domain accounts can be specified. But since a SQL Server environment is dynamic, its users can be added and removed monthly. The best advice here is to review domain logins from the list to verify if approved logins are listed:

Foreign domain logins rule

Blank passwords

This high-level analysis rule will determine whether any SQL Logins have blank passwords.

Logins with blank passwords can be exploited for malicious purposes, so the best practice is to avoid using them. Also, the second-best advice is to change passwords regularly:

Blank passwords rule in ApexSQL Manage

SQL Authentication

This health check rule allows users to investigate if SQL Authentication is enabled on the SQL Server.

To avoid potential security risks, it is recommended to connect to SQL Server instances using Windows Authentication and avoid using SQL Server Authentication in mixed mode.

If the rule fails, the following message is displayed:

“Server is in Mixed authentication mode (SQL Server Authentication is enabled). We recommend using Windows authentication wherever possible.”

SQL Authentication rule in ApexSQL Manage

sa Account Password Policy

This high-level analysis rule allows users to check if Password Policy is enabled on the sa account.

If the rule fails, the following message is displayed:

“Password policy is not enforced on the sa account.”

The worst-case scenario here is If the password for the SQL Server instance ‘sa’ account is ever breached, an attacker could repeatedly exploit a known SQL Server login password:

sa Account Password Policy rule

The password policy is covered in the “SQL Server password policy should be turned on” section of the Executing a health check analysis of the SQL Server Security category article.

SQL Logins Password Expiration

This health check rule allows users to determine if Password Expiration is enabled for SQL Logins. The password expiration policies are used to manage the lifespan of a password. When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled.

When SQL Server instance Login is using password expiration, the password will expire and must be changed regularly.

If the rule fails, the message is stating that listed accounts are not using password expiration policy:

SQL Logins Password Expiration rule

DISTRIBUTOR_ADMIN Login

This health check rule determines if the DISTRIBUTION_ADMIN account is acceptable. SQL Server instance is vulnerable if the instance is not configured to be a distributor and a DISTRIBUTOR_ADMIN account exists.

If the rule fails, the following message is displayed:

“DISTRIBUTOR_ADMIN account should be deleted.”

Since the best advice here is to delete the mentioned account, the SQL manage instance tool summons the proper fix SQL script that offers this option:

DISTRIBUTOR_ADMIN Login rule

Must_Change not configured for SQL Logins

This high-level analysis rule checks if the SQL logins have the “MUST_CHANGE” option set to ON.

It is standard practice to ask new users to generate their password after the first log on.

It is recommended to ensure all SQL Server instances Authentication Logins have the “MUST_CHANGE” option set to ON so that the password is updated the first time the login is used.

If the rule fails, the message is stating that listed accounts are not using password expiration policy:

Must_Change not configured for SQL Logins rule

xp_cmdshell Enabled

The xp_cmdshell is an extended stored procedure that allows the execution of the host executable outside the controls of database access permissions. Malicious users who have compromised the integrity of the SQL Server database process may exploit this access to control the host operating system and perpetrate additional malicious activity.

This health check rule investigates if the xp_cmdshell is enabled, and if it is, the tool is offering the option to disable it via the specific fix SQL script.

Best practices for system stored procedures would be to, unless necessary, disable xp_cmdshell procedure:

xp_cmdshell Enabled rule in ApexSQL Manage

xp_cmdshell Proxy Account Exists

The xp_cmdshell proxy is used to allow users without SQL Server instance sysadmin privileges to use the xp_cmdshell routine. The xp_cmdshell proxy account serves to grant a set of Windows authorities that are used by xp_cmdshell for this type of scenario. When users who have sysadmin privileges use xp_cmdshell, the Windows permissions granted to the SQL Server service account are used.

This high-level analysis rule checks if the xp_cmdshell Proxy Account exists, and if it does, the tool is offering the option, via the specific fix SQL script, to disable this account.

Best practices for system stored procedures would be to, unless necessary, disable the xp_cmdshell_proxy_account:

xp_cmdshell Proxy Account Exists rule in ApexSQL Manage

Conclusion

The extreme caution should be taken when deciding whom we are granting the specific “mighty” permissions on our SQL instance. If wrong decisions are made here then, our SQL Server instance will be left vulnerable and easily exploitable.

 

September 25, 2020