CODEWITHSUNDEEP
azureazure-sql-databaseconnection-string
Curious-programmer
Curious-programmer

Reputation: 813

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;

Upvotes: 2

Views: 3702

Answers (3)

SOAL ABDELDJALLIL
SOAL ABDELDJALLIL

Reputation: 416

If you are using MySql InApp here is the solution :

public static string Connect()
        {
            //Build an SQL connection string  
            //MySqlConnectionStringBuilder sqlString = new MySqlConnectionStringBuilder(Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb"));
                var ConStr = System.Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb");
                ConStr = ConStr.Replace("Database", "database");
                ConStr = ConStr.Replace("Data Source", "server");
                ConStr = ConStr.Replace("User Id", "user");
                ConStr = ConStr.Replace("Password", "password");
                ConStr = ConStr.Replace("Source", "server");
                //ConStr = ConStr.Replace("127.0.0.1", "localhost");

                // This line split "server=localhost:[port]" in 
    "server=localhost;port=[port]
                ConStr = ConStr.Replace(":", ";port=");
                MySqlConnectionStringBuilder sqlString = new MySqlConnectionStringBuilder(ConStr);

                return sqlString.ConnectionString;
        }
    }
    public ApplicationDbContext()
            : base(SingleConnection.ConString, throwIfV1Schema: false)

Upvotes: 0

Alberto Morillo
Alberto Morillo

Reputation: 15694

You just need to publish your Web app and database to Azure as explained here.

To publish your web app, right-click the project and select Publish. On the create a site page provide existing SQL Azure server, database and login credentials.

Two database connections will be provided when you publish, one of them is the DBContext. When you publish the Web site with the Wizard data tables are not published.

Since you have configured firewall settings on SQL Azure, to publish the database go to the database project. Right-click the project and select Publish. On the Publish Database dialog, click on Edit and update information regarding the database on Azure. Save the profile and publish the database.

Upvotes: 1

Janley Zhang
Janley Zhang

Reputation: 1587

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.

Upvotes: 3

Related Questions