This is day thirteen of the SQL Advent 2012 series of blog posts. Today we are going to look at servers where everything is installed and enabled. Before we start this post let’s look back in time a little. Before SQL Server 2005 came out when you installed SQL Server pretty much everything was turned on by default. This of course widened the attack vector against the database servers. With SQL Server 2005 pretty much everything is turned off and you have to turn the features on if you want to use them. Now sometimes some admins will just turn everything on because that way they don’t have to deal with this later, this are also the same kind of people who insist that the account needs to be db_owner otherwise their code won’t work.

To see what these features are and if they are turned off, you can use the following query.

SELECT name, value,value_in_use 
FROM sys.configurations
WHERE name IN (
'Agent XPs',
'SQL Mail XPs',
'Database Mail XPs',
'SMO and DMO XPs',
'Ole Automation Procedures',
'Web Assistant Procedures',
'xp_cmdshell',
'Ad Hoc Distributed Queries',
'Replication XPs',
'clr enabled')

The difference between value and value_in_use is that value_in_use is what is currently used and value will be used next time the server is restarted. If you want to have the change take effect immediately then use RECONFIGURE

Here is an example that will turn off xp_cmdshell

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE
GO
 
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO
 
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE
GO

If you prefer the GUI, you can also use that, right click on the database server name in SSMS, select Facets, from the Facet dropdown select Surface Area Configuration. You will see the following Facet properties

Here you can enable or disable the features you are interested in. You can also export these properties as a policy to use on other servers so that the features are the same on all your servers

Installing everything by default

Here I have mixed feelings myself about what to do. On one hand I don’t like to install SSAS or SSRS if there is no need for it, on the other hand I don’t feel like adding that stuff 6 months down the road if there is suddenly a need for it. If I do install it, I make sure it at least doesn’t run by default but it is disabled. There is no benefit in having SSAS, SSRS or SSIS running and using CPU cycles as well as RAM if nobody is using these services. If you do install it and nobody uses it, disable the services, you will have more RAM and CPU cycles for the SQL Server service available.

That is all for day thirteen of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap