can't access postgresql database on computer with two networks?

[dbadmin]

I am trying to connect to a postgres database that resides on a debian server. My computer where Power BI resides is a windows 10.

The Debian computer has two different networks (two different ethernet ports).  a 192.168.1.0 network and a 172.16.50.0 network.

If the 172 network is disconnected, I can access the database just fine and connect to most of the tables (three don't show up).

If both networks are up (as they NEED to be), I can't access the database at all. 

I've added all the appropriate permissions in the pg_hba.conf file, I've opened up the ports on the debian, I've added the debian computer (both IPs) to the Windows hosts file. 

I'm at a loss -any ideas of what needs to be done or something I could try? I'm not sure why Power BI is having issues connecting with both IPs to begin with. ANY kind of insight would be greatly appreciated! 

Thanks in advance!

[v-yuezhe-msft]

Hi @dbadmin,

When both networks are up, please go to File -> Options and settings -> Options -> Diagnostics and click "enable tracing" in Power BI Desktop, then connect to PostgreSQL database and check the detailed logs in the traces folder. Also please help to post the detailed logs here.  

In addition, could you please install other PostgreSQL GUI tool on the computer that is running Power BI Desktop, then check if you encounter any issues when connecting to PostgreSQL database in the case that both networks are up. Also you can install ODBC driver and create a ODBC data source for PostgreSQL, then connect to the ODBC data source from Power BI Desktop and check if it is successful.

Thanks,
Lydia Zhang

[dbadmin]

DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.3154505Z","Action":"Engine/IO/Db/PostgreSQL/AuthorizationError","ExceptionCode":"28000","Exception":"Npgsql.NpgsqlException:\r\nno pg_hba.conf entry for host \"192.168.1.37\", user \"postgres\", database \"epic_ar_db\", SSL on\r\nSeverity: FATAL\r\nCode: 28000\r\n at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()\r\n at Npgsql.NpgsqlState.ProcessAndDiscardBackendResponses(NpgsqlConnector context)\r\n at Npgsql.NpgsqlConnector.Open()\r\n at Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnectorPool.RequestPooledConnectorInternal(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.WrappedDbConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExtensions.<>c__DisplayClass1.<Open>b__0()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.RunWithRetryGuard[T](Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)","ResourceExceptionKind":"InvalidCredentials","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.1180739"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.2465326Z","Action":"Engine/IO/Db/PostgreSQL/RunWithRetry","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.1870882"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.1504176Z","Action":"Engine/IO/Db/PostgreSQL/RunWithRetry","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.3162108"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.1051327Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.3615122"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.1050214Z","Action":"FirewallDocumentEvaluator/GetResult<Microsoft.Mashup.Evaluator.Interface.IPreviewValueSource>","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.0013101"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.4667675Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":4,"Duration":"00:00:00.0000533"}
DataMashup.Trace Warning: 24579 : {"Start":"2016-11-21T16:32:57.1063411Z","Action":"RemotePreviewValueSource/RunStub","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.ResourceAccessAuthorizationException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: FATAL: 28000: no pg_hba.conf entry for host \"192.168.1.37\", user \"postgres\", database \"epic_ar_db\", SSL on\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.<>c__DisplayClass34.<GetSchemaTable>b__32()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.RunWithRetryGuard[T](Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.GetSchemaTable(Func`2 loader, Boolean openConnection, String collectionName, String[] restrictions)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.GetTables()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.LoadCatalog()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.get_NavigationPropertiesRecord()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbCatalogTableValue.GetEnumerator()\r\n at Microsoft.Mashup.Engine1.Language.Query.SkipTakeEnumerable.GetEnumerator()\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()\r\n at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()\r\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)\r\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()\r\n at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\nInnerException\r\nException:\r\nExceptionType: Npgsql.NpgsqlException, Npgsql, Version=2.2.3.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7\r\nMessage: FATAL: 28000: no pg_hba.conf entry for host \"192.168.1.37\", user \"postgres\", database \"epic_ar_db\", SSL on\r\nStackTrace:\n at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()\r\n at Npgsql.NpgsqlState.ProcessAndDiscardBackendResponses(NpgsqlConnector context)\r\n at Npgsql.NpgsqlConnector.Open()\r\n at Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnectorPool.RequestPooledConnectorInternal(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection)\r\n at Npgsql.NpgsqlConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.WrappedDbConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExtensions.<>c__DisplayClass1.<Open>b__0()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.RunWithRetryGuard[T](Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n\r\n\r\n\r\n\r\n","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.3889590"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:32:57.1049943Z","Action":"RemoteDocumentEvaluator/Service/OnBeginGetResult","evaluationID":"4","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"a6d8b875-1e93-4656-9cd9-4385dd785fba","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.3903767"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-21T16:33:23.7654830Z","Action":"SimpleDocumentEvaluator/GetResult/Compile","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"77949932-f0e4-49fa-862f-c4482bc4febf","Process":"Microsoft.Mashup.Container.NetFX40","Pid":7504,"Tid":1,"Duration":"00:00:00.0010916"}

@v-yuezhe-msft - hello! 🙂 Here is the file. it gives that same "user not authorized" error - but I HAVE added all the proper users to the pg_hba.conf file. Every combination that I can think of. 

Any other ideas or suggestions? I do have pgadmin. I've never had a problem connecting to postgres from Power BI before. This just became a problem when I switched over to debian

[snkbalaji]

Hi dbadmin,

Did you managed to solve this particular issue? 

I am suddenly getting this issue from yesterday onwards, Thank you.