Environment
Situation
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]'
Resolution
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.
Cause
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.
Additional Information
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.