A few days ago, I had to deploy for the first time a .NET Core based application and SQL Server database to the Azure. I had some hard time on the production server getting exceptions like InvalidCastException.
InvalidCastException: Unable to cast object of type 'System.Data.ProviderBase.DbConnectionClosedConnecting' to type 'System.Data.SqlClient.SqlInternalConnectionTds'.
Everything in my app seemed to be fine and it worked like a charm in the local environment, I just copied the database connection string from the Azure dashboard:
I’ve got a tip for you: It’s a trap!
The highlighted link generates a valid connection string, however it’s not going to work properly with your Entity Framework based application due to the problematic flag values:
Server=tcp:{your_server_address};Initial Catalog={{your-db-name}};Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Entity Framework requires MultipleActiveResultSets to be set a True to work properly. I knew that already for many years, but I just didn’t notice that Azure portal generated connection string with invalid value of this flag… Some googling provided me an exellent answer at StackOverflow that made me notice those incorrect values.
Just change them to:
Server=tcp:{your_server_address};Initial Catalog={{your-db-name}};Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
Voila! Your app should now work!