Connecting to YellowFin DB through Encrypted (SSL) Connection

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

Hello Dylan,

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

Thanks,

Ryan

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?

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

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:

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.

jdbc:jtds:sqlserver://servername:1433/DATABASENAME;ssl=required;integratedSecurity=true;domain=NA;useNTLMv2=true;encrypt=true;trustServerCertificate=true

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

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

Here is what I see on my YellowFin deployment:

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?

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.

Hello Dylan,

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

Thanks,

Ryan

Hi,

Is there a similar way to connect to Oracle DB as well ?

version: 19C Standard Edition

TSL enabled

Thanks,

Nischitha.

Hi Nischitha,

I am currently testing a similar situation for Nick (also BMC), are you by chance working with the same client on this? I will let you know my findings in the coming days.

Cheers,

Neal