Problem
I have been put in charge of securing our SQL Server 2005 servers. In a previous tip you discussed how to use some of the options of the Surface Area Configuration tool. I noticed there is also an option to configure settings for Features, but I am not clear what the options are, what they mean and how to set them up.
Solution
In a previous tip, How to setup security features for SQL Server, we introduced the SQL Server Surface Area Configuration tool and in a follow-on tip for the Surface Area Configuration for Services and Connections, we talked about the options available in that portion of the tool. In this tip we’ll expand on the portion of that tool called Surface Area Configuration for Features. As with Services and Connections, you have the option of selecting to view by instance or view by component. The below figure shows a view by instance, which is the easiest way to configure features for a given SQL Server.
The option selection is for Ad Hoc Remote Queries. By default, ad hoc remote queries are disabled. This is a behavior change Microsoft made back in SQL Server 2000 SP3 in order to make the SQL Server platform more secure. OPENROWSET and OPENDATASOURCE both allow a user to run a query on a remote SQL Server without a linked server connection being defined (hence the phrase “ad hoc remote query”). By default, this setting is disabled and with good reason: it would allow a normal user to hop from the first SQL Server to another, even if this wasn’t an intended behavior. Therefore, you’ll usually leave this option unchecked, and therefore, disabled. Only if you absolutely need it should you permit ad hoc remote queries in this manner. It is usually better to build a linked server, execute a query to the said linked server, and then remove the linked server connection instead.
Most of the options determine whether or not to turn on a particular component. For instance, whether or not to turn on CLR integration. The one that stands out is DAC. DAC is the “Dedicated Administrator Connection” and by default there is a DAC for SQL Server, but you must log on to the actual server itself to use it. This DAC configuration allows you to turn on DAC so you can connect from a remote system, such as your workstation. Generally, if you have the ability to remote desktop into a SQL Server, there’s usually not a good reason to turn on remote DAC. There are some things that can be done through the DAC that can’t be done through a normal connection, and thus, it shouldn’t be exposed to remote access unless you have no other choice.
Back to the options, the two exceptions to the on/off configuration choices are shown below: Native XML Web Services and Service Broker.
The reason these two are different is that they help configure endpoints, or network connections into SQL Server. Native XML Web Services is a new feature in SQL Server 2005 (and deprecated in SQL Server 2008) which allows SQL Server to respond to SOAP and XML over HTTP requests as if it were a regular web service. No IIS is required. Service Broker is a new message queuing feature within SQL Server. If you don’t have an XML web service or service broker endpoint configured, you’ll see the following message when you click on the applicable option:
If there is no endpoint for that given type, there’s nothing else that needs to be configured from a service area perspective. SQL Server isn’t opened up because there is no endpoint, so surface area configuration is not needed for that option. On the other hand, if you have an endpoint defined, you’ll see the following:
In this case you can configure whether or not the endpoint is started, but nothing else. If you want to do more, you’ll have to connect to SQL Server and execute the appropriate T-SQL commands with relation to the endpoints themselves.
This brings us to the end of the different options you’ll see. As far as typical configuration, these are what should be in place most of the time:
- Ad Hoc Remote Queries – Disabled
- CLR Integration – Enabled only if you plan on using .NET Assemblies on your SQL Server. Otherwise, Disabled.
- DAC – Disabled unless DBAs do not have remote desktop access to the SQL Server. If that’s the case, then Enabled.
- Database Mail – If you’re using SQL Server Agent and need reporting on jobs or need to send emails from SQL Server then Enabled. Otherwise, leave Disabled.
- Native XML Web Services – This would only have an option if you’ve configured it manually. If so, consider transitioning to a web service written in .NET deployed on IIS due to the deprecation in SQL Server 2008.
- OLE Autmation – Disabled.
- Service Broker – If you’re using Service Broker, it likely needs to be running.
- SQL Mail – Disabled as Database Mail is the replacement for SQL Mail. Avoid SQL Mail unless you have to support legacy code that requires it.
- Web Assistant – Disabled.
- xp_cmdshell – Disabled.
Next Steps
- Review the introduction to the SQL Server Surface Area Configuration tool.
- Review the configuration options for the SQL Server Surface Area Configuration for Services and Connections.
- Stay tuned for how to configure SQL Server’s network settings using SQL Server Configuration Manager.
- Check out these related tips on MSSQLTips.com:
