Entity Framework connection failed to postgres: "The underlying provider failed on Open"

Question

I am testing an Azure function project in a local environment, using Npgsql as a driver to postgres, connection strings defined in local.settings.json instead of App.config.

When I set Npgsql as the provider for Entity Framework and set the default connection factory to NpgsqlConnectionFactory, reading a DbSet in the DbContext produces the following error:

The underlying provider failed on Open. -> 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) -> The system cannot find the file specified

Here is my DbContext:

public partial class PostgresContext : DbContext
{
    public PostgresContext() : base("postgres-key")
    {

    }
}

public class NpgSqlConfiguration:DbConfiguration
{
    public NpgSqlConfiguration()
    {
        SetProviderFactory("Npgsql",NpgsqlFactory.Instance);
        SetDefaultConnectionFactory(new NpgsqlConnectionFactory());
    }
}

Here is my connection string:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "AzureWebJobsDashboard": ""
  },
  "ConnectionStrings": {
    "postgres-key": "Server=127.0.0.1;Port=5432;Database=postgres;User Id=postgres;Password=postgres;"
  }
}

I have tried replacing the Server section of the connection string with localhost, checking to see if the postgres 64 service is running, checked that port 5432 is open, checking whether Entity Framework works with local.settings.json.

The only way I have been able to move forward was creating my own NpgsqlConnection for manual database queries. I am trying to avoid plan B as much as possible and would appreciate any assistance in getting the ORM working.

Update: using .net framework 4.7.1

Are you sure your database in running and port 5432 is open? — kamil-mrzyglod
– kamil-mrzyglod, Commented Apr 27, 2018 at 6:47
I checked the postgres x64 service is running and ran netstat -an in powershell to determine the ports status. It had the state of LISTENING. — Samuel Jenks
– Samuel Jenks, Commented Apr 27, 2018 at 7:02
Also I should add, I can access the database from a python website's debug test, using the same connection string. — Samuel Jenks
– Samuel Jenks, Commented Apr 27, 2018 at 7:04

Rong Zhao · Accepted Answer · 2018-07-05 23:56:11Z

1

Hm.. I am not familiar with .net, however, according to Npgsql's official document, the connection string looks like this:

"Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase"

While yours is:

"Server=127.0.0.1;Port=5432;Database=postgres;User Id=postgres;Password=postgres;"

Why not use same parameters? Such as:

"Host=127.0.0.1;Username=postgres;Password=postgres;Database=postgres"

refer to this link: http://www.npgsql.org/doc/connection-string-parameters.html

And please make sure it listen on all interfaces or lo interface, you can try"

telnet 127.0.0.1 5432

to make sure connectivity be working

answered Jul 5, 2018 at 23:56

Rong Zhao

3181 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Samuel Jenks · Accepted Answer · 2018-07-09 03:12:46Z

I revisited my reproduction of the issue thanks to the surge of helpers recently. I managed to get it working when this stack overflow post made me realize, I had omitted DbConfiguration.SetProviderServices() from the dbconfiguration inheritor. The npgsql documentation is terrible since it failed to mention this was required.

public class NpgSqlConfiguration : DbConfiguration
{
    public NpgSqlConfiguration()
    {
        SetProviderFactory("Npgsql", NpgsqlFactory.Instance);
        SetProviderServices("Npgsql", provider: NpgsqlServices.Instance);
        SetDefaultConnectionFactory(new NpgsqlConnectionFactory());
    }
}

here is my full reproduction (if anyone runs into a similar issue in the future): https://bitbucket.org/DCDprivate/entityframeworkissuereproduction

Collectives™ on Stack Overflow

Entity Framework connection failed to postgres: "The underlying provider failed on Open"

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related