How to call update-database from package manager console in Visual Studio against SQL Azure?

Question

I am using EF 6.1.3. I am trying to call update-database from package manager console against SQL Azure. Everything works fine with local SQL Express 2012. I can successfully connect to the server with SQL Server Management Studio 2012/2014 and with Visual Studio Server Explorer with the same credentials. I have made an exception on the SQL Azure firewall from the management portal already but I receive the error :

Error Number:18456,State:1,Class:14 Login failed for user 'xxxxxxx'. This session has been assigned a tracing ID of 'xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'. Provide this tracing ID to customer support when you need assistance.

The exception is :

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'xxxxx'. This session has been assigned a tracing ID of 'xxxxxx'. Provide this tracing ID to customer support when you need assistance. at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.b__36(DbConnection t, DbConnectionInterceptionContext c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.b__32() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) at System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase() at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run() at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner) at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force) at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0() at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command) ClientConnectionId:xxxxx

The connection string I use is :

Update-Database -SourceMigration xxxxxx -TargetMigration xxxxx -StartUpProjectName xxxxx -ProjectName xxxxx.Migrations -ConfigurationTypeName "xxxxx.MigrationConfiguration" -ConnectionString "Server=tcp:xxxxxx.database.windows.net,1433;Database=xxxxxx;User ID=xxxxxx;Password=xxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" -ConnectionProviderName "System.Data.SqlClient" -Force

I got the connection string from the portal. Does anybody know where is the gotcha or how to troubleshoot this?

I find only one strange thing in the error message : Login failed for user 'xxxxxxx'. The user is actually xxxx@serverName. Is there any escape trick going on here?

Answer 1

As I suspected - the @ character in the username breaks it. For a reason unknown to me the generated username for Sql Azure is username@serverName. When you get the connection string parameter from the Azure Management Portal and put it for EF migrations in double quotes :

-ConnectionString "connString with username@serverName"

you get the "username@serverName" cut-off so the underlying connection provider uses only "username" and not "username@serverName".

Entity Framework reads the username as "username" and not as "username@serverName" and you get rejected by the server. Nearly every program out there has the smarts to check if you have placed or not the @serverName suffix and does the trick behind the scenes. EF is a bit stricter and does not. ( and I like it)

The solution to the problem is simple - turn around the quotes. Surround the connection string parameter with single quotes and the username with double quotes like that :

-ConnectionString 'connString with "username@serverName" .... '

This way the username is preserver as a whole and not cut-off at the @ character and EF connects properly.