Problems with Database Deployment using VS2010 (Package/Publish SQL tab)

Question

Background:
I am using the deployment tools in Visual Studio 2010.
I right clicked my project and selected Package/Publish settings. Put all my settings in there ...

I am then using "web deploy" to tranfer the files to my remote server running a remote agent service and this is working fine. The transforms i have on my Web.Release.config do their thing and the server can access the database I created manually.

Problem:
My next step was to get the Database Deployment working too. I went into the Package / Publish SQL tab and entered my Connection string for the destination database.

(Data Source=MyDBServer;Initial Catalog=Database2;User ID=User;Password=pass)

This database is empty ready to accept the import.

I also enter in the connection string for the source database. This lives on the same server.

(Data Source=MyDBServer;Initial Catalog=Database;User ID=User;Password=pass)

Database Scripting options are set to Schema and Data (changing this makes no difference) and the database scripts are set to [Auto Generated Schema and Data]

When i deploy this now, i get the error:

Error 4 Web deployment task failed.((09/06/2010 16:41:51) An error occurred when the request was processed on the remote computer.)

(09/06/2010 16:41:51) An error occurred when the request was processed on the remote computer. The entry type 'Unknown' was not expected at this time. The serialization stream may be corrupted.

Additional Info:
I can successfully create a package with no problems. I looked at the contents in the zip and can see the SQL is generated fine (so no problems connecting to the database). I can then copy this SQL and run it as a new query on the new database and the tables and data are created fine.

I can not seem to work out where this is going wrong, i googled the error and there are no entries on the whole internet. Anyone have any ideas?

Addendum:
To get some further idea of what might be going on, i sent the package across to the server and imported it using IIS. It told me i needed SQL Server Management Objects. So I installed that. Next attempt it told me my user did not have permission to create the database, I thought excellent this must be the problem. :Granted access - Re-run. Passed! So i deleted all the tables and went back to VS2010 clicked publish and i get the same error. :(

Answer 1

Sorted it!

Thank goodness, i was totally out of ideas when i went back to a video by hanselman. He mentioned that the Web Deployment Agent can have permissions. I went in had a look and there was a tab in it's properties called log on.

I entered the detials of an account with a decent level of access and clicked okay.

I then restarted the service as requested to enable the changes.

I then went back to VS 2010 and clicked Publish Web.

Music to my eyes, i see the words "Publish succeeded", I check the database and the tables are there. Excellent!

I think i scared the office by getting a little over excited, if you get this problem and this solution fixes it for you, try to hold in the temptation to shout out "YES!, yes, get in!" while laughing maniacally or people will think you're weird like me.