FSharp.Data.SqlCient works locally but not on production server

Question

I am having an issue with FSharp.Data.SqlClient. I am building an Asp.net Core full framework 4.6 web app. The main project is in C# which references several projects that are in F#. Each of these F# projects use FSharp.Data.SqlClient for data access. When running the app locally everything works fine, but when I deploy it to azure I get the error "Instance Failure" when the F# code attempts to execute a query.

I have a suspicion that in some way it has to do with how the connection strings are being consumed or maybe some sort of runtime conflict between FSharp.Data.SqlClient and Entity Framework. Entity framework is in the main project to handle the membership data and nothing else. I have to specifically add a connection string to a config file in the main project so that the referenced F# projects can access the database at runtime. Entity framework consumes it's data string via the AppSettings.Json file. I am unaware if there is a better way to wire this up, but again, this works perfectly when I run it locally but not on the server that it's been deployed to.
Is there something that I need to enable or change code-wise for the app to work on the production server?

Here's a view of my data strings. In my F# project I have an app.config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <connectionStrings>
 <add name="DefaultConnection" connectionString="Data 
 Source=server code here" 
 providerName="System.Data.SqlClient" />
 </connectionStrings>
 <startup>
 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7" />
 </startup>
</configuration>

In that same F# project I have another file that I use to access the runtime connection string:

module internal DbAdmin
open FSharp.Data
open FSharp.Configuration
module Admin = 
// runtime connection string
type private Config = AppSettings<"App.config">
let rtConnection = Config.ConnectionStrings.DefaultConnection

In the main C# project I have an app.config file:

<configuration>
 <runtime>
 <gcServer enabled="true"/>
 </runtime>
 <connectionStrings>
 <add name="DefaultConnection" connectionString="Data Source=server 
 code here" providerName="System.Data.SqlClient"/>
 </connectionStrings>
</configuration>

and in the appsettings.json is configured as this:

{
 "ConnectionStrings": {
 "DefaultConnection": "Data Source=Server code Here"
 }
}

this is the actual query code:

type Select_AllArticles = 
 SqlCommandProvider<
 "
 select * from article.vw_AllArticlesAndDetails
 ", Admin.connectionString, ConfigFile = Admin.configFile
 >
 try
 succeedWithMsg 
 (Select.Select_AllArticles.Create(Admin.rtConnection).Execute() |> 
 Seq.toList)
 (createGoodMsg(OK("Some Success Message.")))
 with
 | ex -> fail (createBadMsg(DbError(ex.Message + " --selectAllArticles")))

**Update: ** After remote debugging the app, this is the entire error message it gives

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This is odd to me as the connection string in use is the same one used by Entity Framework which seems to access the data base just fine.

New Update Ok, I think I figured out what's going on. I believe it is definitely a config issue. So as I stated before, in development I had to create a config file in order to create a connection string that FSharp.Data.SqlClient could connect to. I couldn't figure out how to get it to connect to the connection string in the appsettings.json file. Perhaps someone can explain that to me, as that may be the best solution. Anyhow, I followed up on my assumption that the connection string inside of the config file wasn't getting updated on deployment by manually inserting the production server connection string, and then deploying the app. Sure enough the issue was gone, and everything worked normally. So, now the question is, what's the best way to get FSharp.Data.SqlClient connected to the connection string correctly in a core app that utilizes a appsettings.json file? How do I go about handling this issue? I need someone to walk me through it, as I'm new to this.

Current Status

So after realizing that it is indeed a config issue, the question now is how do I properly retrieve the connection settings from the appsettings.json file via my F# projects. Using the FSharp.Data json provider, I need to figure out how to properly locate the appsettings.json file for both production and development use. How can an F# project locate a file in the main project? Am I just overly complicating things?

• Tomas Petricek: What does your code (the bits dealing with connection strings) look like?
• Tomas Petricek: Can you also add the code where you're using FSharp.Data.SqlClient?
• user1206480: Ok added that code as well.
• Tomas Petricek: Hmm, you are passing Admin.rtConnection to the command when creating it, so this bit seems OK to me!
• user1206480: Yes, I feel it has to be a server issue, because as I stated everything works fine locally. I even ran it locally while accessing the live server database to see if I could reproduce the error but could not. Then again it could just be something simple that I am unaware of as I am new to this.

Answer 1

You appear to be looking for App.config to find the connection string. That usually gets replaced with a different .dll.config file when deploying, though that doesn't appear to be the issue. Do you have a DefaultConnection in you Azure App Service connection strings that is incorrect? Other than that, I would suggest parsing the connection string from the appsettings.json rather than the app.config file.

• user1206480: Yes, that is the path that I am on now. Is there a preferred way of doing that from F#.
• Tomislav Markovski: You can use the JsonProvider to parse the app settings.json file. type Settings = JsonProvider<"appsettings.json">. This will give you typed access to the structure. fsharp.github.io/FSharp.Data/library/JsonProvider.html
• user1206480: How do I elegantly go about employing this solution. I'm trying to avoid passing the literal connection string to each query function. I know there has to be a way to access the run time connection string from F#. This is what I was doing via the web config before moving to asp.net core and running into this problem.
• user1206480: How do I properly locate the appsettigs.json file from my F# project for both development and production use. Somehow this all seems overly complicated now.