Now that you have setup your SSH tunnel using Putty It’s time to get excel involved. (Not there yet? go back to part 1)
You will need Office 2013 Professional to get access to the software and you will need to download Power Query from Microsoft. Here’s the current link http://www.microsoft.com/en-us/download/details.aspx?id=39379
Before you connect to a PostgreSQL database, the PostgreSQL .Net Data Provider needs to be installed. To install the PostgreSQL .Net Data Provider, see Install the PostgreSQL .Net Data Provider.
Once you’ve installed Power Query and the PostgreSQL adapter you are ready to open Excel. Navigate to the new Power Query tab and select From Database
Power Query From Database
You’ll get a popup to setup your database
Server will be localhost and the tunnel port that you setup before. If you’re following along closely we setup localhost and used the default value of 5432. This plugin doesn’t appreciate non-default port numbers so be sure to use the default.
Database is the name of your target database on the server
You will need to enter your database username and password also. You will likely get the following popup which is a good sign and you should accept.
Power Query Encryption Popup
If you run into trouble leave a comment and I’ll see if I can help.
high im having problems connecting to my postgres database in amazon AWS through power query. i get the following error message
DataSource.Error: PostgreSQL: Failed to establish a connection to ‘devcluster.c5axnj2pfd0o.us-west-2.redshift.amazonaws.com’.
Details:
Message=Failed to establish a connection to ‘devcluster.c5axnj2pfd0o.us-west-2.redshift.amazonaws.com’.
ErrorCode=-2147467259
I have been having a fit with this………..I have a new laptop installed with win 10 pro…..that is the least of my problems….or the start of them.
I get the putty connection okay. I can even connect to psql with the connect just to make sure the user id I am going to use can connect to the database.
I believe this is in the npgsql.dll configuration due to the error in the machine.config pop up i am getting. I have no clue on how to set this up. I have tried the version you gave in the example but that is not working…..the current download does not have the dll files available…not sure if I am getting the right ones or not. Any notes on a win 10 install would be appreciated.
Thanks
John
Any idea what I’m doing wrong?
I get the following error when using the method you describe above:
provider: ‘Npgsql.Tls.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
. The specified hostname was not present in the certificate.
at Npgsql.Tls.TlsClientStream.SendAlertFatal(AlertDescription description, String message)
at Npgsql.Tls.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos)
at Npgsql.Tls.TlsClientStream.TraverseHandshakeMessages()
at Npgsql.Tls.TlsClientStream.d__43.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.Tls.TlsClientStream.d__72.MoveNext()'”