Surface Area Configuration for SQL Server Analysis Services Using PBM

By: Ashish Kumar Mehta   |   Updated: 2010-04-29   |   Comments   |   Related: > Analysis Services Security

Problem

I have installed a new instance of SQL Server 2008 Analysis Services and I am unable to find the Surface Area Configuration tool that I used to use in SQL Server 2005. How can I manage the Analysis Services features in SQL Server 2008?

Solution

Surface Area Configuration was a great tool which was introduced by Microsoft in SQL Server 2005 to help SQL Server DBA's to quickly turn ON / OFF many of the features which are turned fff by default in the product. However, Microsoft has removed Surface Area Configuration tool in SQL Server 2008. Now the question is how do you manage the Surface Area Configuration for Analysis Services? Database administrator can now use Policy Based Management to enable or disable feature for Analysis Services. Let's go through the steps to enable or disable Analysis Services features using Policy Based Management.

Configure Surface Area Configuration for SQL Server 2008 Analysis Services

1. Connect to SQL Server 2008 Analysis Services Instance using SQL Server Management Studio.

2. In Object Explorer, right click the SQL Server 2008 Analysis Services Instance and select Facets from the drop down list as shown in the below snippet to open up the Facets window.

3. In View Facets window you will be able to see the list of Facet which are available for SQL Server 2008 Analysis Services. You can select any of the Facet Properties and then select the value as True to enable the feature and choose the value as False to disable the feature as shown in the below snippet.

Once you have changed the values click OK to save the Surface Area Configuration changes. It is advised to keep the unwanted features turned off as this helps to protect your Analysis Services instance from potential attacks.

Analysis Services 2008 features which can be managed using Policy Based Management are:

• AdHocDataMiningQueriesEnabled: - The Data Mining Extension (DMX) OPENROWSET statement basically supports the use of ad hoc queries using external providers. Enable ad hoc data mining queries only if your applications and scripts use these statements, otherwise it is better to turn off this feature.
• AnonymousConnectionsEnabled: - Anonymous connections allow unauthenticated users to establish connections with your Analysis Services instance. Enable anonymous connections only if your applications require unauthenticated users to connect to the Analysis Service instance, otherwise it is better to turn off this feature.
• LinkedObjectsLinksFromOtherInstancesEnabled: - Analysis Services can supports linked objects, which link dimensions and measure groups between different instances. Enable linked objects - links from other instances, only if other instances of analysis services link to objects of the current instance.
• LinkedObjectsLinksToOtherInstancesEnabled: -Analysis Services supports linked objects, which link dimensions and measure groups between instances. Enable linked objects - links to other instances, only if this Analysis Services instance link to objects on other Analysis Services instances.
• ListenOnlyOnLocalConnections: - Enabling remote connections for Analysis Services opens a TCP/IP port on the server. Enable remote connections only if you want to allow connections for remote computers, otherwise it is better to turn off this feature.
• UserDefinedFunctionsEnabled: - Analysis Services can load assemblies that contain user defined functions. These functions can be based on the common language runtime (CLR) or can be Component Object Model (COM) objects. CLR based objects can be secured using the CLR security model, but COM objects cannot be secured. Enable loading of COM functions only if your applications require them, otherwise it is better to turn off this feature.

Next Steps

• Review tips on Policy Based Management.
  • http://www.mssqltips.com/sqlservertip/1492/using-policy-based-management-in-sql-server-2008/
  • http://www.mssqltips.com/sqlservertip/1493/evaluate-and-implement-policies-in-multiple-sql-server-instances/
• Review Policy Based Management in Books Online.
  • http://msdn.microsoft.com/en-us/library/bb510667.aspx
• Review using Policy Based Management with Central Management Servers to ease administration across database servers.
  • http://msdn.microsoft.com/en-us/library/bb895144.aspx
• Review SQL Server Surface Area Configuration for Features (SAC) Option Selection
• Review Using the SQL Server Surface Area Configuration Command Line Tool
• Review SQL Server security settings using the Surface Area Configuration tool (SAC)
• Read all my previous tips.

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips