I've come across a situation which I've never seen before, and I'm hoping you may be able to answer either or both of the following questions:
1. Have you ever seen a domain set-up like this before?
2. What do you recommend, in how to best approach resolving this challenge?
Development environment is standard. One domain, all works OK. Development is one-machine, everything on a local box. I'm using my Windows credentials for the services and for all authentication. Impersonation mode is Use a specific user name and password i.e. my credentials. No problems.
Moving to QA/UAT, the UAT server is on a child domain of the development domain, with transitive trust relationships established in both directions beween the parent development domain and the child UAT domain. (Everything is in the same VLAN). All ports for all server-based SQL Server instances anywhere in the organisation - both RDBMS and SSAS - are non-standard. 1433 and 2383 are never used.
Now, here's something even more unusual: an SSAS instance was created for me on a new UAT Server, and the UAT Analysis Services service uses a service account created in the parent domain.
As I say, I've never come across anything like this configuration before. Have you?
Now to the problem itself. I've created a SQL Server database on the UAT box, and put my data there. I add the UAT Service account that SSAS is running under in the logins, and ensure that its default database is correct, and that it has datareader privileges. I change the SSAS project configuration to point the Data Source to that database, and verify that I can explore the data OK. I ensure the project properties specify the UAT SSAS instance, and change the impersonation mode to Use the service account.
I attempt to process a small dimension (to validate the overall configuration), and get:
OLE DB error: OLE DB or ODBC error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.; 28000. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'SOURCEDB', Name of 'SOURCEDB'.
I did validate that the two domains had two-way trust. They must, otherwise the UAT SSAS service would not start (in the child domain) using a service account created in the parent domain.
I did try using my local SQL Server database as a data source, using an Windows Account impersonation mode of Use a specific user name and password i.e. my credentials. I was hoping that the SSAS could read the data from my local box. No joy. Same problem.
At this point, I'm stymied. I cannot get the UAT server to process using the UAT server's own service account, or my credentials. Frankly, it's the latter that really puzzles me. Those credentials are the same ones used to do local processing, and they work fine. It's as though they become untrusted when attempring to reach from the UAT SSAS instance in the child domain to the dev RDBMS instance on my PC in the parent domain . . . which doesn't make sense.
All help gratefully received,
- Edited by Lady Donna Thursday, June 14, 2012 9:16 AM
even if you don't have a direction or suggestion for me to try, regarding resolution of the issue, I'd still like to know if you've ever seen this configuration before (the parent-child domain thing with named instances only, using non-standard ports).
Its not one I've come across before and I'd like to know if this set-up is as unusual as it seems to me. So, my question remains:
"As I say, I've never come across anything like this configuration before. Have you?"
Cheers, Donna Kelly
I have seen a similar situation before. The scenario is that the SQL database is in the main corporate domain and the SSAS server is in a separate domain which houses some external client logins. The CLIENT domain trusts the CORPORATE domain, but the CORPORATE domain does not trust the CLIENT domain.
In the above situation, I was able to set the credentials on the data source of the cube to use a CORPORATE domain login and it worked fine. I don't think your logic is wrong.
I would try to remote desktop to the SSAS server under the SSAS service account, open SSMS, and then try to connect to the SQL Server. Make sure it works outside of SSAS first.
unfortunately, for this client, I am but a lowly BI developer, and not a domain admin, nor even possessed of a user account on the server.
Therefore, I cannot "try to remote desktop to the SSAS server under the SSAS service account" although I do agree this would be an appropriate next step.
Rgarding your hypothesis that that the "CORPORATE domain does not trust the CLIENT domain." that doesn't obtain in this situation, I'm afraid. As I said in the OP, "the UAT server is on a child domain of the development domain, with transitive trust relationships established in both directions beween the parent development domain and the child UAT domain".
FYI, I'm now convinced that the problem is a Kerberos authentication issue, and the resolution will lie somewhere in AD, possibly with SPN settings. However, I don't have the privs to follow up this line of enquiry . . . It has been handed to a DBA to sort it out. If it is, I will post the fix here.
- Edited by Lady Donna Thursday, July 05, 2012 11:54 AM