ReportServer Forum

JohnLee

Member

Registered: 2016-09-07

Secure Datasource Connection for Remote server

Hi All,

I've been able to connect to remote test servers with no issue and I'm really starting to appreciate the potential of ReportServer.   However, I'm stumped about how to go about connecting to a report postgress database using ssl.  Here's the general config:

ReportServer has SSL enabled. Https connection functional, using tomcat8.
Data Server also has SSL enabled and a web server on top of a postgres db.
Data Server postgress server is using self-signed certs, but the web server is using certs from a vendor.  Postgres has Trusted Cert root cert from vendor...so in theory it should accept encrypted connections from the report server..the report server's cert is from the same vendor.

Hopefully that makes sense. 

Cheers,
John

JohnLee

Member

Registered: 2016-09-07

Re: Secure Datasource Connection for Remote server

Is everyone reporting locally? Surely someone is doing a remote, encrypted connection?

karolina

Member

Registered: 2014-08-09

Re: Secure Datasource Connection for Remote server

Hi,

I used to have remote databases in OpenVPN network, but long time ago I moved everything to a local network. Mostly because the OpenVPN connection wasn't always stable and also it took much more time to execute reports. Note that I have rather very small amount of data to be transfered and still the difference was clearly visible.

Karolina

JohnLee

Member

Registered: 2016-09-07

Re: Secure Datasource Connection for Remote server

Hi,

I used to have remote databases in OpenVPN network, but long time ago I moved everything to a local network. Mostly because the OpenVPN connection wasn't always stable and also it took much more time to execute reports. Note that I have rather very small amount of data to be transfered and still the difference was clearly visible.

Karolina

Most of our infrastructure is in the cloud, so not much of a choice there.  I'm kind of surprised that there aren't more options for secure connections.  EG SSH tunneling or whatnot.

karolina

Member

Registered: 2014-08-09

Re: Secure Datasource Connection for Remote server

Hi John,

There may be more options - I might just be not aware of them.
Ask Arno :-)

karolina

JohnLee

Member

Registered: 2016-09-07

Re: Secure Datasource Connection for Remote server

Hi John,

There may be more options - I might just be not aware of them.
Ask Arno :-)

karolina

Hey Karolina,

I'm hoping I'm just missing something painfully obvious.   I don't know Arno, but hopefully he reads the forums.

Cheers,
John

karolina

Member

Registered: 2014-08-09

Re: Secure Datasource Connection for Remote server

Arno is one of the ReportServer's architects, and also one of the brightest minds I had a pleasure to work with.

I also hope he reads forum (and my e-mails :-)) Let's see :-)

Karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Secure Datasource Connection for Remote server

be careful ... too many kind words might go to my head :-D

Hi John, hi Karolina.

Most databases (including PostgreSQL) directly support encrypted connections. To enable secure connections you usually need to pass one or more parameters as part of the JDBC url. In PostgreSQL, this should be ssl=true. You can find more about using SSL with PostgreSQL on https://jdbc.postgresql.org/documentation/94/ssl.html, but in short, all you would need to do is to use as connection URL

jdbc:postgresql://host:port/database?ssl=true

The above setup, however, has one problem, namely: how can ReportServer be sure to trust the certificate sent by your database server? For this you need to make ReportServer aware of a "truststore" (a store of certificates the client should trust). As far as I am aware, PostgreSQL does not allow you to configure a truststore as part of the connection (in contrast to, e.g., MySQL https://forum.reportserver.net/viewtopi … 2440#p2440). This means that you must add the database server's certificate (or a cert higher up the trust chain) to a truststore that is given to ReportServer during startup. See the above MySQL link for how to create a truststore. As to providing it to ReportServer during startup, you will need to set the following values

-Djavax.net.ssl.trustStore=\path\to\keystore.key -Djavax.net.ssl.trustStorePassword=************

as part of the JAVA_OPTS environment variable. See https://reportserver.net/en/tutorials/i … -practice/ and look for JAVA_OPTS for some further info on where to set this in case you manually set up ReportServer on Ubuntu. In case you used the Bitnami installer, have a look at https://docs.bitnami.com/installer/apps … for-tomcat.

Hope this helps.
Arno

karolina

Member

Registered: 2014-08-09

Re: Secure Datasource Connection for Remote server

Hi John,

Does this solution work for you?
As I also use PostgreSQL, I'd like to know the status - just in case I need sth else than OpenVPN.

Karolina

JohnLee

Member

Registered: 2016-09-07

Re: Secure Datasource Connection for Remote server

Hi Arno, Karolina,

As a result of your help, I am, in fact, closer to establishing a connection.  Thank you!

The current hang up is this error:

SSL error: sun.security.validator.ValidatorException: PKIX path building failed: java.security.cert.CertPathBuilderException: Unable to find certificate chain.

The server I am trying to connect to does have a valid ssl cert issued by Comodo.  It is not a self-signed cert.  Nonetheless, I've imported the
CARoot cert, the TrustCA cert the ValidationSecureServerCA cert and the cert from the server I'm trying to connect to.  All to no avail.

Any suggestions?

Cheers,
John

karolina

Member

Registered: 2014-08-09

Re: Secure Datasource Connection for Remote server

Hi John.

I spoke with my new network & servers administrator. He said this was probably something connected with the Comodo certificate itself. Probably you will have to merge all certificates together (whatever it means).
My knowledge about certificates is no more than that: I know they exist, that one should use them and I know how to set up OpenVPN having tutorial in front of my eyes.
My suggestion is to contact the Comodo technical department.
Looks like I can't help you more :-/

Karolina

JohnLee

Member

Registered: 2016-09-07

Re: Secure Datasource Connection for Remote server

Hey Karolina,

Thanks for the info.  Thought occurred to me, that I may not be understanding which server this error is for?  Is it the postgres system I'm trying to connect to or the reporting server cert? 

The other thing I've noticed is that you can have a self-signed cert for postgres and a CA issued cert for the webserver..in this case an old version of Jetty.  How would it fail to build the chain on a self-signed cert?  (Which I created today.)  If it's the ReportServer cert, how come I don't get any errors when connecting to the RS web interface? 

I could re-issue the cert specifically for tomcat, see if that makes a difference...