Neon•2y ago

flat-fuchsia

Connecting to Google Looker Studio

I'm trying to connect Looker Studio to my Neon database but am getting a vague error from the UI:

Sorry, we encountered an error and were unable to complete your request.

Sorry, we encountered an error and were unable to complete your request.

* I can connect to my database using other GUI tools * I've read the discussion in the old forums: https://community.neon.tech/t/connect-to-data-studio-looker-studio/299 * I've tried enabling SSL in Looker and uploading the isrgrootx1.pem certificate from Let'sEncrypt Can anyone provide some guidance on how to troubleshoot?

Neon

Connect to Data Studio / Looker Studio

Hi! 👋 👋 I migrate from heroku recently on my project with hasura, but now I don’t have metrics about my SaaS, is possible to connect to Data Studio? Some idea how? Regards.

36 Replies

foreign-sapphire•2y ago

I tested too, and can't get a connection working. If you open a web inspector the error coming back is JDBC_HOST_NOT_REACHABLE. The error is the same regardless of Enable SSL being ticked. Can you open a support case with Looker? CC @Daniel maybe you can sanity check since it worked for you in the past?

optimistic-gold•2y ago

I tried it without any luck. It worked previously. Something must have changed on the Looker Studio side. I saw a couple of mentions online of using a Google BigQuery connector instead, but I have not explored that path. https://support.google.com/looker-studio/thread/252342730/error-when-connecting-postgresql-to-looker-studio?hl=en

Error when connecting PostgreSQL to Looker Studio - Looker Studio C...

optimistic-gold•2y ago

I tried Postgres 14, too. No luck.

extended-salmon•2y ago

I am also facing the same issue. I changed the JDBC URL query string ssl_mode to require and I got the error below {"reason":23,"code":14,"reasonStr":"JDBC_BACKEND_ERROR","errorCategoryStr":"DATASET_CONFIGURATION","category":5,"uniqueErrorCode":"4660330d"},"params":{"errorId":"4660330d"}

foreign-sapphire•2y ago

Same. Seems likely that something changed in Looker. @[FM] Kethiri Sundaralingam @james-fl0 I suggest opening a ticket with Google to investigate further

extended-salmon•2y ago

I created a ticket https://issuetracker.google.com/326508717. Please follow up if you want to add more details.

foreign-sapphire•2y ago

Thanks! I also commented on the issue

flat-fuchsiaOP•2y ago

thanks for your help!!

flat-fuchsiaOP•2y ago

I was able to connect using a couple of the community connectors

foreign-sapphire•2y ago

Fantastic! Thanks for letting us know. Sounds like the official (?) connector is borked

wise-white•2y ago

Thank you. SynchWith worked for me.

rival-black•2y ago

