Can't store users in the default MVC application

Question

I had some problems with using the authorization before so I got a brand new everything - new computer, new OS, fresh installation of VS, new app and DB in a new resource group on the Azure. The whole shabang.

I can confirm that I can log in to the Azure DB as the screenshots below show.

I can see the databases, tables, users etc.

The problem is that, although it works locally (using the default connection string provided automagically for me), it doesn't perform very well in the Azure (although I'm using the publish file from there). It said something about the file not being found and according to this answer, I needed to change the connection string.

After I've altered it, I get the following error. Please note that the firewall is open and that I can access the DB when I run the code of my applications. I feel that there's something that goes wrong when the authentication part is automatically configured. I'm out of ideas on how to trouble-shoot it, though.

[SqlException (0x80131904): Login failed for user 'Chamster'. This session has been assigned a tracing ID of '09121235-87f3-4a92-a371-50bc475306ca'. Provide this tracing ID to customer support when you need assistance.]

The connection string I'm using is this.

Server=tcp:f8goq0bvq7.database.windows.net,1433;
Database=Squicker;
User ID=Chamster@f8goq0bvq7;
Password=Abc123();
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=10;

This issue's bothered me for a while and I'll be bounting it in two days. Any suggestion's warmly appreciated.

Answer 1

I believe I've managed to resolve this weird issue. It appears that the user I'm using, despite being admin with all bells and whistles isn't recognized as admin when used in the connection string and trying to create the tables (which is the case at the first registration).

My solution was to create two logins - one with db_owner role and one with db_datareader and db_datawriter. First, I've used the elevated user in my connection string and registered a single user. That created the tables in the database as shown below.

Then, while able to continue as admin, I realized that we should try the demoted user and tada!, it worked perfectly. Once the tables were there, the whole shabeling behaved as expected.

To be perfectly sure, I dropped the tables from the database and there it was - the same issues as before. When I changed to the elevated user, the tables were restored allowing me to get back to the demoted one.

I also tried dropping the tables, confirming the issues to re-appear and then creating the tables manually. That works too! So basically,the only gotcha that caused it all was the original admin who's not treated as admin.

It might have to do with the fact that my Azure account's getting a bit old, LiveID used there is ancient and that didn't have an updated version of DB in Azure (the pull-up to v12 was carried out the 18th of December, so it's possible that it also was a requirement to get it working). I'm too tired and lazy to check that out and I realize that I've no idea how to get an "old" type of account. Besides, the issue will decrease and gradually vanish because the old accounts get upgraded eventually.