MS Azure/Code-First: Login failed for user

Question

I want to deploy my ASP.NET application to MS Azure (and SQL Azure). The application is using code-first.

After publishing my application and calling the corresponding URL I receive an error message saying:

Login failed for user 'myUserName'. This session has been assigned a tracing ID of '8beaff1c-629f-45b3-991b-228425bc30a4'.Provide this tracing ID to customer support when you need assistance.

I'm pretty sure this is due to connection strings in my config files.

Here is the relevant extract from my web.config:

<connectionStrings>
    <add name="dbContext" 
         connectionString="server(local)\SQLEXPRESS;database=myDB;integrated security=true;MultipleActiveResultSets=True" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

And here is the corresponding web.release.config:

  <connectionStrings>
    <add name="ApplicationServices" connectionString="Server=tcp:xt1rnwvt89.database.windows.net;Database=myDB;User ID=myUserName@xt1rnwvt89;Password=myPassword;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True;" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    <add name="dbContext" connectionString="Server=tcp:xt1rnwvt89.database.windows.net,1433;Database=myDB;User ID=myUserName@xt1rnwvt89;Password=myPassword;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True;" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>

I'm not sure if I need both the ApplicationServices and dbContext settings...?

Of course the credentials given above are anonymized. But the real credentials should be correct, because I can successfully connect to my Azure database with SQL Server Management Studio (SSMS).

I already checked several documentations and tutorials like https://www.windowsazure.com/en-us/develop/net/tutorials/web-app-with-sql-azure/, but I couldn't find a solution yet. Everything seems to be ok.

Does anybody have an idea?

Answer 1

I had the same issue (Login failed for user 'myUserName) trying to set up the connection between my webmatrix azure web app to my azure sql database.

Had no problem connecting into the azure sql database using sql server management studio. So I knew that my username and password were ok.

I tried linking the web app to the sql database in the dashboard as suggested however this does not work for me. Spent a long time messing with connection strings in my web.config file. Trying stuff and uploading the new files to test.

Then discovered that the connection string is stored in the web app configuration page in the azure portal dashboard. It turns out that my password still had the curly braces around it in the connection string (as illustrated in the default suggested string). I must have entered it like that at some point. Removing the curly braces solved the issue.

This is another option to try if anyone else is having problems.

Answer 2

This is the connectionstring I use:

<add name="dbContext" connectionString="Server=tcp:xxxxxxxx.database.windows.net;Database=xxxxxxxx;User ID=xxxxxxx@xxxxxxxxx;Password=xxxxxxxxxx;Trusted_Connection=False;Encrypt=True;" providerName="System.Data.SqlClient" />

Also make you sure set the correct firewall settings:

• When deployed you should have the 'Allow other Windows Azure Services to access this server.' option checked.
• When connecting from another network make sure to add that IP to the firewall rules otherwhise you will have a connection denied.

If you want to make sure you use the correct connection you can ask the correct one from the management portal. Just click your databasename on the left and then you can see the connection string on the right panel.

Edit Also when using Code First make sure the database doesn't already exist because the database drop won't work.