Lesson 2: Setting SSAS Server Security and Server Properties

Estimated lesson time: 20 minutes

After installing an SSAS instance, the SSAS service needs to be configured with SSAS security roles. SSAS security has two elements: SSAS server role security for administration, and cube and dimension security for user access and operations. This lesson discusses SSAS server roles. Chapter 16, Administering and Securing SSAS Cubes, reviews SSAS cube and dimension roles for user security access.

In addition to security, the SSAS instance can be configured to capture server activity (user queries, server processing, and error logs) and define data location. These settings are optional but can be used for tuning drive activity, optimizing aggregations, and troubleshooting.

Setting SSAS Administrative Roles and Permissions

When it comes to setting up SSAS security for management activities, there are two primary security roles:

• The server role, which provides access to complete SSAS server functions
• Database roles, which define database-level administration tasks and end-user data access

SSAS security is based on Windows authentication only. Unlike the SQL Server database engine, which contains a second security option called SQL authentication, SSAS uses only Windows authentication.

Exam Tip

SSAS uses only Windows authentication. This means that SSAS security can only be assigned to local users and groups and domain users and groups.

SSAS Server Role

Server-level security includes a single server-level role, which provides complete SSAS access to assigned users. The server role assignment is available through the server properties window.

To manage the users and groups assigned to the role, connect to SSAS through the SQL Server Management Studio (SSMS), right-click the SSAS server, and then select Properties. Figure 2-5 shows the Analysis Services Properties window with the Security settings page selected on the left.

Figure 2-5 The Analysis Services Properties window includes a Security tab to assign the server role to users and groups.

To add a new user or group to the server role list, simply click the Add button and you will be able to type in or browse to either local users and groups or domain users and groups. The implications of the server role are:

• The server role provides complete access to the features and functionality of the entire SSAS instance, including data and processing.
• Users who are members of the local administrators group on the same server as the SSAS instance are included in the server role automatically even though the local administrator group does not appear on the server role list.

SSAS Database Roles

The second SSAS security level is the database role. Database roles are used for two primary purposes:

• For administrative task assignments
• For user access to data and data-related functionality

To manage database roles, you need to connect to SSAS in SSMS and navigate to the database folder. Drill down to the database in which roles need to be applied, right-click the Roles folder, and then select New Role. Figure 2-6 shows the Create Role window.

Figure 2-6 The Create Role window lets you assign a role administrator and assign read and/or process functions.

After a role is created, you can edit it through the same Roles folder in SSMS by right-clicking and choosing Properties.

For the purposes of management tasks, the database role window includes two settings pages: the General page and the Membership page. The General page lets you name and describe the role and assign database permissions. Three database-level permissions exist:

• Full control (Administrator) Assignment to the Full control role gives complete access to the database, including data, schema, processing, and operations. Administrators can also manage security roles.
• Process database The Process database role allows SSAS processing. This means a user can be limited to processing the database in which the role is created. However, this option does not give a user read access to the data or definition; read access must be assigned separately.
• Read definition The Read definition role lets a role member see the full definition of a database; it does not allow a user to have data-access rights or processing rights.

In the same way that server roles are managed, you can add or remove local users and groups or domain users and groups on the Membership tab in the Create Role window; this appears as Edit Role when you are editing a security role.

The rest of the property pages in the Database Role interface (such as Data Sources, Cubes, Cell Data, and so on) are for securing data-level and object-level security and are reviewed in Chapter 16.

Editing SSAS Server Properties

SSAS includes server-level properties that apply to the entire instance. The settings include service-level properties to manage and tune the ways in which some functionality is applied, and they include properties that allow various levels of logging.

Figure 2-7 shows the General page of the SSAS server properties window, which includes a list of server properties that can be managed. As you did when you performed the server-role assignment, open SSMS connected to SSAS, right-click the database, and select Properties.

Figure 2-7 To manage SSAS server properties, connect to SSAS through SSMS, right-click the Server, and select Properties; properties are managed on the General page.

The properties are assigned a category, either Basic or Advanced, and by default, only the Basic properties are displayed. To display the Advanced properties, select the Show Advance (All) Properties check box.

IMPORTANT SSAS server logging list changes in SQL Server 2005

Note that with the release of SQL Server 2005 Service Pack 2 (SP2), some of the property entries related to logging changed. These are prefixed with Log\. Some properties were removed and others were added. Table 2-8, SSAS Server Logging Properties, later in this chapter, identifies some of the more important log properties available with SP2.

A general best practice is to keep the property defaults unless a specific requirement dictates a change. These changes might be recommended through white papers or other SSAS resources. For the purposes of this Training Kit, you should note two general property categories:

• Logging
• Directory Locations

Modifying Query and Error Logging

