Can't Export Data-tier Application for Azure

Question

I am trying to create a .bacpac file of my SQL 2012 database.

In SSMS 2012 I right click my database, go to Tasks, and select Export Data-tier Application. Then I click Next, and it gives me this error:

Error SQL71564: Element Login: [myusername] has an unsupported property IsMappedToWindowsLogin set and is not supported when used as part of a data package.
 (Microsoft.SqlServer.Dac)

I am trying to follow this tutorial so that I can put my database on Azure's cloud:

http://blogs.msdn.com/b/brunoterkaly/archive/2013/09/26/how-to-export-an-on-premises-sql-server-database-to-windows-azure-storage.aspx

How can I export a .bacpac file of my database?

Lynnalynne

Answer 1

I found this post referenced below which seems to answer my question. I wonder if the is a way to do this without having to delete my user from my local database...

"... there are some features in on premise SQL Server which are not supported in SQL Azure. You will need to modify your database before extracting. This article and several others list some of the unsupported features.

This blog post explains how you can use SQL Server Data Tools to modify your database to make it Azure compliant.

It sounds like you added clustered indices. Based on the message above, it appears you still need to address TextInRowSize and IsMappedToWindowsLogin."

Ref. http://social.msdn.microsoft.com/Forums/fr-FR/e82ac8ab-3386-4694-9577-b99956217780/aspnetdb-migration-error?forum=ssdsgetstarted

Edit (2018-08-23): Since the existing answer is from 2014, I figured I'd serve it a fresh update... Microsoft now offers the DMA (Data Migration Assistant) to migrate SQL Server databases to Azure SQL.

You can learn more and download the free tool here: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

Lynnalynne

Answer 2

SQL Azure doesn't support windows authentication so I guess you'll need to make sure your database users are mapped to SQL Server Authentication logins instead.

Mantling

A full day spent on this issue. All I had to do was remove my windows authenticated user from the db and add in a SQL auth login. Thanks so much! – Gaudreau

ugh. why isn't there just an IGNORE option for this – Airframe

So I can't export an on-premises DB for use on another on-premises DB, just because Azure doesn't support it? That's idiotic. – Kingcup

Regarding vaindil's response... use sqlpackage.exe. The GUI tools are very limiting for this. You can use sqlpackage.exe Extract/Publish to work with on-prem DBs much easier. Sqlpackage does support Export/Import bacpacs also, but that will take you down the same road. Instead use Extract/Publish dacpacs with /p:ExtractAllTableData=True. – Anaptyxis

Answer 3

As of 9/2018, there is a new tool called the Data Migration Assistant that can help with the task of migrating an existing on-premise SQL Server Database to Azure. While the tool will NOT resolve the issue of migrating Windows Logins, it will at least help you get your database schema and data migrated up to Azure.

Here is the overview of the tool: https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-2017

You can download the Data Migration Assistant here: https://www.microsoft.com/en-us/download/details.aspx?id=53595

Once you have migrated your database up to Azure, you can configure an AD Administrator on the SQL Server, then create contained users in your database to allow users to login to your SQL Database with their Windows accounts.

Inseparable