Error when trying to create Data Source on same server as Data Mart. (NETIQKB46770)

When attempting to create a Data Source, an error reports that the Data Source already exists. When using XMLCheckin to remove the Data Source, a message says that Data Source does not exist.

Error: 'Login failed for user 'DMAGENT <datasource_repositoryserver_repository>'. Reason: Not associated with a trusted SQL Server connection.'

Error: '[NetIQETL] ETL FAILED STEP [STEP_18_1_CUBEPARTITION] Description [Process [AMGeneric2UTC] Partition: Process [AMGeneric2UTC] Partition] Interface [{00000000-0000-0000-0000-000000000000}] [Error Code = 80040076 Source= AMGeneric2UTC Error Message = Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.;42000]'

When creating a Data Mart, Analysis Center creates a SQL Server account to use as a part of the linked server from the Analysis Center Warehouse to the Data Mart database. Problem may occur if the SQL Server was installed using mixed-mode authentication and Analysis Center was installed using only Windows authentication.

Data Mart requires SQL Server to use mixed-mode authentication. The OLAP Processing job will fail to log onto the Data Warehouse if the SQL Analysis Services uses only Windows authentication. If you use SQL Server with only Windows authentication, you must install the Data Warehouse on the same server as the Analysis Center Web Service.

You may be able to change the SQL Server from Windows only authentication to mixed-mode authentication.  However, this authentication change has not been explicitly tested, and may not resolve this issue.

A SQL Server service account is running under a different domain account. When you add an Analysis Center component, such as a Data Source, all SQL Server service accounts (SQL Server, SQL Agent, Analysis Service, and AC Web-service) must run under the same domain logon account.

The Analysis Center components are installed on a Domain Controller, which is unsupported due to potential .NET Framework conflicts. For more information, see the Analysis Center Release Notes.

Formerly known as NETIQKB46770

Requirements for Windows Only Authentication in your Analysis Center environment:

• The network domain must be configured for Kerboros security.  If the network domain runs on a Windows 2003 Server, the domain is probably already configured for Kerboros by default. You must configure Kerboros manually if your domains runs under Windows 2000 server.
• You must configure the network domain for DTC. For more information, see the Microsoft article http://support.microsoft.com/default.aspx?scid=kb;en-us;329332.
• You must add all SQL Servers to the domain Active Directory.
• The Analysis Center machines must all be trusted for delegation.
• All SQL Server services must have service principle name using setspn, which you can download from: http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.asp.
• You must set the OLAP service for service principle name using setspn.
• All SQL Servers, Analysis Center Web Services, SQL Agent Services, and OLAP service accounts must run under the same domain logon account.
• The service logon account must be trusted for delegation.
• Create all linked servers without adding server login. Access to the linked server will be made using Windows Authentication.
• The Data Warehouse SQLServerAgent must run under a service account that has access to OLAP server and all Data Mart servers.
• The Data Mart SQLServerAgent must run under a service account.
• The owner of linked server must have the sysadmin role.

The configuration requirements for Windows Only authentication do not guarantee that Analysis Center will work in a fully distributed environment. Microsoft Technical Support has reported a double-hop problem in the Microsoft Windows operating system that you can only avoid by using SQL Authentication. To guarantee that the Windows Only authentication works, the Web Service, the OLAP Service, and the AC Data Warehouse server must be installed on the same server to eliminate the double-hop issue.  However, during the Analysis Center upgrade, the ETL job may fail if the current context cannot be transferred.

NetIQ Technical Support recommends the following configuration for a fully-distributed installation of Analysis Center:

• The SQL Server Agent on the Data Ware and Analysis Center web service must use Windows authentication to access the Analysis Center Warehouse and configuration databases.
• SQL Agents for the Data Marts should use only Windows authentication to access Warehouse and Data Mart databases, such as when the ETL job is running. The ETL job always runs under SQL Service account because the owner of the ETL job belongs to the SQL System Administrator group. The current context is always used when a connection is made to the Data Warehouse server or Data Mart server within the ETL job.
• The Data Warehouse SQL code uses SQL Authentication to access to Data Mart databases using linked server. The linked servers to the Data Mart server are then configured with SQL authentication. Warehouse SQL code always uses the linked server to access Data Mart database, so SQL authentication is always used.
• Data Warehouse Agent jobs use Windows Authentication to access Data Mart databases. SQL Agent jobs are owned by ACAGENT user, which belongs to the SQL System Administrator group. Connections to Data Mart server made from Data Warehouse SQL Agent job are always made under Windows authentication.

When the Data Mart and Warehouse, using the OLAP database, are all on the same machine, Windows authentication should function without issue. However, NetIQ Technical Support recommends installing the Analysis Center database on a separate server from the AppManager repository. A distributed Analysis Center environment was designed to reduce resource impact on the AM OLAP database server.  Installing all Analysis Center components on one server may not achieve optimal leverage of all Analysis Center components.  For more information about the Component installation recommendations, see the Analysis Center Installation Guide.