none
In Migrating from a simple MS Access table to Azure I get Invalid object name sys.server_principals RRS feed

  • Question

  • Hi, I've been asked to migrate an Access db from Access 2010 to SQL on Azure. Starting small, I created a simple Name and Address Table in Access2010 in an accdb file. Then I created a db in azure so I would have somewhere to migrate to. I installed the latest SSMA and followed a couple guides found online as well as the internal SSMA guide. 

    While doing the "Convert, Load and Migrate", the "Convert selected objects" step works.

    In doing the "Load converted objects into the target database" I get the message:

    One of the internal SQL statements failed.
    Invalid object name 'sys.server_principals'.

    Where does that come from and how do i get past it?

    Thanks!

    Will


    • Edited by WillAutio Wednesday, July 3, 2019 4:54 AM
    Wednesday, July 3, 2019 4:53 AM

All replies

  • Hi Will,

    Server-level roles and logins are handled a bit differently in Azure SQL Database than they are in SQL Server:

    •There two server-level security roles in the master database: loginmanager and dbmanager.
    •Instead of sys.server_principals, you use sys.sql_logins in master.

    Please take a look at Managing Databases and Logins in Azure SQL Database for details on the differences between Azure SQL Database and SQL Server for server-level administration.

    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" 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 MSDNFSF@microsoft.com

    Thursday, July 4, 2019 9:46 AM
  • Hi Cathy,

    Thanks for your reply.

    For running SSMA I am connecting to the Azure SQL as the "Server admin login". Isn't that like the supreme being? Aside from that, When I do a "Convert, Load, and Migrate" in SSMA, it is the one that decides to use 'sys.server_principals' (somewhere in its generated code I guess). 

    Ok - I've added the "Server admin login" to the dbmanager list and to the loginmanager list.

    I reconnected from SSMA to Azure SQL Database managed Instance, ran Convert, load and Migrate - with the same results.

    If you have any further insight, I welcome it.

    Best Regards,

    Will

    Friday, July 5, 2019 5:05 AM
  • Anyone have some good ideas for this error?
    Wednesday, July 10, 2019 9:42 AM
  • Hi,

    When you create new project in SSMA (first step in the wizard), can you please make sure you select "Azure SQL Database" in the "Migrate To" drop down?

    You can also enable debug logging in the Tools -> Global Settings -> Logging before running the migration and check the log file (path will be in the same settings dialog. If there turns out to be the query that tried to access "sys.server_principals" when you select "Azure SQL Database" as a target for the project, then please share the log file with us through Help -> Feedback option in the SSMA.

    If you suspect that SSMA generates a query against sys.server_principals as part of the converted code, then you can run just the "Convert Schema" and export converted object to a script file to review what's being generated.

    It is a little bit unclear what your actual target is - Azure SQL Database and Azure SQL Database Managed Instance are two different services, can you please confirm what your "Azure SQL" is? Azure SQL Database Managed Instance supports "sys.server_principals", so there should be no issues querying that view, if you indeed connected to a Managed Instance.

    Regards,
    Alex.

    Tuesday, July 30, 2019 4:52 AM