Azure Data Studio Error Deploy dacpac

Kelvin Shee 1 Reputation point
2023-05-15T01:20:14.4+00:00

Dear Expert,

I plan migration from on-premise SQL Server Express 2014 to Azure SQL Server.

I extract DACPAC using SQL Server Object Explorer from Visual Studio 2022.

and I want deploy into Azure SQL Server, I using Azure Data Studio.

during extract progress hit error as below message:

Deploy dacpac: Could not deploy package.
Warning SQL0: A project which specifies SQL Server 2014 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15007, Level 16, State 6, Line 1 'DBNAME\Administrator' is not a valid login or you do not have permission.
Error SQL72045: Script execution error. The executed script:
CREATE USER [DBNAME\Administrator];

The database is have schema and I not able using BACPAC to export out. so that I only can use DACPAC to extract out and import back to Azure SQL Server

any expert can help? how I can solve this issue.

thanks.

Azure SQL Database
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
502 questions
Azure Data Studio
Azure Data Studio
A cross-platform database tool for data professionals using on-premises and cloud data platforms on Windows, macOS, and Linux.
106 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 20,480 Reputation points
    2023-05-15T01:43:37.3066667+00:00
    1. Ensure that the login "DBNAME\Administrator" exists in the target Azure SQL Server. Double-check the name and ensure it is spelled correctly. You can do this by connecting to the Azure SQL Server using Azure Data Studio or another SQL client.
    2. Ensure that the login "DBNAME\Administrator" has appropriate permissions to create users in the target Azure SQL Server. It may require the "CREATE USER" permission or membership in a role with sufficient privileges.
    3. If the login doesn't have the required permissions, you can grant them using a different user with sufficient privileges. Connect to the Azure SQL Server with an account with administrative privileges and execute the necessary GRANT statements to provide the required permissions to the login.

    For example, you can execute the following command to grant the necessary permission to theadministrator:

    GRANT CREATE USER TO [DBNAME\Administrator];
    0 comments
  2. Konstantinos Passadis 17,381 Reputation points MVP
    2023-05-15T02:15:13.3066667+00:00

    Hello @Kelvin Shee !

    I understand you are trying to import an SQL 2014 Database into Azure SQL using a DACPAC

    Please read carefully:

    https://video2.skills-academy.com/en-us/azure/azure-sql/database/security-overview?view=azuresql

    Authentication is the process of proving the user is who they claim to be. Azure SQL Database and SQL Managed Instance support SQL authentication and Azure AD authentication. SQL Managed instance additionally supports Windows Authentication for Azure AD principals.

    So if you are using Azure SQL Database Windows Authenication is not supported

    I suggest you add a new user with SQL Authentication mode to the Logins

    Add the roles that the DBNAME\Administrator has

    This can be done from SQL Management Studio from the Logins menu. From the Security-Logins page add new user add a password and change type to SQL Authentication, also go to user mappings and map the Login to the required roles

    Go to the DB that you want to export and from Security re do the process

    Use the same user you created earlier

    Check Stored Procedures and Views if needd , to add the new user

    Retry the Export Import

    You have to recreate the user into Azure SQL Database

    I hope this helps!

    Kindly mark the answer as Accepted and Upvote in case it helped!

    Regards

  3. ShaktiSingh-MSFT 14,276 Reputation points Microsoft Employee
    2023-05-15T06:14:05.02+00:00

    Hi
    Kelvin Shee     •,

    Welcome to Microsoft Q&A forum and thanks for using Azure Services.

    As I understand, you want to plan migration from on-premise SQL Server Express 2014 to Azure SQL Server.

    At first step of extraction of DACPAC using SQL Server Object Explorer from Visual Studio 2022, you got error.

    For the SQL Server 2014, there is a known limitation as below:

    If you use SQL Server 2014 or SQL Server 2012, you must store your source database backup files in an Azure storage blob container instead of by using the network share option. Store the backup files as page blobs. Block blobs are supported only in SQL Server 2016 and later versions.

    Reference document: Migration using Azure Data Studio

    If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.

    Thank you.