The logging properties in the SSAS server let you define what information is captured and how it is captured. The logging options are listed under the Log category in the server properties list.

Flight Recorder Activity and Error Log The first log properties to note are the ones listed under the Log\FlightRecorder property. To see all the flight recorder properties, you must select the Show Advance (All) Properties check box. The flight recorder is an error and activity log for SSAS. To use the flight recorder:

1. Set the Log\FlightRecorder\Enabled property to True.
2. Optionally, set the Log\File property; the default msmdsrv.log is set for the flight recorder.
3. Restart the SSAS service for the flight recorder to take effect.

By default, the flight recorder captures default activity such as processing errors and server-level errors, but it does not capture queries. However, the flight recorder leverages SQL Server Profiler tracing definitions, which means that you can define your own SSAS trace and capture the trace definition. With the Log\FlightRecorder\TraceDefinitionFile property, you can override the default capture. For more information about the flight recorder properties, see the SQL Server 2005 BOL topic Log Properties, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en /uas9/html/33fd90ee-cead-48f0-8ff9-9b458994c766.htm.

Query Log The next log properties you should note are the Log\QueryLog properties. These enable you to capture queries that are run against any SSAS database in the instance. You can use the queries that are captured to optimize aggregation. See Chapter 8, Defining SSAS Storage, Partitions, and Aggregations, for details about optimizing aggregations.

Table 2-8 highlights the Log\Query Log properties.

Table 2-8 SSAS Server Logging Properties

The query log does not capture the full MDX query that is sent to the server. Instead, it captures a numeric list of the hierarchies and attributes used in each dimension, such as 01,00000010200000,100,00,100,12000. Each comma separates the level numbers between dimensions. See Chapter 6, Developing SSAS Cubes, for information about dimensions and attributes. The server can use this list to know which hierarchies were accessed and at what level, so it can optimize its aggregates without having the details of the query.

Defining Default Directories

The SSAS server properties also let you define the default location for data, backups, and logs, as Table 2-9 describes.

Table 2-9 SSAS Server File Location Properties

Practice: Setting Up SSAS Query Logging

In this practice, you will set up SSAS query logging to capture user queries against the databases on the SSAS instance.

Exercise: Database Creation and Logging Configuration

1. Open SSMS and connect to the Database Engine in the Server Type drop-down list. Enter (local) in the Server Name drop-down box, and then select Connect.
2. In the Object Explorer, open the (local) database engine instance and right-click the Databases folder. Select New Database.
3. In the Database Name text box, type SSAS_Logging, and then click OK to create the database.
4. In the Object Explorer, click Connect, and then select Analysis Services from the list.
5. Type (local) or localhost in the Server Name drop-down box, and then click OK to connect.
6. Right-click the SSAS server, represented by the yellow cube icon, in the Object Explorer, and then select Properties.
7. In the Log\QueryLog\QueryLogConnectionString property, click in the value field, and then select the ellipsis to bring up the Connection Manager window.
8. Enter (local) in the Server Name drop-down box, then in the Select Or Enter A Database Name drop-down list, select the SSAS_Logging database that you created in Step 3. Click OK to save the connection string.
9. Find the Log\QueryLog\CreateQueryLogTable property and change the property to True.
10. Notice that, by default, the Log\QueryLog\LogSampling property is set to 10, and the Log\QueryLog\QueryLogTableName property is set to OlapQueryLog.
11. Click OK in the Analysis Services Properties window to save the changes. Note that none of these changes requires a service restart; they will take effect immediately.
12. In the Object Explorer window, browse to the SSAS_Logging database in the Databases folder of the (local) database engine connection.
13. Right-click the Tables folder, and then click Refresh.
14. Open the Tables folder and note that there is an OlapQueryLog table; this was created by SSAS for query logging. This table will initially be empty, but if you return to it after running a query, it will contain logging information.

Quick Check

1. Can a user be assigned security rights to be an administrator of an SSAS database but not an administrator of the entire SSAS instance?
2. Can a user be assigned Processing rights to a database without the ability to see the data or schema?
3. Does the query logging feature in the SSAS server properties let you capture the MDX statements to a file for later review?

Quick Check Answers

1. Yes, a user can be assigned security rights to be an administrator of an SSAS database but not an administrator of the entire SSAS instance. The Administrator database role allows a user to perform administrator tasks on the SSAS database only.
2. Yes, a user can be assigned to a database role that has Processing privileges but not read definition or access to any of the cubes.
3. No, the Query Log table does not store the actual MDX statements. It stores the levels for each of the attributes and hierarchies that are used by the query. You can use SQL Server Profiler to capture SSAS activity by tracing MDX.

< Back      Next >

© Microsoft. All Rights Reserved.