In a previous article, we have discussed about the top 10 security factors that you should take into consideration in order to secure your SQL Server instances. In this article I will try to deep dive into one of those factors, that is SQL Server Surface Area.
First, we need to define the terms Surface Area and Surface Area Reduction. SQL Server Surface Area is a term that is used for characterizing all those Database Engine features and components of SQL Server which when enabled, can provide additional functionality. This additional functionality however, comes with a security cost. This cost is the increase of the potential targets for malicious attacks that could take place, in order for the attacker to gain unauthorized access to a SQL Server instance and its databases. Disabling or at least “properly” setting up these features and components is called Surface Area Reduction.
The major surface area features in SQL Server that have a direct or indirect effect to your instance’s security are:
- Ad Hoc Distributed Queries
- CLR Enabled
- Cross DB Ownership Training
- Database Mail XPs
- Ole Automation Procedures
- Remote Access
- Remote Admin Connections
- Scan for Startup Procs
- SQL Mail XPs
- Trustworthy
- xp_cmdshell
Another point that could be considered as part of SQL Server’s Surface Area is the “sa” built-in SysAdmin login. SQL Server lets you use Mixed Mode authentication, that is Windows Authentication along with SQL Server authentication. Even though it is recommended to use Windows authentication only, in the case where you are using Mixed Mode, the built-in SysAdmin user (sa) gets automatically created (you set the password during SQL Server’s setup).
Ad Hoc Distributed Queries
With “Ad Hoc Distributed Queries” server configuration option, you can allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE functions. This option is disabled by default.
The recommendation is if you do not really need this option, do not enable it. In case you need to run a distributed query, there are other ways/workarounds to perform it. For example, you could use an SSIS package and other similar solutions.
CLR Enabled
With “CLR Enabled” server configuration option, you specify whether user assemblies can be ran by SQL Server or not. This option is disabled by default.
Creating and running .NET assemblies in SQL Server allows to broadly extend SQL Server’s functionality. However, if not managed properly, it can introduce additional security risks. For example, if this option is really required by your operations and its use is allowed by your Organization’s security policies, you should only allow CLR assemblies with “SAFE” permissions and not with “EXTERNAL_ACCESS” or “UNSAFE_ACCESS”. For more info, please check this MSDN article.
Cross DB Ownership Chaining
With “cross db ownership chaining” server configuration option, you can either enable or disable cross db ownership for all databases in a SQL Server instance. A simple example of cross db ownership chaining, is having a view or stored procedure in Database A referencing one or more tables in Database B. This could potentially expose data outside the boundaries of a database. That’s why cross db ownership chaining server configuration option is disabled by default. In the case where you really need to use cross-database ownership chaining, then you can consider setting it up only for the individual databases that exchange information between them instead for all databases in the instance. For more info please refer to this MSDN article.
Database Mail XPs
With “Database Mail XPs” server configuration option, you can enable or disable Database Mail. This option is disabled by default. This option, when enabled, allows the user to send mails via SQL Server’s database engine. This creates the risk of having data sent outside to a remote host via mail.
In case you need to use Database Mail’s functionality, you could consider other, more secure, alternatives such secure ftp, enterprise monitoring software that among other can send mail notifications, and other similar approaches.
Ole Automation Procedures
With “Ole Automation Procedures” server configuration option, you can specify whether OLE Automation objects can be instantiated within Transact-SQL batches or not. If this option is enabled, it essentially enables the user to execute external functions and this could be a security risk. Examples of OLE Automation object are objects that expose the IDispatch interface. This feature is disabled by default.
As with other surface area components/features if this feature is not absolutely required and allowed by your Organization’s security policies, you should not enable it.
Remote Access
With “remote access” server configuration option, when enabled, you can execute stored procedures from local or remote servers on which instances of SQL Server are running. This functionality can be abused in order to launch Denial of Service (DoS) attacks on remote servers by sending huge workload processing requests. Furthermore, note that this feature is deprecated and will be removed in the next version of SQL Server. To this end, it is not recommended to use it in new development work. Also, if it is not required by any operation, then you should consider disabling it (if for example your SQL Server instance does not interact with any other SQL Server instance).
Remote Admin Connections
With “remote admin connections” server configuration option, you can enable client applications on remote computers to use the Dedicated Administrator Connection (DAC) to the SQL Server instance. The DAC can be used in order to perform troubleshooting and other diagnostic procedures on a SQL Server instance, even in the case where the instance is not in a stable state.
There are different opinions regarding whether this feature should be enabled for all SQL Server instances or not. The basic recommendation is that it should be enabled for clustered SQL Server instances. Note that this feature, is disabled by default.
Scan for Startup Procs
With “scan for startup procs” server configuration option, you can enable SQL Server to scan for automatic execution of stored procedures at SQL Server startup time. This option is disabled by default. If enabled, it creates the risk of having potentially malicious code be executed during SQL Server’s startup.
You should avoid using this feature for security reasons. If, however, you need this or similar functionality, you should consider implementing it in a more controlled manner.
SQL Mail XPs
“SQL Mail XPs” was the predecessor of Database Mail XPs. SQL Mails XPs feature was deprecated and removed in SQL Server 2012 and later. If you are using SQL Server 2008 R2 or earlier, then like in the case of Database Mail XPs, this feature, when enabled (it is disabled by default), creates the risk of having data sent outside to a remote host via mail.
If you need to have this or similar functionality, you could consider other, more secure alternatives (i.e. secure ftp, enterprise notification software, etc.).
Trustworthy
“TRUSTWORTHY“ is not a SQL Server surface area feature but a database property. A very important one. This property is turned off by default. If turned on for a database, it tells SQL Server to fully trust the specific database and its contents. This creates the risk of having malicious code (i.e. in the form of CLR assemblies with EXTERNAL_ACCESS or UNSAFE permission settings) contained in a database and that could be executed and thus affect the entire SQL Server instance. It is recommended not to turn on this database property for security reasons. If for any reason you consider turning TRUSTWORTHY on (even though you should avoid that), there is a Support KB article that discusses certain guidelines for using the TRUSTWORTHY database setting in SQL Server.
xp_cmdshell
With the “xp_cmdshell” server configuration option, you can control whether members of the SysAdmin fixed server role can execute the extended stored procedure xp_cmdshell and thus interact with the Operating System onto which SQL Server instance is installed.
If you enable this feature, along with the functionality that is offered, you also create the risk of having someone that maliciously gained SysAdmin access on your SQL Server instance, to also gain access to the Operating System of the database server and thus be able to execute OS commands. So, unless it is required and allowed by your Organization’s security policies, you should not enable this feature.
sa User
Regarding “sa” built-in SysAdmin user (in case you are using “Mixed Mode” as Authentication Mode), after you ensure that there are other authorized persons that have SysAdmin access on the SQL Server instance and after you make sure that if you remove or rename “sa” will not create any problem, then you should consider disable or at least renaming the “sa” login.
Securing SQL Server with DBA Security Advisor
The surface area of SQL Server is just a part of what needs to be secured towards securing your entire SQL Server instances. There are also many other factors that you need to take into consideration such as: authentication, authorization, auditing, password policies, and much more. Also, the SQL Server hardening process must be repetitive and not just a one-time process. Furthermore, the existence of multiple SQL Server instances within Organizations makes the whole hardening project more complex.
Having all the above in mind and within the context of my initiative SQLArtBits, I have developed DBA Security Advisor, an enterprise tool which assesses single or multiple SQL Server instances for vulnerabilities using a rich set of security checks. After assessing the SQL Server instances, DBA Security Advisor explains what the detected risks are and provides useful recommendations as well as remediation scripts and methods.
The main features of DBA Security Advisor (Enterprise Edition) include:
-
7 Categories with more than 30 security checks:
- Server-Level Accesses
- Surface Area
- Authentication and Authorization
- Auditing
- Password Policies
- Miscellaneous
- Advanced
- Scan single and multiple SQL Server instances
- Generate security reports
- Provide recommendations for the detected security risks
- Provide remediation scripts or methods for the detected security risks
- Rich set of display options
- Rich set of export options
- Report history
- Information about connected instances
- Program customizations
- Program options
You can download the datasheet for more information on the available features.
Let’s see a simple example of using DBA Security Advisor. For this example, consider a scenario which features two SQL Server instances named “SQL2K14” and “SQL2K16”. As the Enterprise Edition of DBA Security Advisor allows scanning multiple SQL Server instances at the same time, let’s connect on both instances:
After connecting the SQL Server instance(s), DBA Security Advisor provides some basic information about the connected instances. In case you are looking for more information, you can use the “Connected Instance(s) Information” function.
Now let’s choose from the list of security checks which checks we want to run against our SQL Server instances:
For this example, we are just going to run the below two “Surface Area” security checks:
- Cross DB Ownership Chaining
- Ole Automation Procedures
After running the above security checks we get the below report:
As you can see in the above screenshot (Figure 5), in the generated report’s page, in “Display Options”, there is a link labeled “Suggest Remediation Scripts”. If you click that link, as the name implies, you will get remediation scripts or methods for the detected security risks. Let’s conclude this example with Figure 6, which shows the remediation script for the detected security risk along with the recommendation:
No you have all the information you need in order to review the detected security risks, the recommendations, as well as the remediation scripts or methods, and finally decide how you want to handle the findings of the assessment.
As you can see from the above example, DBA Security Advisor has many interesting features that can help you secure your SQL Server instances much easier instead of manually performing this important process. With a rich set of security checks, a powerful recommendation engine, and support for assessing multiple SQL Server instances, it is an interesting solution when it comes to securing your SQL Server instances.
Let’s conclude this discussion by mentioning that, even though different security assessments and tools detect certain items as potential security risks, there are cases where your business operations and essentially the underlying applications might require some of those items/features to be enabled. In such cases, you must make sure that you take all the necessary security precautions when you enable such a feature. Similarly, whenever you read security assessment reports and plan your actions, if a feature marked as security risk is required by an application, you must make sure that you will proceed, if possible, to the necessary application changes in cooperation with the application’s developers, and certify that the application will be able to operate properly prior to disabling the specific feature/item which have been identified as a security risk.
View all posts by Artemakis Artemiou
- Certificate Management in SQL Server 2019 - May 31, 2019
- SQL Server consolidation – Hosting multiple databases on a single SQL Server instance - December 2, 2016
- How to create and manage T-SQL code snippets - October 28, 2016