4.2 Deciding Between Windows and SQL Authentication

Before you install Analysis Center, you must decide about the type of authentication you want to use, set up the necessary accounts and permissions, configure each SQL Server to use the appropriate type of authentication, and configure the SQL Server services to use appropriate accounts. For more information, see Section 4.3.3, SQL Server Services.

To use Windows Authentication, do the following on each SQL Server:

Configure each Windows account as a login that requires access.
Specify appropriate Server Role for each login.
Configure whether to use Windows Authentication Mode or Mixed Mode.

To use SQL Server Authentication, do the following on each SQL Server:

Create the same login name and password to connect to each SQL Server. The login name and password must exist locally on each server.
Specify appropriate Server Role for each login.
Configure each SQL Server to use Mixed Mode Authentication.

4.2.1 Deciding Authentication Type For Connections Between Analysis Center Components

The following table lists the type of authentication you must configure for the different connections between Analysis Center components:

Connections	Authentication Type
During the Data Warehouse Installation
Data Warehouse SQL Server agent to Data Warehouse SQL Server	Windows or SQL Server Authentication
Data Warehouse SQL Server agent to Data Warehouse Analysis Services	Windows Authentication
Data Warehouse Analysis Services to Data Warehouse SQL Server	Windows or SQL Server Authentication
Data Mart SQL Server agent to Data Mart SQL Server	Windows or SQL Server Authentication
Data Mart SQL Server agent to Data Warehouse SQL Server	Windows or SQL Server Authentication
During the Web Service Installation
Web Service to Configuration Database	Windows or SQL Server Authentication

4.2.2 Deciding Authentication Type For Data Warehouse Installation

During installation of the Data Warehouse, you must select one of following authentication types for connections between the Data Warehouse and the Data Mart SQL Server:

Option	Description
Default	This option is recommended for a fully distributed environment. The Data Warehouse SQL Server Agent and Analysis Services use Windows Authentication to connect to the Data Warehouse.The log on account for each service is used to make the connection. The SQL Server Agent login account must have administrative permissions for the Data Warehouse SQL Server and Analysis Server. However, if you do not initially configure the SQL Server Agent account with administrative permissions, you can delegate these permissions during the installation. The Data Mart SQL Server Agents use SQL Server Authentication to connect to the Data Mart and Data Warehouse SQL Servers. The installation program creates the required SQL Server login accounts and encrypts the passwords. The Data Warehouse and the Data Mart SQL Servers must use Mixed Mode security (Windows and SQL Server Authentication).
Windows Only	This option is not recommended for a fully distributed environment. To use Windows authentication for every connection between Analysis Center components, you must install all the components on the same computer. The login account for each service is used to make the connection. The Data Warehouse SQL Server Agent and Analysis Services use Windows Authentication to connect to the Data Warehouse.The log on account for each service is used to make the connection. The SQL Server Agent login account must have administrative permissions for the Data Warehouse SQL Server and Analysis Server. However, if you do not initially configure the SQL Server Agent account with administrative permissions, you can delegate these permissions during the installation. The Data Mart SQL Server Agents use Windows Authentication to connect to the Data Mart and Data Warehouse. Before you install Analysis Center, you must configure the login account for the Data Mart SQL Server as a member of the System Administrators server role for the Data Warehouse SQL Server and for each Data Mart SQL Server. The Data Warehouse SQL Server and SQL Server Agent, Analysis Services, any Data Mart SQL Servers and SQL Server Agents, and the Analysis Center Web Service must all use the same domain account for their service log-on accounts. The domain account used for these services must have administrative permissions for the Data Warehouse SQL Server, any Data Mart SQL Server, and the Analysis Server (System Administrators server role for the SQL Servers). Administrative permissions for Data Mart SQL Servers must be granted prior to installation. Other administrative permissions can be granted during installation. The domain account must be trusted for delegation. All SQL Server services must have a Service Principle Name. SQL Server Analysis Services must have a Service Principle Name. The Data Warehouse and Data Mart SQL Servers can be configured to use either Windows or Mixed Mode Authentication. All Analysis Center SQL Servers and the Analysis Server must be added to the Active Directory of their domain. Any domains that host Analysis Center computers must be configured for Kerberos security. On computer hosting an Analysis Center SQL Server, Microsoft Distributed Transaction Coordinator (DTC) must be configured for network access. All computers hosting Analysis Center components must be trusted for delegation. You cannot use Windows authentication for all the connections in a fully distributed environment because of the double-hop issue. Consider the following scenario: You install the Analysis Center components on three computers: Analysis Server on Computer A, Data Warehouse SQL Server on Computer B, Data Mart SQL Server on Computer C. For processing jobs, the Analysis Server makes a connection to the Data Warehouse SQL Server to get dimension data, and then makes a subsequent connection from the Data Warehouse SQL Server to the Data Mart SQL Server to get fact data. The credentials of the Analysis Services login account cannot be passed from Computer A to Computer B and then to Computer C.
Custom	You can use either Windows or SQL Server Authentication for each connection. If you use SQL Server authentication to connect to a SQL Server, that SQL Server must be configured to use Mixed Mode (Windows and SQL Server Authentication). Analysis Center does not manage SQL Server accounts. The passwords for SQL Server accounts are sent in clear text, which might pose security risks. If you use Windows Authentication, the SQL Server Agent login account must have administrative permissions for the Data Warehouse SQL Server and Analysis Server (System Administrators server role for the SQL Server). If you do not initially configure the SQL Server Agent account with administrative permissions, you can do so later in the installation. Before you install Analysis Center, you must configure the login account for the Data Mart SQL Server Agent as a member of the System Administrators server role for the Data Warehouse SQL Server and for each Data Mart SQL Server. In this case, you must have previously configured the Windows and/or SQL Server accounts as members of the System Administrators server role for each relevant SQL Server. If the Data Warehouse SQL Server Agent account does not already have SQL Server and Analysis Services administrative permissions, you can delegate those later in the installation. If you use SQL Server Authentication for a connection, the login name and password you specify for that connection must already be configured on the relevant SQL Server and must also belong to the System Administrators server role for that SQL Server. You cannot use a SQL Server account whose username contains spaces, single, or double quotes. You cannot use a SQL Server account whose password contains spaces. However, you can use a SQL Server account whose password contains single or double quotes.