New Information - SQL Server 2000 SP3.
This topic describes operational considerations for security in Microsoft® SQL Server™ 2000 Analysis Services. These considerations are related to Analysis server administration.
Service Logon Account Permissions
The service name for Analysis Services is MSSQLServerOLAPService. By default, this service runs under the local system account. If the MSSQLServerOLAPService service is subsequently configured to run under a local or domain user account, the account must be a member of the OLAP Administrators group on the Analysis server. Otherwise, Analysis Manager cannot process cubes and dimensions on the server. Membership in the OLAP Administrators group allows the service to access the registry and the data directory on an Analysis server.
For Kerberos authentication, delegation, and mutual authentication to work, the MSSQLServerOLAPService service must run under one of the following types of accounts:
- The local system account.
- A domain account with administrative privileges in the Microsoft® Active Directory® domain.
- A domain account without administrative privileges in the Active Directory domain. For this type of account, a domain administrator must register a Service Principal Name (SPN) for the account separately, using the setspn utility from the Windows 2000 Resource Kit. For additional information, see Security Account Delegation.
To maintain the logon account, use the Services application in Control Panel.
Service Logon Account Permissions to Data Sources
If Microsoft Windows NT® 4.0, Windows® 2000, or Windows XP integrated security is used, the logon account associated with the MSSQLServerOLAPService service must have permissions to access data sources that Analysis Services administrators can access through Analysis Manager. Otherwise, Analysis Services administrators will not be able to process the objects they maintain using Analysis Manager.
The permissions that are required depend on the type of storage structure used for the Analysis Services cube. When MOLAP storage is used, the account must at least have SELECT permissions on the source database. If ROLAP or HOLAP storage is used, the account must at least have SELECT and CREATE TABLE permissions on the source database.
Accessing Your Cube from Another Workstation
An administrator who creates a cube can be denied access to the cube. This can occur when the administrator logs on to a workstation other than the one hosting Analysis Services and attempts to view data in the cube on the server computer. A common cause for this problem is that the administrator was logged on under a local account on the server computer when the cube was created, and then logged on under a local account on the second computer. The cube owner's access control list (ACL) reflects the local account on the server computer, not the local account on the second computer, and the administrator is denied access.
To avoid this problem, you have two options:
- Always log on as a domain account when you create cubes, and then log on as the same domain account on other computers.
- Assign a role to the cube after it is created. You are then able to access the cube from other computers, if you log on as an account granted access by the role.
Lapse Between Change to End User's Access and Effect of Change
The time that elapses between a change to an end user's access defined in an Analysis Services role and the actual effect of the change depends on the value of the Auto Synch Period initialization property, the end user's actions, and how long the end user maintains a connection. The value of this property controls the frequency (in milliseconds) of client/server synchronization, including revalidation of end users' access. This value defaults to 10,000 milliseconds (10 seconds), but is passed to Analysis Services in each connection string. Thus, the default can be overridden by end users and client applications and can vary from end user to end user and client application to client application.
If the Auto Synch Period property is set to null or 0 (zero), synchronization does not occur at a constant interval. It occurs due to end users' actions; therefore, the time that synchronization will occur cannot be predicted accurately. In this case, changes made to an end user's access while the end user is connected to a cube do not take effect until synchronization occurs or the end user disconnects from the cube. After an end user has been granted access to a cube, that end user can remain connected to the cube for the duration of a query session until synchronization occurs. An end user cannot be forcibly disconnected from a cube during a query session after access has been granted. If the end user's access is removed during the query session, the end user will not be able to reconnect to the cube after disconnecting from it.
If the Auto Synch Period property is set to a nonnull, nonzero value, at the specified interval, end users' logon user names and authorizations are compared to their access defined in Analysis Services roles. At that time, changes to an end user's access that occurred since the last synchronization take effect immediately. For example, if an end user's access to a cube has been removed, the end user is immediately unable to access the cube.
For more information about the Auto Synch Period property, see Auto Synch Period Property.
It is important that you protect the security of your data. As with all database products, this includes judicious assignment of administrative access. All users who have administrative access to Analysis servers should be careful when they use Web browsers, productivity applications, and e-mail.
It is recommended that you establish specific Windows NT 4.0, Windows 2000, or Windows XP user accounts to administer Analysis Services and require administrators to refrain from accessing Web pages, productivity applications, and e-mail applications that support scripts or macros when using these administrative accounts. If it is necessary to use an application that supports scripts or macros when you are logged on as an administrator, set security to the highest level and never accept any control or object that is not marked script safe. Decision Support Objects (DSO) is not marked script safe, and your browser will provide a prompt before loading DSO. You should reject the loading of DSO in this way unless you are certain the application loading it is trusted.
It is also recommended that you use Windows NT 4.0, Windows 2000, or Windows XP integrated security for connections between an Analysis server and SQL Server used as a data source. Windows Authentication has benefits over SQL Server Authentication, such as secure validation and encryption of passwords, primarily because to its integration with the Windows NT 4.0, Windows 2000, and Windows XP security systems. For additional information, see Authentication Modes.