.Net Ria operation failed after new connection string

Question

Hello

First of all this is not a firewall issue because I can connect to with SSMS and every operation such DDL execution works with a new connection string.

One .Net Ria failed on submitting my dataset. Such as _context.SubmitChanges(submit_Completed, row1); ...

The lines worked for months with old SQL Azure connection string. If it matters, see my connection string in web.config, used by my .Net Ria domain service.

  <connectionStrings>
    <add name="db9Entities1"
connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;tcp:vm64tzkr0o.database.windows.net;Database=db1;User ID=********;Password='*********';Trusted_Connection=False;&quot;" providerName="System.Data.EntityClient" />

Answer 1

Hello,
Is only SubmitChanges failing? Have you tried getting data and is it working normally ?
From what I see here is the provider connection string encoded in the Entities connection string is missing "Server=" before the "tcp:vm64tzkr0o.database...".
And also what is the error?

I am using .NET Ria but with POCO and it had some issues only with SubmiteChanges, it wat throwing some nullreference exception, but since I use POCO I had my own implementention of service operations and my overrides work quite OK.

Answer 2

Thanks for your comment Anton. This is error message I got from server.

"The underlying provider failed on Open"

I have not yet tried just retrieve data because the submitchange() is starting point of my project.

I tried connect after removing the "server=" part in my connection string without success.

Answer 3

Solved.

I created the connection string programatically as follows, checked connectivity, and copy-paste the generate string onto my web.config.

Generated string :
metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='Data Source=tcp:vm64tzkr0o.database.windows.net;Initial Catalog=db1;Integrated Security=False;User ID=****;Password="*****"'

Code generating it:

SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
//sqlBuilder.DataSource = ".\\sqlExpress";
sqlBuilder.DataSource = "tcp:vm64tzkr0o.database.windows.net";
sqlBuilder.InitialCatalog = "db1";
sqlBuilder.IntegratedSecurity = false;
sqlBuilder.UserID = "****";
sqlBuilder.Password = "*****";

EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
entityBuilder.Provider = "System.Data.SqlClient";
entityBuilder.Metadata = "res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";
entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

Console.WriteLine(entityBuilder.ToString());
XElement ele = new XElement("e");
ele.Value = entityBuilder.ToString();
ele.Save("c:/e.xml");

using (EntityConnection con =
    new EntityConnection(entityBuilder.ToString())
    )
{
    con.Open();
    Console.Write(con.State.ToString());
}