CODEWITHSUNDEEP
c#azure-sql-databasebotframework
chlorolupus
chlorolupus

Reputation: 11

Bot server returned HTTP 5xx when trying to connect to azure SQL

I was trying to record the bot and user's activities with Azure SQL following this tutorial. However, the bot returned HTTP 5xx error in the Azure Web app dashboard.

The DB worked locally but not remotely on Azure SQL. I have added all IP of the Web App to the SQL firewall and copied the connectionString of Azure SQL to the publish option. When I tested it using skype the bot did not respond and showed a 5xx server error in the dashboard.

<connectionStrings>
<add name="BotDataEntities" connectionString="metadata=res://*/Models.BotData.csdl|res://*/Models.BotData.ssdl|res://*/Models.BotData.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\BotData.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
    <parameter value="mssqllocaldb" />
  </parameters>
 </defaultConnectionFactory>
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>

Is there something wrong with my web.config which prevented the bot server to send activity to the Azure SQL?

Upvotes: 1

Views: 81

Answers (2)

Eric Dahlvang
Eric Dahlvang

Reputation: 8292

As stated by Alberto: your connection string is incorrectly pointing to a local database. Please try changing it to something like:

  <connectionStrings>
    <add name="BotDataEntities" providerName="System.Data.SqlClient" connectionString="Server=[YourDatabaseServerName];Initial Catalog=[YourDatabaseName];Persist Security Info=False;User ID=[YourDatabaseUserId];Password=[YourDatabaseUserPassword];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" />
  </connectionStrings>
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
    <parameter value="mssqllocaldb" />
  </parameters>
 </defaultConnectionFactory>
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>

Note: The Bot Framework team also has a blog post that walks through saving activities in Azure Sql that might be helpful for you: https://blog.botframework.com/2017/05/05/saving-bot-actions-on-azure-sql-server/

Upvotes: 3

Alberto Morillo
Alberto Morillo

Reputation: 15698

Examining your programming code it references LocalDB everywhere. It is not targeting SQL Azure Database.

Upvotes: 3

Related Questions