Another workaround for this issue consists in using an IP address and setting the endpoint name in the password field (https://neon.tech/docs/connect/connection-errors#d-specify-the-endpoint-id-in-the-password-field). Not very elegant, but it did the trick.

Neon

Connection errors - Neon Docs

This topic describes how to resolve connection errors you may encounter when using Neon. The errors covered include The endpoint ID is not specified Password authentication failed for user Couldn't co...

rival-black•2y ago

I completely missed this thread, but had to work on a customer case about this today. While reproducing this problem, I could observe that when clicking on "Authenticate", the connection always failed with

{"reason":28,"errorStatus":{"reason":28,"code":3,"reasonStr":"JDBC_HOST_NOT_REACHABLE","errorCategoryStr":"DATASET_CONFIGURATION","category":5,"uniqueErrorCode":"12f851b2"},"params":{"errorId":"12f851b2"}}

{"reason":28,"errorStatus":{"reason":28,"code":3,"reasonStr":"JDBC_HOST_NOT_REACHABLE","errorCategoryStr":"DATASET_CONFIGURATION","category":5,"uniqueErrorCode":"12f851b2"},"params":{"errorId":"12f851b2"}}

. However, based on our proxy logs, it seems that the auth request never reached our infra. As soon as I replaced the FQDN by an IP, I saw the requests reaching our proxy server. (But looker kept failing, for a valid reason: without EP name in the connection string, our proxy couldn't determine where to route the request. Hence the trick of setting the EP name in the password field :-p )

foreign-sapphire•2y ago

Awesome trick Yanic. I saw the JDBC_HOST_NOT_REACHABLE but wasn't smart enough to try replacing the hostname with IP 😬

harsh-harlequin•2y ago

This is strange. Just last month I got looker studio working without 'password hack' or needing to specify a specific IP: https://github.com/neondatabase/neon/issues/2321#issuecomment-1919091648. What did Google change to break it 🤔 Just to clarify, hardcoding the IP is not a recommended long term solution as our IP addresses are not stable and likely will change over time. Ideally google will revert whatever they changed and fix it to actually resolve the DNS again

GitHub

Connecting with Google Data Suite · Issue #2321 · neondatabase/neon

Steps to reproduce go to https://datastudio.google.com Click "Data sources" Click "+ Create" -> "Data source" Choose "PostgreSQL" (by google) Fill in Host...

harsh-harlequin•2y ago

I thought it might have been our rollout of ipv6 support, but it also does not work in us-east-1 which has no ipv6 support as of yet

$ dig AAAA some-endpoint.us-east-1.aws.neon.tech

; <<>> DiG 9.10.6 <<>> AAAA some-endpoint.us-east-1.aws.neon.tech
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 4484
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;some-endpoint.us-east-1.aws.neon.tech.    IN AAAA

;; ANSWER SECTION:
some-endpoint.us-east-1.aws.neon.tech.    300 IN CNAME us-east-1.aws.neon.tech.

;; AUTHORITY SECTION:
aws.neon.tech.        900    IN    SOA    ns-538.awsdns-03.net. awsdns-hostmaster.amazon.com. 1 7200 900 1209600 86400

;; Query time: 86 msec
;; SERVER: 8.8.8.8#53(8.8.8.8)
;; WHEN: Thu Feb 29 17:22:51 +04 2024
;; MSG SIZE  rcvd: 173

$ dig AAAA some-endpoint.us-east-1.aws.neon.tech

; <<>> DiG 9.10.6 <<>> AAAA some-endpoint.us-east-1.aws.neon.tech
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 4484
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;some-endpoint.us-east-1.aws.neon.tech.    IN AAAA

;; ANSWER SECTION:
some-endpoint.us-east-1.aws.neon.tech.    300 IN CNAME us-east-1.aws.neon.tech.

;; AUTHORITY SECTION:
aws.neon.tech.        900    IN    SOA    ns-538.awsdns-03.net. awsdns-hostmaster.amazon.com. 1 7200 900 1209600 86400

;; Query time: 86 msec
;; SERVER: 8.8.8.8#53(8.8.8.8)
;; WHEN: Thu Feb 29 17:22:51 +04 2024
;; MSG SIZE  rcvd: 173

(our DNS is a recursive resolver, so it would list the AAAA fields from the CNAME in this response)

wise-white•2y ago

Stupid question. Where do I find the IP address...? I'm on a starter plan.

foreign-sapphire•2y ago

@adacow on Linux or WSL you can use dig or nslookup commands with you database's hostname. For example:

nslookup ep-restless-sun-12345.us-west-2.aws.neon.tech

nslookup ep-restless-sun-12345.us-west-2.aws.neon.tech

wise-white•2y ago

Thanks. I see a bunch of addresses. Would any of them do?

foreign-sapphire•2y ago

brother do you know how to obtain the SSL encryption to connect to looker studio? I'm stuck on that part

wise-white•2y ago

I think it's through the ca certificate mentioned above.

foreign-sapphire•2y ago

you right, but the problem solution was this one haha

wise-white•2y ago

I'm in the same boat 😆

foreign-sapphire•2y ago

hahah, the one by SyncWith worked wonderful for me

wise-white•2y ago

It does work but do you not see it being slow?

foreign-sapphire•2y ago

well it kinda is but I'm not the one to judge cus it's my first time using looker

foreign-sapphire•2y ago

Yep, any should do. The issue (as Conrad pointed out) is that there's no guarantee the IPs won't change. Hostname is the ideal option.

wise-white•2y ago

I couldn't get the solution to work for some reason. What buffles me: why does elephantsql work without the ssl full option? Is there a way to test this without the SSL enforced option via Neon? Also it works with 15.4 in elephantsql (not sure if helpful)

harsh-harlequin•2y ago

Neon requires SSL for all connections. Some postgres clients might default to using SSL

optimistic-gold•2y ago

Hey @adacow Odd. I tried it again today with the Neon hostname (not IP address as mentioned above) and the connection worked.

wise-white•2y ago

Sweet. Will try it in a bit. Which region and what version of pg?

optimistic-gold•2y ago

sorry, forgot to show the .pem file:

optimistic-gold•2y ago

I am on us-east-2. This was a Postgres 16 project.

wise-white•2y ago

Nice. Cheers for that detail. It does work for me as well on eu-east-12 v14 Thank you @Daniel 🔥

foreign-sapphire•2y ago

Nice! 👌 I guess Google patched something?

exotic-emerald•2y ago

We managed to get the connection work (only on pooled) and the database tables are perfectly discoverable but now the issue is that I get query issues. I don't think the connector is quering the right stuff. Has anyone had these types of problems?

Gaming

Programming

Connecting to Google Looker Studio

Did you find this page helpful?