A problem while using the Data-tier Application (DAC) framework

Question

I am trying to use the The Data-tier Application (DAC) framework. Here is what I did.

1) Logged in to a local SQL Server 2008 R2 server using integrated securty (COMPANY\USER_NAME). Created the bacpac file of a database on this server using DacImportExportCli.exe command.

DacImportExportCli.exe -s serverName -d databaseName -f C:\filePath\exportFileName.bacpac -x -e

Bacpac file was created successfully.

2) Now, tried to restore this bacpac file on the SQL Azure DB. Used this command.

DacImportExportCli.exe -s serverName.database.windows.net -d databaseName -f C:\filePath\exportFileName.bacpac -i -u userName -p password

Then, got this message.

Import started: 5/2/2011 2:25:02 PM

Connecting...
Connection Open.
PrepareSystemTables: Pending Preparing DAC metadata in the SQL Server instance '
u548pz4l7z'
PrepareSystemTables: Success Preparing DAC metadata in the SQL Server instance '
u548pz4l7z'
CreateDatabase: Pending Creating database 'eGovRestored'
CreateDatabase: Success Creating database 'eGovRestored'
ScriptDACObjects: Pending Preparing deployment script
ScriptDACObjects: Success Preparing deployment script
CreateLogin: Pending Creating login 'COMPANY\USER_NAME'
CreateLogin: Warning Creating login 'COMPANY\USER_NAME'
CreateDatabaseObjects: Pending Creating schema objects in database 'eGovRestored
'
CreateDatabaseObjects: Failure Creating schema objects in database 'eGovRestored
' Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception
occurred while executing a Transact-SQL statement or batch. ---> System.Data.Sql
Client.SqlException: 'COMPANY\USER_NAME' is not a valid login or you d
o not have permission.

 

I don't understand why it is trying to login using the COMPANY\USER_NAME when I have provided it the username and password for SQL Azure DB.

Can somebody help me please?

Answer 1

Hello! Anyone? Since this technology is in CTP mode, I can find no information elsewhere. Help me please!

Answer 2

That message pops up because when the DAC framework exports your database, it carries the logins with it.  When importing, DAC will attempt to create a login that requires integrated security in SQL Azure.  SQL Azure does not support integrated security and therefore emits an error.  Your .bacpac can still import successfully even after that error, however, that login will not be created.

 

Also, the latest version of the EXE is available here along with some references to additional resources:

  http://sqldacexamples.codeplex.com/

 

Answer 3

Hi IndianProgrammer,

The issue is due to having Windows Login "COMPANY\USER_NAME" in your database, SQL Azure doesn't support Windows Logins.

I suggest you to use SQL Azure Migration wizard (http://sqlazuremw.codeplex.com/) to do an analysis on the database you want to migrate and then fix the reported issues before using DACImportExportCLI

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer