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:
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:
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:
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:
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:
-
For more information on this topic, consult the following You should not disable the guest user in the msdb database article.
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:
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:
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:
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:
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.”
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:
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:
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:
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.”
-
For more information on the matter, consult the Change SQL Server Authentication article.
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:
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:
-
More info about the password expiration policies can be found in the Password policies in SQL Server article.
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:
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:
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:
-
Refer to the following How to use xp_cmdshell extended procedure article.
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:
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
