mssqltips logo

Surface Area Configuration for SQL Server Analysis Services Using PBM

By:   |   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.

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

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.

 the list of Facets which are available for SQL Server 2008 Analysis Services

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


Last Updated: 2010-04-29


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools