0

I have looked through various posts related to this problem, but none provide an answer. I created a .Net 5.0 app that accesses an Azure SQL DB using EF 6.4.4 which works with .Net standard libraries. I modified the EF by adding a function that creates the connection string from appsettings.json since .Net 5 apps don't use a web.config file. This also works well in Azure with the configuration settings in an app service.

The connection string looks like this:

metadata=res://*/EF.myDB.csdl|res://*/EF.myDB.ssdl|res://*/EF.myDB.msl;provider=System.Data.SqlClient;provider connection string='Data Source=tcp:mydb.database.windows.net,1433;Initial Catalog=myDB;Integrated Security=False;Persist Security Info=False;User ID=myuserid@mydb;Password="password";MultipleActiveResultSets=True;Connect Timeout=120;Encrypt=True;TrustServerCertificate=True'

I also have a deployment pipeline that will deploy the code after a check-in instead of using the Visual Studio publish feature, but the pipeline deployed code has the same problem.

When I first created the app and published it to the app service, it worked. Recently I updated the app with no changes to the EF connection. Now I get the "Invalid Object name when I reference any table in the model. If I run the same code locally and connect to the Azure SQL DB, the DB is accessed as expected. This problem only occurs when running in the Azure app service. Note that there are no connection strings configured for the app service since the EF string is built from the config settings. I saw this post, but I don't think it applies:

Local works. Azure give error: Invalid object name 'dbo.AspNetUsers'. Why?

even though the problem is the same. I have also read various posts about the format of the EF connection string. Since my model is database first, (and the connection used to work), I'm confident the string has the correct format. I don't think the problem is in the code since it works when running locally with a connection to the Azure SQL DB. It must have something to do with the Azure app service configuration, but I'm not sure what to look for at this point. Unfortunately I don't have a copy of the code and publish files that did work to compare to, but it the pipeline build doesn't work either and that it how the code would normally be deployed. Thanks for any insight you might have!

John
  • 511
  • 1
  • 5
  • 10
  • 1
    Try removing `metadata=res://*/EF.myDB.csdl|res://*/EF.myDB.ssdl|res://*/EF.myDB.msl;` and see. – Nishan Mar 06 '21 at 17:19
  • Try my answer, if you need further help, pls let me know. – Jason Pan Mar 08 '21 at 02:23
  • @NishānWickramarathna That didn't work, I got an error when running it locally: "Format of the initialization string does not conform to specification starting at index 0" – John Mar 08 '21 at 06:33

2 Answers2

0

UPDATE

metadata=res://*/EF.myDB.csdl|res://*/EF.myDB.ssdl|res://*/EF.myDB.msl;provider=System.Data.SqlClient;provider connection string='Data Source=tcp:yourdbsqlserver.database.windows.net,1433;Initial Catalog=yourdb;Persist Security Info=False;User ID=userid;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30'

When the troubleshooting problem is not on the string, our easiest way is to use vs2019 to re-use the generated string.

enter image description here

Your connection string should be like below.

<connectionStrings>
    <add name="SchoolDBEntities" connectionString="metadata=res://*/SchoolDB.csdl|res://*/SchoolDB.ssdl|res://*/SchoolDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=SchoolDB;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient"/>
</connectionStrings>

For more details, you can refer my answer in the post and the tutorial.

1. Timeout period elasped prior to obtaining a connection from the pool - Entity Framework

2. Entity Framework Tutorial

Jason Pan
  • 15,263
  • 1
  • 14
  • 29
  • Jason, please note that I am not using a web.config file or app.config file. I don't think the problem is the connection string since it works locally and used to work in Azure. I'm trying to determine what might have changed in Azure when I updated the app service. – John Mar 08 '21 at 06:35
  • @John I know, I mean your connection string is wrong. Please update part of your code about the connection string in the post. – Jason Pan Mar 08 '21 at 06:38
  • @John Try to use my connection string which updated just now. Please check it patiently so that the problem can be solved. If the problem is not solved after the string is processed, please check whether the table structure of azure sql db and local db are consistent. – Jason Pan Mar 08 '21 at 07:10
0

The problem was one of my config settings in Azure. The catalog parameter was missing. A simple fix, but the error message was misleading, so I thought I would note that here in case anyone else gets the same "Invalid object name" message when referencing an Azure SQL DB with EF. It would have been more helpful if the message was "catalog name invalid" or "unable to connect to database".

For those who are curious about building an EF connection string, here is example code:

public string BuildEFConnectionString(SqlConnectionStringModel sqlModel, EntityConnectionStringModel entityModel)
{

    SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
    {
        DataSource = sqlModel.DataSource, 
        InitialCatalog = sqlModel.InitialCatalog, 
        PersistSecurityInfo = sqlModel.PersistSecurityInfo, 
        UserID = sqlModel.UserID, // Blank if using Windows authentication
        Password = sqlModel.Password, // Blank if using Windows authentication
        MultipleActiveResultSets = sqlModel.MultipleActiveResultSets, 
        Encrypt = sqlModel.Encrypt, 
        TrustServerCertificate = sqlModel.TrustServerCertificate, 
        IntegratedSecurity = sqlModel.IntegratedSecurity, 
        ConnectTimeout = sqlModel.ConnectTimeout 
    };

    //Build an Entity Framework connection string

    EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
    {
        Provider = entityModel.Provider, // "System.Data.SqlClient",
        Metadata = entityModel.Metadata, 
        ProviderConnectionString = sqlString.ToString()
    };
    
    return entityString.ConnectionString;
}

Given what I have learned, the properties should be validated before the string is returned. If the string is created this way, all of the connection string properties can be added to the config settings in the app service. I used the options pattern to get them at runtime. Thanks to everyone for your suggestions.

John
  • 511
  • 1
  • 5
  • 10