Search code examples
azureazure-sql-databaseconnection-string

Azure error - connection string difficulties

I am working with an mvc5 application in vs 2015. I am getting errors whenever I attempt to access items from the database

Login failed for user '{your_username}'.

When I go azure portal "allow access to azure services" setting is on with my client Ip address listed. I think my connection string is not configured correctly. I can't figure out how to correctly configure it on my local machine and with azure.

I have kept my the connection string in my web config to my local server which worked previously:

In my web.config 

<add name="IdentityDbContext" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Users\UserName\EduSmart_db.Mdf;          Initial Catalog=EduSmart_db;Integrated Security=True" providerName="System.Data.SqlClient" />

In Azure Portal connection string

Server=tcp:xxxx.database.windows.net,1433;Initial Catalog=Edu;Persist Security Info=False;User ID=Name;Password=Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
  • How can I tell which connection string azure is using?
  • Is there a different problem i have not considered?
Solution

  • How can I tell which connection string azure is using?

    Azure use the second connection string. The first one(localdb) just used in your local machine. The server can not get it. You could add Azure Sql connection string name in Context class to specific it.

    Is there a different problem i have not considered?

    You could check from these ways:

    1.The firewall settings in Azure Sql Database. Yo could define a range of ip address(like ×.×.×.0 to ×.×.×.255) instead of only one ip address.

    enter image description here

    2.You could check Azure sql connection string in web.configs file like this. It works fine on my side.

     <connectionStrings>
        <add name="ConnectionStringName" providerName="System.Data.SqlClient" connectionString="Data Source=tcp:[databasename].database.windows.net,1433;Initial Catalog=[databasename];Integrated Security=False;User Id=[username];Password=[password];Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True" />   
   </connectionStrings>

    If you use Code First for Azure Sql, you could add related connection string name in Context class:

     public ApplicationDbContext(): base("ConnectionStringName")
 {
 }

    3.You could click Tools>Connect to Database>Microsoft SQL Server>enter your server name, username and password about Azure Sql. Then click Test Connection button to check whether you could connect successfully.

    enter image description here

    Besides, you could refer this article to learn how to access Azure Sql in your project locally.