Connecting to YellowFin DB through Encrypted (SSL) Connection

Dylan Solomon shared this question 2 years ago
Answered

I am looking for a way to encrypt connections to the YellowFin reporting database from the YellowFin server. I've been able to find documentation on how to encrypt traffic between the browser and the YF website, but not between the website and the DB.


The reporting DB is deployed on a SQL Server instance, and SSL encryption on DB connections is being enforced. I assume I need to make some changes to the JDBCUrl argument in the web.xml file on the YellowFin server to make this work.

Comments (9)

photo
1

Hello Dylan,


Thanks for reaching out with your question. Because of the varying configuration methods of encrypting RDBMS traffic, we haven't gotten around to creating many articles on this. Since you've already configured your SQL Server to force SSL, we'll go ahead and focus on Yellowfin configurations.


When encrypting to a Data Source, this is a straight forward drop down selection to choose SSL connections.


As you've mentioned, all of the connection settings for Yellowfin between itself and its Configuration Database take place in the <YellowfinInstall>/appserver/webapps/ROOT/WEB-INF/web.xml file. In this file, as you've again mentioned, lives the JDBCUrl. This parameter is the JDBC connection string to your Yellowfin Configuration Database. There's extensive information available from Microsoft here about JDBC Connection strings for SQL Server regarding SSL. As a quick example, you can add ';ssl=required' to enable this in your connection.


  1. jdbc:jtds:sqlserver://10.100.1.168:1433/;ssl=required

While the link I've given shows many other options related to SSL, this simple parameter should be what you need to force SSL connections through Yellowfin Configuration.


Please let me know if this information answers your question.


Thanks,

Ryan

photo
1

Hello Dylan,


I wanted to touch base and see if my response helped you achieved your goal.


Thanks,

Ryan

photo
1

No, this doesn't seem to work. I see no change in behavior when adding the "ssl=required" string to the JDBCUrl parameter. I've made sure to restart the Tomcat service after the change as well. Any other ideas?

photo
1

Hello Dylan,


Thanks for the reply. It would be helpful if you could provide some additional details on what you're expecting to see when implementing this. Is your Yellowfin application failing to connect to its configuration database?


Thanks,

Ryan

photo
1

Yes, the Yellowfin application is failing to connect to the configuration DB. To give more details to what I see:

1) When I do not force encryption in SQL Server, the YellowFin service starts up without problem, and reports are displayed.

2) When I turn on encryption in SQL Server, I see the following error when I attempt to go to the YellowFin admin.html page:

1d75d149406dcffca65db05e0351a46e

This error takes a few minutes to appear.

3) When I check the YellowFin logs, I see that there are errors in the jdbc.log file that look similar to this:


2017-06-20 12:11:17  ERROR: Failed to create initial connection at DBConnectionPool startup. Giving up.java.sql.SQLException: I/O Error: DB server closed connection.
4) My YellowFin web.xml JDBCUrl under the JDBC Connection Pooling section looks like this:

jdbc:jtds:sqlserver://servername:1433/DATABASENAME;ssl=required;integratedSecurity=true;domain=NA;useNTLMv2=true;encrypt=true;trustServerCertificate=true
I've tried a number of permutations of the JDBCUrl arguments above, adding/removing various combinations, but they all result in the same error, so I'm not sure they are even being picked up.

photo
1

Hello Dylan,


Thanks for the detailed response. I can see you're using the bundled JTDS SQL Server driver. In previous cases, switching over to the official Microsoft JDBC driver has allowed clients to connect using SSL parameters. You can add the driver to Yellowfin by following this article. At the bottom of the article is a link detailing how to switch drivers once it's installed. I recommend testing this and seeing if it allows you to force SSL. I also recommend having a look through the instance after switching drivers to ensure everything functions as expected.


Please let me know your results.


Thanks,

Ryan

photo
1

I don't see the Plugin Management option from the YellowFin Administration console. Here is the expected menu from the article you linked to:


452278b1d48dca9a6eb2a2c135d7356b

Here is what I see on my YellowFin deployment:

92df05fa23480ab1469f574c630d6d44


Is there a way to add a new JDBC driver on the back-end, rather than having to do it through the YellowFin web portal?

photo
1

Hi Dylan,


As a quick note on this, Plugin Management is likely disabled in your Role Settings. To access this, you'll want to go to "Administration" > "Admin Console" > "Roles" and click your Role. Then expand the "Administration" section and ensure that "Plugin Management" at the bottom of that section is checked. This enables Plugin Management.


49a9b0cb673dc9db17ec34f1cfa2017c


Thanks,

Ryan

photo
photo
1

I have finally figured out the pieces that were missing.


1) I installed the Microsoft JDBC driver you mentioned.

2) The .jar needed to be copied to the LIB folder under my YellowFin installation, and the LIB folder of my JRE installation.

3) The auth DLL needed to be copied to the BIN folders of the YellowFin and JRE installations. Without this, I couldn't use Windows authentication to connect to SQL Server, only SQL authentication.

4) My web.xml JDBCUrl connection string ended up looking like this:

jdbc:sqlserver://servername:1433;databaseName=yellowfinconfigdb; encrypt=true;trustServerCertificate=true;integratedSecurity=true

5) My web.xml JDBCDriverClass was changed to "com.microsoft.sqlserver.jdbc.SQLServerDriver"

6) Once YellowFin was started up, I had to change my data source configurations. Instead of specifying them as Microsoft SQL Server databases, I selected "Generic JDBC Data Source", which allowed me to select the new Microsoft driver I had installed (com.microsoft.sqlserver.jdbc.SQLServerDriver).

7) I had to specify the connection string for my data sources, which ended up looking similar to my JDBC URL above.


Thanks for the help today.

photo
1

Hello Dylan,


Thanks for posting your resolution here. Don't hesitate to reach out if you have any further questions or issues.


Thanks,

Ryan