Azure Mobile Service login failed with database and "lead" user

Question

I use Azure Mobile Services with my applications without any problems, but today, when I tried to disconnect from the service, I get this error:

Exception = System.Data.SqlClient.SqlException (0x80131904): Cannot open the master database requested by login. Login failed. Login failed for user "JVlSvKwDpdLogin_ ***** ".

I never had this problem, and I only connect to my mobile service in code as follows:

public static MobileServiceClient MobileService = new MobileServiceClient( "https://<webservicename>.azure-mobile.net/", "<YOUR-API-KEY-HERE>" ); 

Before this error occurred, I never provided a username or password. I saw some solutions in which they created a user for the database, but I do not want to create it right now, since we are still testing, and now I would prefer to use this service without it. Is this a problem with the mobile service or a problem with the database?

UPDATE

As suggested by Matt below, I found MS_ConnectionString on the Azure portal. Then I connected to the "master" database on my Azure SQL server and searched for the login above. I changed the password to the one found in the connection string using

 ALTER LOGIN <login> WITH password='<password-found-in-connection-string>'; 

But now I get this error:

Exception = System.Data.Entity.Core.ProviderIncompatibleException: An error occurred while accessing the database. This usually means that the connection to the database failed. Ensure that the connection string is correct and that the appropriate DbContext constructor is used to specify or search the application configuration file. See http://go.microsoft.com/fwlink/?LinkId=386386 for information on DbContext and connections. See Internal Exception for failure details. ---> System.Data.Entity.Core.ProviderIncompatibleException: the provider did not return the ProviderManifestToken string. ---> System.Data.SqlClient.SqlException: Login failed for user: "JVlSvKwDpdLogin_ ******* ".

I am not changing anything using the connection string or the web.config file for my AzureMobileService project.

web.config:

 <connectionStrings> <add name="MS_TableConnectionString" connectionString="Data Source= (localdb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-CoverageTool.AzureMobileService-20140910083006.mdf;Initial Catalog=aspnet-CoverageTool.AzureMobileService-20140910083006;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings> 

MobileContext:

 private const string connectionStringName = "Name=MS_TableConnectionString"; public MobileServiceContext() : base(connectionStringName) { } 

Connection string

Data Source = ***** .database.windows.net; Start Directory = sbpconsulting_db; User ID = ******* Login_sbpconsulting; Password = *** *********** ; Asynchronous Processing = True; TrustServerCertificate = False;

Answer 1

This error may also occur if the Entity Framework database initializers are not compatible with the database user permissions that your Azure Mobile Service uses.

For example, when creating a database for Azure Mobile Service, Azure automatically creates a database user for your service. This user does not have administrator rights - he can usually read and write data to a table. In this case, if you use the DBC DropCreateDatabaseAlways initializer, your user will not have sufficient permission to actually delete the database, and you may see the error you indicated.

There are new initializers that have been introduced to work with a limited set of permissions:

• ClearDatabaseSchemaAlways - use DropCreateDatabaseAlways instead
• ClearDatabaseSchemaIfModelChanges - use DropCreateDatabaseIfModelChanges instead

Answer 2

I can only think of one thing that can be a problem. Windows Azure Databases only allow specific IP addresses, which you have white before handing out.

So, if you are trying to run the application from another Internet connection or if your IP address has changed, this may be your problem.

Try to access your database directly on your Azure Management Console and allow your IP address to access the database server.

Azure always needs authentication, so check your applications app.config / web.config for credentials.

More code-based information would be helpful to make this answer more than a shot in the dark.

Answer 3

Do you control your SQL login? Do you have other databases hosted on the same Azure SQL Server? This error occurs on the backend between your service level and the database and will not be affected by anything in client applications.

The portal account does not have the required permissions. Either you found an error, someone revoked permissions for this user, or changed the password.

Based on the error message, I would say that this is the last problem, and you need to find out what the password is and make sure that it is synchronized with the MS_TableConnectionString parameter on the “Configuration” tab of your mobile service. You may need to reset the password for this SQL entry, as well as update the connection string to make sure they are the same.

Answer 4

Another issue that could be the issue is EF migrations. Have you changed your model and enabled migrations? All this will work fine on your local instance, and you can add migrations and update the database. When you move on to deployment, although you want to enable automatic migration to make sure that SQL Azure DB also receives applicable migrations. I have seen people having problems with the same error message (ProviderIncompatible) when it is a migration problem. To enable automatic migration, be sure to go to the configuration.cs file and change the line of code, which sets boolean to false by default.

Answer 5

I also had this problem. My mobile service connected to my Azure SQL database called Diary. This worked fine for several weeks. Then, without any changes on my part, I started getting the error:

Exception = System.Data.SqlClient.SqlException (0x80131904): Cannot open the "master" database requested by login. Login failed. Login failed for user "HwRkAPcQLyLogin_xxxService".

I fixed the problem:

• adding the user "HwRkAPcQLyLogin_xxxService" to my database "Diary"
• assign db_owner permissions to user 'HwRkAPcQLyLogin_xxxService'

I used this handy Azure user management tool: https://aumc.codeplex.com/

I had nothing to change in the main database.