SSMS (400) Bad Request error attempting connect to premium capacity dataset

[Anonymous]

I'm trying to connect to data in a Premium Capacity workspace from SQL Server Management Studion v18.4

1. I get the workspace connection from Workspace Settings
2. choose login with Azure Active Directory - Universal with MFA (I get prompted for my corporate federated login, which succeeds)
3. am returned a (400) Bad Request error.

Any ideas or cause or remediation?

[JirkaZ]

@Anonymous Analyze in Excel is not enabled for the capacity. Hence the error message.

[Anonymous]

Thanks @JirkaZ - this is certainly the right path, but something still does not make sense.

I used Fiddler to look at the response header and found just what you suggested:

{"code":"BadRequest","subCode":0,"message":"XMLA endpoint feature is disabled. Turn on Analyze in Excel feature in PowerBI.com to enable this feature.","timeStamp":"2020-03-04T15:21:33.0663841Z","httpStatusCode":400,"hresult":-2147467259,"details":[{"code":"RootActivityId","message":"b22d0448-03c6-4ba3-84a5-cecd6c67162f"}]}

However, Analyze in Excel is working fine on this dataset and all others.  This only occurs within SSMS (and Profiler and DAX Studio).

I'm trying to contact the tenant admin to learn more about the settings for our service, but can anyone suggest why this would happen when Analyze in Excel seems to be turned on?

[Anonymous · Accepted solution]

OK, we have it working.

Something was hosed up in Service Admin.  We had had Analyze in Excel enabled and it worked across the tenant.  SSMS even worked at one point.

Today, upon examination, it was set to disabled - even though Analyze in Excel for Excel connections continued to work.  Admin set it to enabled and now access in Excel, SSMS and DAX Studio all work again.

Happy ending (at least I hope it stays this way), but a big waste of time.

Dale

[GilbertQ]

Hi there

First thing I would say is make sure you got the latest version of SSMS installed.

Also make sure you got all the latest drivers installed too.

Here is a great blog post by Chris Webb who details on how to connect.

https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/

[Anonymous]

Thanks for your reply, but 18.4 is the latest release of SSMS (I installed last week).

I used Chris‘a post as a guide and even tried the profiler as he did with the same result.

I will also note that Analyze In Excel works with no trouble.

Still hoping someone recognizes this issue and has a suggestion.

[GilbertQ]

Can you confirm that you have got the latest drivers installed?