Surface Area Configuration – Where did it go in 2008?

In SQL Server 2005 we were given the Surface Area Configuration tool to assist us in terms of security. The idea was to help us reduce the “surface area” of the SQL Server from a security point of view. It is “missing in action” in SQL Server 2008 so does this mean all our security worries are over? I don’t think so. So where has it gone?

The Surface Area Configuration tool would show us which features were disabled, explain what they were used for and let us enable them if we would choose to do so. It also listed the SQL services running on the machine allowing us to choose exactly which we wanted to be running. There was noticeable overlap of the services functionality between the SAC tool and SQL Server Configuration Manager so that part of the tool will not to be missed; we’ll just use SSCM for that.

However, the SAC tool was useful in identifying which security features were available and what the implications were if we should decide to enable them. One example was the extended Stored Procedure called xp_cmdshell which allows a Windows OS command to be submitted directly from SQL. This powerful feature can be powerfully good or powerfully bad. It is now disabled by default for good reason but is fully explained in the SAC tool and can be enabled if you are confident of your security environment. Of course, we could use Books Online to review each feature, but the idea of collecting all the security related features together was a good one and the Knowledge Base was fairly good. So what do we do now?

Well, this functionality has been merged in with the Policy Based Management feature of SQL Server 2008. I wrote a blog entry on this new feature previously. (During the Beta this feature was called the Declarative Management Framework but is now known as Policy Based Management). With PBM, you can control numerous SQL Servers across the network from a central server through policies. You can enforce things like naming conventions, or database options or security settings. The properties we can control are listed in “facets”. These facets can be used to define “conditions” which in turn can be used to define “policies”. The big feature is that PBM works for SQL Server 2000, 2005 and 2008 as long as you have at least one server at the 2008 level to serve as the Central Management Server.

One of the facets is called “Database Options”. We could create a condition based on one of the facet properties like “AutoShrink”. This database option is generally thought of as bad practice when enabled so we could then use the condition to create a policy to report on all databases that currently have this option turned on. We could schedule the policy or run it “on demand” to find out about violations.

So what has this got to do with Surface Area Configuration? Well, there is a Surface Area Configuration facet that contains all the properties we used to see in the SAC tool. Microsoft wants us to use this facet to control the use of these security features across multiple servers using policies. The SAC tool would work on a single instance basis so this approach is definitely an improvement. We can now apply a policy to a single server or across multiple servers, our choice. And choice is a good thing.

Cheers

Brian

Recent Posts:

Rebuild the System Databases – Practice makes perfect!

Skill-based Certification for the future?

Browser Support in Reporting Services?

Want to know what’s going on in your cube?

• Microsoft
• pbm
• policies
• policy based management
• security
• SQL Server
• SQL Server 2008