Testing migration of SQL/SSAS from 2017 to 2019, when editing an SSAS database connection in SSMS 18.12.1 results in an error - Object reference not set to an instance of an object. (Microsoft.AnalysisServices.SsmsManagementDialogs)

Spencer-Jones, Mike 6

After updating connection string properties, SSMS returns
TITLE: Microsoft SQL Server Management Studio

------------------------------

Object reference not set to an instance of an object. (Microsoft.AnalysisServices.SsmsManagementDialogs)

------------------------------

BUTTONS:

------------------------------

NOTE: it appears to work in SSMS 17.9.1

Also when running the SSAS cube refresh from a SQL Agent job (SQL 2019) we now get the following
Note. Analysis Server has persisted any security information specified as part of the Connection string portion of the Datsource object definition

07/13/2022 15:43:56,TDW_SSAS_Refresh,Error,1,XXXXXXXXXX_SSAS_Refresh,Refresh Cube,,Executed as user: UUUUUUUUUU. <return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Warning WarningCode="1092091904" Description="Note. Analysis Server has persisted any security information specified as part of the Connection string portion of the Datsource object definition." Source="Microsoft Analysis Services" HelpFile="" /></Messages></root></return>. The step failed.,00:00:16,0,0,,,,0

Manually processing the cube works fine.

Spencer-Jones, Mike 6 Reputation points

2022-07-21T16:42:25.89+00:00

Further note: The cubes are successfully refreshed, but the refresh result (either manually in XMLA window or through SQL Agent job) returns the warning message causing the job to report failure.

SSAS 2019 result
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Warning WarningCode="1092091904" Description="Note. Analysis Server has persisted any security information specified as part of the Connection string portion of the Datsource object definition." Source="Microsoft Analysis Services" HelpFile="" />
</Messages>
</root>
</return>

SSAS 2016 result
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>

If the SSAS2019 refresh job step is set to retry on failure it will generally succeed on the 2nd attempt, but not all the time.

1 answer

Joyzhao-MSFT 15,581 Reputation points

2022-07-14T02:46:46.673+00:00

Hi @Spencer-Jones, Mike ,
This is because SSAS can not process data. I think it's because of your impersonation setting in your connection. check your connection setting and test it again.
I would suggest you to set the impersonation information as "Specific Windows user name and password" . This option specifies the model use a Windows user account to import or process data from the data source. The domain and name of the user account uses the following format:<Domain name>. When creating a new model using the Table Import Wizard, this is the default option.

Executed as user: UUUUUUUUUU. <return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Warning WarningCode="1092091904"...

Check the permission for the user that was mentioned in the message. Make sure it has the processing permission.
Best Regards,
Joy

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Spencer-Jones, Mike 6 Reputation points

2022-07-20T09:33:42.017+00:00

Thanks for the suggestion.

The impersonation settings were the same as on our old SQL 2016 environment which is working

When editing the connections in SSMS 17.9 we get a message
The SQL Server authentication credentials you specified for the connection will be persisted on the server. These credentials will be used when processing the data.
Do you want to save your changes and persist your credentials?
Yes No

SSMS 18.12 doesn't show this, just a GUI error.

We have had mixed success with updating the connections in 17.9 and being able to run the refresh job, but not for all cubes which is strange

Silviu Vladutu BCR 0 Reputation points

2024-09-26T10:16:56.4066667+00:00

I found a solution to this problem on a Oracle connection. But in my case the problem was when i tried to refresh a partition of a specific table. The refresh on the table worked perfectly, but on the partitions were problems.

Please check the file Model.bim. Open it in Notepad or Notepad++.

Then please see that the line with the connection string to have

"Persist Security Info=true".

Hope to solve the problems.

Regards,

Silviu
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Testing migration of SQL/SSAS from 2017 to 2019, when editing an SSAS database connection in SSMS 18.12.1 results in an error - Object reference not set to an instance of an object. (Microsoft.AnalysisServices.SsmsManagementDialogs)

1 answer

Your answer