none
Restoring AdventureWorks Reseller Database RRS feed

  • Question

  • Hi Everyone,

    I'm going through an exercise to restore a database (pg 21 of the following Github file). As instructed, I try to run the following script in PowerShell:

    Invoke-ASCmd –InputFile "F:\Labs\Setup\Scripts\Lab01-Setup-Reseller_Sales.xmla" -Server "localhost\MD"
    pause

    But then I get the following message: 

    <return xmlns="urn:schemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"></root><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="-1056964601" Description="Internal error: The operation terminated unsuccessfully." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1056571392" Description="OLE DB error: OLE DB or ODBC error: Login failed for user &#39;NT SERVICE\MSOLAP$MD&#39;.; 28000; Cannot open database &quot;AdventureWorksDW2016&quot; requested by the login. The login failed.; 42000." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1055784860" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of &#39;AdventureWorksDW2016&#39;, Name of &#39;AdventureWorksDW2016&#39;." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1054932980" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of &#39;Product&#39;, Name of &#39;Product&#39; was being processed." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1054932979" Description="Errors in the OLAP storage engine: An error occurred while the &#39;Color&#39; attribute of the &#39;Product&#39; dimension from the &#39;Reseller Sales&#39; database was being processed." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1055129594" Description="Server: The current operation was cancelled because another operation in the transaction failed." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1056571392" Description="OLE DB error: OLE DB or ODBC error: Login failed for user &#39;NT SERVICE\MSOLAP$MD&#39;.; 28000; Cannot open database &quot;AdventureWorksDW2016&quot; requested by the login. The login failed.; 42000." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1055784860" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of &#39;AdventureWorksDW2016&#39;, Name of &#39;AdventureWorksDW2016&#39;." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1054932980" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of &#39;Product&#39;, Name of &#39;Product&#39; was being processed." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /><Error ErrorCode="-1054932979" Description="Errors in the OLAP storage engine: An error occurred while the &#39;Category&#39; attribute of the &#39;Product&#39; dimension from the &#39;Reseller Sales&#39; database was being processed." Source="Microsoft SQL Server 2017 Analysis Services" HelpFile="" /></Messages></root></results></return>

    I'm not knowledgeable enough to figure out what it means or, more precisely, why it seems to be saying it could not open the database, or that login failed. If there's any other detail you need from me to try to figure out this question then I'm happy to provide it.

    Thanks!

    GL2018

     
    • Moved by Teige Gao Monday, November 26, 2018 1:46 AM
    Friday, November 23, 2018 6:13 PM

Answers

  • If the SQL database is stuck in the "restoring" state I don't think anything else will be able to connect to it. This is most likely why the login is failing. Try and see if you can delete this database and then re-run the setup for it. If you can't delete the database maybe try stopping and starting the SQL Server (or re-booting your VM).

    If all else fails you might just need to delete your VM and create a new one.

    The permissions on your login is not an issue "sysadmin" gives you full admin rights, ticking any of the other options won't have any effect.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by GL2018 Tuesday, November 27, 2018 2:47 PM
    Monday, November 26, 2018 9:35 PM
    Moderator

All replies

  • Hi GL2018,

    So, would you like to restore the SSAS database from Azure VM?

    Can you successfully restore it using SSMS?

    Based on the error message, the NT SERVICE\MSOLAP$MD does not have enough permission to access database AdventureWorksDW2016 database, please try to use other account which has sufficient permission to both the DW database and SSAS instance, check if this issuer persist.

    Regards,

    Pirlo Zhang 


    MSDN Community Support&lt;br/&gt; Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &lt;a href=&quot;mailto:MSDNFSF@microsoft.com&quot;&gt;MSDNFSF@microsoft.com&lt;/a&gt;.

    • Proposed as answer by Tom Phillips Monday, November 26, 2018 1:31 PM
    Monday, November 26, 2018 2:13 AM
  • So there are 2 steps on p21 "Installing the Sample Database" and "Installing the Model".

    The error you are getting is indicates that the "Installing the Sample Database" step either was not done or did not complete successfully. This step installs a relations database that the model then uses to load data from. 

    Try following the steps on p22-23 to check that this SQL database has been installed correctly. If not try re-running that step from the middle of p21.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Tom Phillips Monday, November 26, 2018 1:31 PM
    Monday, November 26, 2018 5:43 AM
    Moderator
  • So there are 2 steps on p21 "Installing the Sample Database" and "Installing the Model".

    The error you are getting is indicates that the "Installing the Sample Database" step either was not done or did not complete successfully. This step installs a relations database that the model then uses to load data from. 

    Try following the steps on p22-23 to check that this SQL database has been installed correctly. If not try re-running that step from the middle of p21.


    http://darren.gosbell.com - please mark correct answers

    Hi Darren, 

    Great point - I thought the database had been fully installed but in SSMS the database name shows as "AdventureWorksDW2016 (Restoring....)" and the "Restoring" part never goes away. 

    When I tried to reinstall the sample database I get the following message:

    F:\Labs\Lab01\Assets>sqlcmd -S localhost -d master -i "F:\Labs\Setup\Scripts\Lab01-Setup-master.sql"
    Restore the AdventureWorksDW2016 database
    ** Database already exists - database not restored **
    Create the NT SERVICE\MSOLAP$MD login
    ** Login already exists - login not created **

    F:\Labs\Lab01\Assets>sqlcmd -S localhost -d AdventureWorksDW2016 -i "F:\Labs\Setup\Scripts\Lab01-Setup-AdventureWorksDW2016.sql"
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'GL1stVM\VM-Admin'..
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "AdventureWorksDW2016" requested by the login. The login failed..

    F:\Labs\Lab01\Assets>pause
    Press any key to continue . . .

    I'm using an Azure VM from my own personal account, so am not sure why the login keeps failing (I'm assuming that there should be nothing that my username shouldn't have access to). The user permissions for VM-Admin seem to be the following:

    Should I just tick all of them? I thought I'd ask before I do it, just in case I do that and make things worse (I'm a novice here).

    Thanks!

    GL2018


    GL2018

    Monday, November 26, 2018 4:49 PM
  • If the SQL database is stuck in the "restoring" state I don't think anything else will be able to connect to it. This is most likely why the login is failing. Try and see if you can delete this database and then re-run the setup for it. If you can't delete the database maybe try stopping and starting the SQL Server (or re-booting your VM).

    If all else fails you might just need to delete your VM and create a new one.

    The permissions on your login is not an issue "sysadmin" gives you full admin rights, ticking any of the other options won't have any effect.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by GL2018 Tuesday, November 27, 2018 2:47 PM
    Monday, November 26, 2018 9:35 PM
    Moderator
  • Thanks Darren - I deleted the database and re-ran the setup for it and it worked smoothly. Not sure why it was stuck in the "restoring" state to begin with, but I'm happy it's resolved. And good to know that "sysadmin" gives me full admin rights.

    Cheers,

    GL2018


    GL2018

    Tuesday, November 27, 2018 2:49 PM