How to enable Data Encryption (SSL) for a JDBC driver..

[pradeepvv82]

Hi,

I am are trying to push data from File-Maker Pro 9 (On a Mac-OS X) to Microsoft SQL Server 2008. I want to encrypt this. I want to enable SSL (Secure Socket Layer) for the connection.

I have ZERO knowledge of Java, JDBC drivers, and Plugins, so I may be asking some things wrongly. Pls excuse me for that.

I bought 360 Works Plugin (http://www.360works.com/plugins/JDBCPLUG/documentation.html#jdbcOpenDatabase) and Microsoft JDBC drivers (sqljdbc4.jar). By default in the JDBC driver the encryption is set as False.

In the Microsoft-JDBC driver documentation, they mentioned the following to enable SSL from a Java application:

String connectionUrl =

"jdbc:sqlserver://localhost:1433;" +

"databaseName=AdventureWorks;integratedSecurity=true;" +

"[color:red]encrypt=true;trustServerCertificate=true";

I am attaching a sample "connectURL.java" code that was given with the JDBC driver.

My question is do I need to write a Java code as the above with "encrypt=true" and compile it on my Mac to enable SSL ?? If so, will the encryption be true even when connecting to SQL Server from File-maker Pro 9 on my Mac??

My other question is, in the 360 Plugin code mentioned in the documentation:

jdbcOpenDatabase("jdbc:sqlserver://db.example.com/invoices")

Will the following work for encryption??

jdbcOpenDatabase("jdbc:sqlserver://db.example.com/invoices;[color:red]encrypt=true ")

Thanks in Advance,

Pradeep

[Smef]

You should be able to pass this as a parameter for your URL when connecting. For example, you might pass:

qlserver://db.example.com/invoices?encrypt=true

with a question mark to specify the URL parameter, I believe. I think a question mark is the correct way to specify it, though you may want to try the semicolon if that doesn't work for you. I was looking at http://support.microsoft.com/kb/873014 and it looks like they use a question mark there, so that's probably the way to go.

You are putting your information in the correct function.

[pradeepvv82]

Thanks David for your reply. I have a question.

Since I got 360 JDBC Plugin , I have plugin functions available in FM 9 Adv. The below is the sample code given in 360 Documentation for connecting to a DB.

jdbcRegister("YOURLICENSEKEY123" ; "Company Name") and

jdbcLoadDriver("com.mysql.jdbc.Driver" ;

"file:///Library/Java/Extensions/mysql-connector-java-3.0.11-stable-bin.jar") and

[color:red]jdbcOpenDatabase("jdbc:mysql://db.example.com/invoices")

So I just need to change [color:orange]"jdbc:mysql://db.example.com/invoices" in the above to something like [color:red]"jdbc:mysql://db.example.com/invoices?encrypt=true" (or) [color:orange]"jdbc:mysql://db.example.com/invoices;encrypt=true"

Will it work ??

Thanks !!

Pradeep

[Smef]

Yes, that would work fine.

I would recommend, however that you split those functions into a few different script steps to help you with the error checking. Do one set variable script step for registering the plugin, and a separate one for connecting to the jdbc database

Set Variable [$reg; jdbcRegister("YOURLICENSEKEY123" ; "Company Name")]



Set Variable [$load; jdbcLoadDriver("com.mysql.jdbc.Driver" ;

"file:///Library/Java/Extensions/mysql-connector-j ava-3.0.11-stable-bin.jar")]



Set Variable [$open;jdbcOpenDatabase("jdbc:mysql://db.example.com/invoices")]

This will allow you to see the results of each function separately in the three separate variables and perform more precise error checking.

[pradeepvv82]

In the last step you mentioned, it should be like this ??

Set Variable [$open;jdbcOpenDatabase("jdbc:mysql://db.example.com/invoices [color:red]?encrypt=true")]

-Venkat

[Smef]

You should remove the space in your URL, but otherwise that looks good.

[pradeepvv82]

Thanks for your detailed explanation.

I will try to connect to SQL server as you said and will update the post.

-Pradeep V