The overall security of SQL Server instances is a broad topic. Security breaches happen often, and, in most cases, they are resolved quickly, and the level of security is tightened, but the best and most desirable scenario is to avoid them entirely. This can be done by taking proactive actions in analyzing possible security risks and patching them before the least desirable scenario happens. With SQL Server environments being dynamic, there is an almost constant need for better security and management of SQL Server instances.
To address this need comes ApexSQL Manage, a SQL Server instance management tool that can analyze and perform health checks of SQL instances in bulk. With a predefined set of rules and the ability to create custom rules, the overall security of SQL instances can be increased, and potential risk scenarios averted. Predefined rules can be executed as they are, and based on the SQL instance configuration, the result can be Pass or Fail, but for some rules, that outcome is not enough, i.e., the Pass or Fail result doesn’t provide the necessary information. These types of rules require additional criteria to present a comprehensive result. The criteria specified is a login account, single or multiple, and these types of rules are colored red in the application.
The following health check rules require additional user-side criteria to provide relevant information upon execution:
- Are there any unauthorized accounts on the SQL Server instance?
- Is the Active Directory helper Login account acceptable?
- Is the Analysis Services Login account acceptable?
- Is the Integration Services Login account acceptable?
- Does the sa account have a blank password?
- Is the Full-Text Search Login account acceptable?
- Is the Reporting Services Login account acceptable?
Based on the rule, the criteria can vary, but the process of configuring criteria is straightforward. Click the Manage button of the Health tab:
Rule management window is where new rules can be created from scratch or from pre-existing ones, edited, and criteria configured. To set a criterion, check the rule and click the Edit button:
The Edit rule window will show the description and parameters. The Description box specifies which parameter needs to be added. Click the Add button and add a suitable parameter:
Parameter required for this rule is authorized accounts. Enter the relevant accounts and click the OK button:
Unauthorized Accounts
Only trustworthy accounts should have sysadmin privileges. It is recommended to keep this list to a minimum to avoid potential security breaches. By adding trustworthy accounts as parameters to this rule, the tool will analyze which accounts are not added to the sysadmin role but have the following privileges on the SQL Server instance: ALTER ANY COLUMN MASTER KEY, ALTER ANY COLUMN ENCRYPTION KEY, ALTER ANY MASK, UNMASK, ALTER ANY SECURITY POLICY, CONNECT ANY DATABASE, SELECT ALL USER SECURABLES, VIEW ANY COLUMN MASTER KEY DEFINITION, IMPERSONATE ANY LOGIN, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION.
Unauthorized accounts, which are considered a potential breach in security, are listed in the Message box of the Result tab:
Acceptable Active Directory Helper Login Account
This service is required for integration with the Active Directory. Active Directory Helper is used when a SQL Server object needs to be created in Active Directory to register an instance. It is automatically started and stopped by the SQL Server instance when the operation is finished.
Running this rule will verify if the Active Directory Helper account is acceptable, e.g., if the specified Login as criteria is the correct one. The advice is to choose an Active Directory Helper account with appropriate privileges to avoid potential security risks. If the result of the rule is Fail, details of the assessment are found in the Message box of the Result tab:
Acceptable Analysis Services Login Account
Analysis Service is an SSAS component used for analysis and reporting as well as processing dimensions, storage, deploying cubes, partitions, etc. The only supported authentication method when connecting to a SQL Server instance from SQL Server Management Studio is Windows Authentication.
Running this rule will verify if the Analysis Services account is acceptable, e.g., if the Windows Login specified as criteria is the correct one. To avoid potential security risks, choose an Analysis Services account with appropriate privileges. If the result of the rule is Fail, details of the assessment are found in the Message box of the Result tab:
Integration Services Login Account on whitelist
SQL Server Integration Services are used to solve complex business problems, i.e., copying or downloading files, cleansing and mining data, loading data warehouses, and managing SQL Server instance data and objects. It is a platform used for data transformation solutions and building enterprise-level data integration.
Running this rule will check if the Integration Services account is acceptable, e.g., if the Login specified as criteria for this rule is the correct one. Details of the rule assessment are found in the Result tab:
sa Account Blank Password
Blank passwords should not be used since they are a serious threat to security. From Windows Server 2008, the default settings for Active Directory domains require complex passwords of minimum eight characters but, if users with the permissions to create new accounts bypass domain password policies, they could create accounts with blank passwords.
Executing this rule will determine if there are any sa accounts with blank passwords on the selected SQL Server instance. If the sa account has been renamed, the renamed account should be specified as criteria. The Result is displayed in the Message box:
Acceptable Full-Text Search Login Account
Full-Text Search is a functionality of SQL Server that provides support for full-text queries, i.e., single phrases and words and well as multiple forms of them. More information on the topic can be found in Hands on Full-Text Search in SQL Server article.
The rule will verify if the Full-Text Search service account is acceptable based on the user-defined criteria. Choose a Full-Text Service account with appropriate privileges to avoid potential security risks. When the rule is executed, the Result can be found in the Message box:
Acceptable Reporting Services Login Account
The reporting service is used by SQL Server Reporting Services (SSRS) component to manage settings for the Report Manager and Report Server. The supported authentication type for connecting from SQL Server Management Studio is Windows Authentication. Detailed information on the topic of Reporting Service and configuration can be found in the SQL Server Reporting Service Configuration Manager article.
This rule will determine if the Reporting Service Login account is acceptable based on the user-defined criteria. The best practice is to choose a Reporting Service account with appropriate privileges to avoid potential security risks. The executed rule will show the Result and details in the Message box:
Conclusion
With the ability to execute rules on multiple SQL instances at once, specifying criteria for this set of rules will drastically decrease the time needed to inspect every SQL Server instance and increase the overall security of the environment.
September 25, 2020
