SQL DB on VM on different domain. authentication works in SSMS & BIDS using 'runas'. How to set up SSAS data source?

Here's my scenario

No AD anywhere (all standalone boxes)

I have an sql server database in a VM in azure. Windows Authentication only. This is my data source

It's a standalone box. I can connect to it using windows authentication by using "RUNAS /netonly /user:VMNAME\MyUserName" with both SSMS and SSDT

I have SSAS installed locally.

I want to deploy a cube locally that connects to the database but I'm not sure how i'm supposed to setup the data source. I've put in VNMame\MyUserName as the impersonation details but to no avail

Error msg from the sql database engine on the azure VM is

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: my.ip.add

May 31st, 2015 9:32pm

States 11 and 12 simply mean the Windows user coming in does not have login access to the server. Valid login but server access failure. SQL Server validated the Windows users token, figured out who it is,
but the Windows user has not been granted access to the server.

http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/

Free Windows Admin Tool Kit Click here and download it now
May 31st, 2015 10:34pm

give rights as DB_Owner to " NT AUTHORITY\ANONYMOUS LOGON " datab
May 31st, 2015 10:49pm

When do you get this error message? Inside SSDTBI when you are setting up and testing the data source? Or after you deployed to the SSAS instance and tried to process the cube on the SSAS server? Did you do runas on SSDTBI?
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2015 5:16am

Hi Greg Processing. I can test the connection (win auth) successfully if I've loaded ssdtbi using /runas. I can explore the data etc But it fails to process upon deployment (in ssdtbi). Same issue trying to process it with ssms. This is a multidimensional cube
June 1st, 2015 9:09am

I believe (though double check me) when you test a data source in SSDTBI with Windows Integrated security selected on the tab where you tell it the Server name it will ignore the impersonation tab and use the runas login. However when you deploy and process it uses the impersonation tab if windows integrated security is chosen on the screen where you set the server name. So I just want to double check you have picked integrated security then on the impersonation tab you entered the sqlservername\username and password. I haven't tested this scenario. What is the SSAS Service account you are using? That anonymous logon error makes it seem like a Kerberos double hop is involved but I don't see how that's the case... or maybe it just means impersonation failed. Another thing you might try is to create a local login on the SSAS computer and the SQL computer with the same username and password and use that in the impersonation settings. That trick helps sometimes.
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2015 10:55am

agree with the first point. The test connection only works if I do runas

This is what the impersonation tab looks like

100% certain I've put in the impersonation data correctly, but when processing (this time trying it from SSMS) it still fails on the AS Engine (local pc) with this error:

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Database', Name of 'Database'.

and on the SQL engine side the error is

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

The SSAS service is running as the default local account: NT SERVICE\MSOLAP$SQL2K14

I changed the ssas service account to be a 'real' user, but same ANONYMOUS LOGON error. If I change the impersonation setting to be 'use service account', I get the 'untrusted domain' error returned on both the SSAS side (processing) and the sql server logs.

But the use specific user account doesn't seem to function when SSAS and the data source are on different domains/workgroups/PCs, or so it appears to me..

June 1st, 2015 7:56pm

Another thing you might try is to create a local login on the SSAS computer and the SQL computer with the same username and password and use that in the impersonation settings. That trick helps
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2015 8:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics