Share via

Error while migrating On-Prem SQL DB to Azure SQL

RJ 106 Reputation points
2022-01-10T21:40:21.737+00:00

Hi there,

Happy New Year.

I'm trying to migrate a SQL database (Version SQL 13) from local server to Azure. I do not have access to portal.azure yet however all i have is the connection details with full sys admin access on the azure SQL.

I right clicked onprem DB --> Deploy Database to Microsoft Azure SQL Database --> Entered Azure connection details.

I got error SQL71627: The element user has property authentication type set to a value that is not supported in MS Azure SQL DB v12.
I got error SQL71627: The element user has property IsMappedToWindowsLogin set to a value that is not supported in MS Azure SQL DB v12.
I got error SQL71561: Error validating element [view name] has an unresolved reference to object. External references are not supported when creating a package from this platform.

My question is how do I migrate all of the DB WITHOUT User details since I feel the on prem users details and azure users would be different? Though its the same group of people will be using, they will have to be created new AZ/AD users? Am i correct?

Open to hear any suggestions. Kindly note i do not have portal.azure access yet.

Thanks

Azure SQL Database
0 comments
Accepted answer
  1. Martin Cairney 2,241 Reputation points
    2022-01-10T22:57:17.687+00:00

    I assume that you are using SSMS to do this?

    Have you looked at Data Migration Assistant? The specific steps to migrate a SQL Server database to Azure SQL DB are described here . This provides a better experience as you will get to see and adjust the object creation scripts - so any stray permissions related item can be stripped if it isn't already by the tool. You then as a second step in the tool migrate the data between tables.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,901 Reputation points MVP
    2022-01-10T23:06:44.53+00:00

    If instead of using bacpacs you use Azure Data Migration Assistant (DMA) for your migration project, then life will be a lot easier for you. It does an assessment for you and allows you to migrate effortlessly. You can download the tool from here. With DMA you can uncheck any logins (SQL71627) or views not compatible with Azure SQL (SQL71561) and proceed further with the migration. Here is a tutorial on how to use DMA to migrate an on-premises database to Azure SQL.

    If you want to still try migrating the database using bacpacs, then you try to copy the database with a new name, remove all logins on the new database, and then create the bacpac against the newly created (copied) database. That may help you get rid of error SQL71627.

    To get rid of error SQL71561 you may need to remove a reference from the view to an object that resides on a different database or you can import that object to the database you want to migrate and then update the view accordingly.

    Hope this helps.

    1 person found this answer helpful.
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-01-11T02:27:49.29+00:00

    Hi AJ-AJ,

    Agree with others. It is recommended to use DMA to help the migration.

    Though its the same group of people will be using, they will have to be created new AZ/AD users?

    Yes, we cannot migrate the Active Directory Users and Groups to Azure SQL Database. You can use Azure Active Directory authentication. Please refer to this doc for more details.

    Best Regards,
    Amelia

    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.

    0 comments