January 17, 2019 at 12:17 pm
Hi there --
I'm trying to connect to an SSAS server via SSMS (the preferred client I'd guess but who knows).
I'm entering
Data Source=xxxx;Initial Catalog=xxx;User Id = xxxxx;password=xxxxx;Provider=MSOLAP.7 (this one is optional I think); Format=Tabular (this one also optional);
The SSMS was version 17.x but I downloaded 17.9 (most recent) as well.
The SSAS version? Well, who knows, I can't connect. The 'creator' is long gone. Actually via Visual Studio it looks like 10.0.5500.0.
ALL CREDENTIALS ARE ACCURATE.
I get the following error
"Element 'return' was not found. Line 5, position 2. (System.Xml)"
The credentials are reading as accurate because changing the user ID or password to anything else results in a different "connection denied" error. So the credentials are being accepted.
ALSO I can connect to the SSAS server just fine using Visual Studio 2015. It connects and shows me all tables.
So WHY the cryptic error in SSMS? What file or .jar or .dll or whatchamacallit BS is necessary here?
Googling the cryptic error message reveals nothing, so I wouldn't try. It only shows one Microsoft Peon claiming it's a connection timeout setting thing and to raise that. It's not that, and raising it does nothing.
So anyone know what to do here?
I'm not even concerned with deducing what the error means per se; is there a checklist in how to connect to SSAS with SSMS? All the credentials are right. I'm using the same connection string in MS Visual Studio 2015.
In Visual Studio 2015, I am selecting ".Net Framework Data Provider for OLE DB" and then "MS OLE DB Provider for Analysis Services Version X.0"
In SSMS, I'm selecting Analysis Services connection, and using the Windows Authentication (with connection string that provides user id and password, again the credentials are being accepted, there is a random cryptic error).
What's going on here? Very frustrating.
January 17, 2019 at 12:36 pm
I haven't seen that error before, but when I click on "Connect" and select "Analysis Services" from the list it doesn't give you the option to specify an initial catalog (database). I'd remove that and also make sure that you're selecting the right connection type in SSMS.
If you're not dealing with Azure AS, then you have to use Windows authentication. Providing a username/password in SSMS will not work. Use "runas" if you're not on the domain.
January 17, 2019 at 12:59 pm
I will try that but --- Analysis Services seems correct (it finds the server whereas the other options, it doesn't)
You can specify an initial catalog either with a backslash \ on the first screen, or in the advanced connection string in Connection Parameters. It appears unnecessary since in Visual Studio 2015 (where it is working) - I can leave it blank and it will find all available catalogs (there is only one).
Yes only Windows Authentication appears to work. However, I CAN pass "fake/ assumed" Windows credentials in via User ID and Password. Then it doesn't give the 'access denied' error but this cryptic one. AND again, I can connect with the same credentials (passed along, not my actual Windows credentials) in Visual Studio 2015.
Not sure what not on the domain means but --- yeah, I'm not on the same website.web.com at the server. But again, works in Visual Studio 2015. Not in SSMS. I wonder why.
I will try the runas thing but I'm not sure about that.
January 17, 2019 at 2:00 pm
peter_parker - Thursday, January 17, 2019 12:59 PMI will try that but --- Analysis Services seems correct (it finds the server whereas the other options, it doesn't)
You can specify an initial catalog either with a backslash \ on the first screen, or in the advanced connection string in Connection Parameters. It appears unnecessary since in Visual Studio 2015 (where it is working) - I can leave it blank and it will find all available catalogs (there is only one).Yes only Windows Authentication appears to work. However, I CAN pass "fake/ assumed" Windows credentials in via User ID and Password. Then it doesn't give the 'access denied' error but this cryptic one. AND again, I can connect with the same credentials (passed along, not my actual Windows credentials) in Visual Studio 2015.
Not sure what not on the domain means but --- yeah, I'm not on the same website.web.com at the server. But again, works in Visual Studio 2015. Not in SSMS. I wonder why.
I will try the runas thing but I'm not sure about that.
A backslash is used when it's a named instance and should not be confused with initial catalog in SQL Server connection strings. If you are dealing with a named instance, then the instance name should be part of the "Server Name" property in SSMS.
The way in which VS and SSMS connects to SSAS is different, and impersonating the user by using the "runas" command is in my opinion the best way to do it in SSMS. I have not tried or experimented with adding username/password in the advanced connection properties, so I cannot comment on that.
Also, if you're not on the same domain as the server you may be dealing with name resolution issues (although not likely if you can connect through VS on the same machine). For fun, try the IP of the server or use the fully qualified domain name in SSMS to see if it makes a difference.
Hope this helps.
January 17, 2019 at 2:05 pm
SSAS tabular only works with Windows authentication. So if the server is indeed in Tabular mode you need to try it that way.
January 17, 2019 at 2:10 pm
1. How come Visual studio loads the SSAS tables with a user ID and password then?
2. I am selecting Windows Authentication. But also specifying a User ID and password. I believe this is already "impersonating" a Windows person.
3. The login credentials I'm using, which were in use by "ye Olde Developer" were never actual Windows credentials (no one used them to log onto a Windows Box. They were never in an Active Directory, ever). Yet they worked for a time. Including, again, in Visual Studio.
Obviously the Windows stack and error codes were written by Sadists, but yeah, I strongly doubt that's the issue. The Credentials are 100% correct. Yes, selecting Windows Auth is necessary in SSMS. I am selecting it. And passing along the impersonated credentials (not sure why MS makes this so overwrought).
Stuff works in Visual Studio. Why. Quite baffling.
January 17, 2019 at 2:15 pm
impersonating the user by using the "runas" command is in my opinion the best way to do it in SSMS.
Not sure how to do this though. The User/ Password were never actual Windows/ Active Directory account. Thusly, I literally cannot 'login' as that user on my Windows machine even if just for the one app. I can only pass it on in connection strings as it was apparently done in Visual Studio. But maybe this only works for 'service account' type stuff.
For fun, try the IP of the server
I'm not fully sure how to enter that in the connection string, but perhaps.
I think I'll try to get another developer to just figure this out.
That, or just have someone create me a brand new username, maybe one that actually IS my Windows login, so no impersonation is required. Hmm.
Thanks for the help guys. Just --- very annoying.
January 17, 2019 at 2:18 pm
peter_parker - Thursday, January 17, 2019 2:10 PM1. How come Visual studio loads the SSAS tables with a user ID and password then?2. I am selecting Windows Authentication. But also specifying a User ID and password. I believe this is already "impersonating" a Windows person.
3. The login credentials I'm using, which were in use by "ye Olde Developer" were never actual Windows credentials (no one used them to log onto a Windows Box. They were never in an Active Directory, ever). Yet they worked for a time. Including, again, in Visual Studio.
Obviously the Windows stack and error codes were written by Sadists, but yeah, I strongly doubt that's the issue. The Credentials are 100% correct. Yes, selecting Windows Auth is necessary in SSMS. I am selecting it. And passing along the impersonated credentials (not sure why MS makes this so overwrought).
Stuff works in Visual Studio. Why. Quite baffling.
1. The username/password that you're prompted for in VS is to connect to the source database connection...the one that's used to populate your cube with data. It is not used to connect to the SSAS instance.
2. You should not specify a username/password when connecting to SSAS in SSMS. Select Windows Authentication and ensure that the user you are running SSMS as is the one with permissions in SSAS. If you're logged in as somebody else, use the "runas" command.
3. See point nr 1.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply