Skip to main content

Notifications

Announcements

New Dynamics 365 Community Call! (ERP Edition)
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX forum / DB Restores
Microsoft Dynamics AX forum
Suggested answer

DB Restores

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello All,

In order to move entire data from environment to environment (environment refreshes), the go-to method in AX 2012 or earlier versions, was a DB Restore (and the necessary post-restore SQL changes). 

Our AX7 environments are hosted in Azure. I was successfully able to restore Database from one environment to another, both of type DEMO (LCS terminology) and May 2016 Update. After the post-restore steps (which are of course different here in AX 7, compared to AX 2012) the environments were working fine. 

But I recently attempted a DB restore from an AX7 environment (of type DEMO and May 2016 Update) to another (of type DEVTEST and August 2016 Update) and did the post-restore actions (like replacing the correct AOS name etc,). The AOS service starts successfully, but my login credentials are not being authorized to login.

The error is : "You are not authorized to login with your current credentials. You will be redirected to the login page in a few seconds."

I know that the credentials work for sure. it is with the same User Id that i created the environment through LCS and also, I use the same credentials to login to the source environment (form wherever I took the backup and restored onto this database). 

Not sure if the authentication is not working because of : 
1). DIfferent Versions of AX VM (MAY versus AUGUST), or,
2). DEMO versus DEVTEST, or, 
3). Azure CLassic Cores vs ARM cores: This is something that we do not have control while deploying the environments over LCS. From what I understand, in the recent weeks, any VMs deployed through LCS are being deployed on ARM cores vs Classic cores. 

If anyone know any better alternative to DB restores in AX 7, please let me know. I am aware of the Configuration & data manager (Preview) tool but it is unclear how to use it. 

Thanks,
Ray.

Categories:
Install and Upgrade
  • Hiro Fukutake Profile Picture
    Hiro Fukutake 5 on at
    RE: DB Restores

    Hello.

    I had same issue "You are not authorized to login~",but resolved after database Synchronization.(Andre said)

    Thank you.

    Beside,Because I was interested, I checked S-ID before and after db-sync ,but there was no difference.

    In my case, databases(copy and restored) were in same tenant,so this is not unnatural.

  • Suggested answer
    Chris R Tilley Profile Picture
    Chris R Tilley 339 on at
    RE: AX7: DB Restores

    Hello Ray,

    Late reply but I had the same issue!

    I checked the SIDs in the different userinfo tables and it seems the SID is not unique as per AX 2012 with on premise Active Directories but instead is keyed to your login URL or something like that.

    What I suggest doing is BEFORE you overwrite your target database, keep a copy of the UserInfo table with your admin user details in.  Alternatively you could just rename the old AXDB to AXDB_Old or something so you have access to the userinfo table with correct records.

    When you have restored your source database over the top you then need to update the SID in the userinfo table.

    Assuming your user id is the admin:

    Select SID from userinfo

    WHERE ID = 'Admin'

    Will give you the SID from the old database, then on new one:  (again assuming everything else is correct, like networkalias, networkdomain)

    UPDATE USERINFO

    SET SID = 'output from previous step'

    where ID = 'Admin'

    Hope that's of some use

    Chris

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 290,107 Super User 2024 Season 2 on at
    RE: AX7: DB Restores

    Hi Ray,

    Instead of using these scripts, you may first try to do a database synchronization from within Visual Studio. Probably you deleted or changed a value wrongly.

    I have published a blog today how to start with an empty AxDB in a local machine. kaya-consulting.com/how-to-start-with-an-empty-ax-database-in-your-local-vm

    This blog is not taking care of e.g. the Management reporter database or the batch framework.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AX7: DB Restores

    Hello Andre, thanks for your response.

    1. Created the backup from an Azure deployment and also restored in another Azure deployment. no local VM involved here.

    2. Data Packages: The Scenario here is copying TEST to DEV so that all the Set-up (which is a lot, considering ALL modules) and all Test data comes over to DEV.  I have used Data Entities and Packages earlier to move specific entities between AX 7 environments.  But from what I understand, for my current scenario, it is not the most efficient and accurate way to go, since I would have to export each and every entity and import it. Unless there is a way to easily tell AX to create a Package that would have ALL entities (essentially all data). Please let me know if there is such a way that I am unaware of.

    3. Though it is a website hosted on the IIS, Microsoft has still named it AOSService. The website is what I was referring to, not a Windows Service. When i said "AOS Service starts successfully", I mean...the IIS App Pool, Website and being able to browse the URL and getting the login prompt.

    4. The following is the SQL I execute on the destination AX database, right after the DB Restore. This is just to remove the references to server and AOS names from the source environment:

    use AxDB

    Delete from SYSSERVERSESSIONS

    update SYSSERVERCONFIG set SERVERID = 'Batch:<EnvironmentName>'

    update BATCHSERVERCONFIG set SERVERID = 'Batch:<EnvironmentName>'

    update BATCH set SERVERID = '<EnvironmentName>'

    --update POWERBICONFIG set REDIRECTURL = 'https://<EnvironmentName>aos.cloudax.dynamics.com/oauth'

    update SYSGLOBALCONFIGURATION set VALUE = 'DATABASE SYNCHRONIZER@**********-BLD.REDMOND.CORP.MICROSOFT.COM' where NAME = 'SCOMPERFORMANCEAOS'

     

    My post-DB-restore SQL Script for AX 2012 environments was way bigger with changing and lot more fields in the AX tables to match server names etc.  But most of those seem non-existent in AX 7 or redundant, or plain empty. 

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 290,107 Super User 2024 Season 2 on at
    RE: AX7: DB Restores

    Hi Ray,

    Can you confirm if you created the backup from an Azure deployment and also restored in another Azure deployment? Or did you use for one of these two a local VM? I do think it was both an Azure hosted environment.

    In fact you can use data packages to move data from one environment to another. This is intended to move master data, setup data and "worksheet" data like orders or journals. https://ax.help.dynamics.com/en/wiki/using-data-entities-and-data-packages/

    A full database would contain also all posted transactions. Í have prepared a blog which would be published tomorrow. This will describe how to get an empty database in a local VM. This works without the issue you mentioned.

    What are the exact steps to change data in the database? What do you mean with AOS service starts successfully? There is no AOS service for AX7 anymore. It is a hosted web application on IIS.

Helpful resources

News and Announcements

New Dynamics 365 Community Call! (ERP Edition)

Quick Links

Dynamics 365 Community Update

Welcome to the inaugural Community Platform Update. As part of our commitment to…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Community Spotlight of the Month

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,107 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,954 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans