Configurar puerta de enlace en power bi.

[marguie]

Hola, estoy creando un modelo en Power bi, el origen es una base de datos de postgredSQL que está en Google cloud platform, pero no consigo configurarla y poder actualizar el modelo.
Me podéis ayudar.

[v-hashadapu]

Hi  @marguie , thank you for reaching out to the Microsoft Fabric Community Forum.

Please refer below step-by-step process to connect PostgreSQL database on Google Cloud Platform (GCP) to Power BI:

1. Go to the Google Cloud Console -> SQL -> Select your PostgreSQL instance. Confirm the instance is publicly accessible or has a static IP address assigned. Navigate to the Connections tab and add your IP address. If SSL is enabled, download the required SSL certificate from GCP.
2. Get PostgreSQL Connection Details like Hostname / IP Address, Port (default is 5432), Database name, Username and password.
3. Download and install the PostgreSQL ODBC driver (psqlODBC) and configure it through ODBC Data Sources on Windows.
4. Open Power BI Desktop -> Click Get Data -> PostgreSQL Database  
5. Enter the server address and database name.
6. Enter credentials and click Connect.
7. If SSL is required, import the SSL certificate.

To update the model in Power BI after connecting to the PostgreSQL database, follow these steps:

A. In PowerBI Desktop, Power BI file -> Home tab -> Refresh

B. Automatic Refresh in Power BI Service:

1. Settings -> Manage Gateways -> Add your data source.
2. Select PostgreSQL as the source type and enter the credentials.
3. go to Datasets -> Schedule Refresh -> Enable refresh and configure the frequency.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[marguie]

Buenos días, 

Los datos los tengo cargados en el modelo pero no me permite actualizar, en desktop me aparece este error:

Y desde la nube al actualizar me aparece este error: 

Error del origen de datos[ValidateMarkupTags]{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[ccon]Instale la versión 4.0.16.0</ip> de Npgsql o una anterior.[/ccon]"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"PostgreSQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"[ccon][ccon]<ip>34.175.193.251</ip>[/ccon]<ip><ip>34.175.0.0</ip></ip>:5432;delonghi-call2summary-pro-conversation-manager-db[/ccon]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.MissingClientLibrary"}}],"exceptionCulprit":1}}} Table xxxxxxxxx

[v-hashadapu · Accepted solution]

Hi @marguie  , thank you for reaching out to the Microsoft Fabric Community Forum.

1. Desktop Error

This error indicates that the SSL/TLS certificate used by the PostgreSQL server is either Expired, Self-signed, Issued by an untrusted Certificate Authority (CA), Or there is a mismatch between the certificate and the server hostname.

Try below solutions:

a. Add the following option in your PostgreSQL connection string:
Trust Server Certificate=true

b. Obtain the root CA certificate from your database provider. Import it into the Windows Certificate Store under Trusted Root Certification Authorities.

c. Validate SSL Settings by ensuring the server uses a valid certificate and checking if the hostname in the certificate matches the database server's address.

d. If this is a development environment, you can disable SSL enforcement in PostgreSQL settings or use SSL Mode=Disable in the connection string.

e. Ensure the machine's date and time are correctly set to avoid SSL validation errors.

2. Cloud Error

The PostgreSQL gateway requires a compatible Npgsql client library (version 4.0.16.0 or earlier), but it's either missing or the installed version is incompatible.

Try below Steps:

a. Download and install Npgsql 4.0.16.0 from NuGet.

b. Place the DLL in the folder C:\Program Files\On-premises data gateway\bin\

c. Open Services in Windows. Find On-premises data gateway and restart it.

d. Go to Power BI Service > Manage Gateways. Confirm the data source is using the latest settings.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[marguie]

Muchas gracias, al final la solución ha pasado por marcar el origen como no cifrado y dar acceso a postgresql tanto a nivel local como de servidor. Ya está el origen integrado y la puerta de enlace configurada. Gracias a todos

[lbendlin]

Can you create a Windows VM in the same Google Cloud?  If yes, then install the Enterprise Gateway on that VM.  

Note:  There is a bug in the gateway installer - npgsql drivers are missing.  You can copy them from the Power BI Desktop.

[marguie]

Hola, como puedo copiar los controladores?

[lbendlin]

Copy them from

C:\Program Files\Microsoft Power BI Desktop\bin\ADO.NET Providers

and paste them into

C:\Program Files\On Premise Data Gateway\m\ADO.NET Providers

(or wherever you installed the gateway to)

All Npg* files and folders.