MS SQL Datasource with SSL

[dan...@garciadev.com]

Is there an example for creating an MS SQL datasource using SSL?  Is that something where you have to create the DSN with the type of "Other - JDBC Driver" and provide a connection string?  I saw a reference to that for MySQL with Railo but am not sure if that is still valid for Lucee 4.5x.

Does anyone have an example string that they can share using either the Microsoft or jTDS driver (or both)?  I am looking to create it within the Lucee Admin site.  I searched the Lucee docs and didn't find anything so it could be something to add to the Datasource section on http://docs.lucee.org/guides/cookbooks.html.

Thanks.

-Daniel

Daniel Garcia

[Joseph Gooch]

With the MSSQL driver, you need only add ;encrypt=true to your connection string.

Something like this:

jdbc:sqlserver://servername:port;databaseName=dbname;integratedSecurity=false;encrypt=true

You can verify the connection is encrypted by executing this in SSMS:

select * from sys.dm_exec_connections where encrypt_option!='FALSE'

You can add the encrypt flag to the MSSQL.cfc dbdriver... or use the Other JDBC driver with classname com.microsoft.sqlserver.jdbc.SQLServerDriver

(In our environment, we'd also set sendStringParametersAsUnicode=true and SelectMethod=direct - YMMV)

You might also benefit from the newer MSSQL JDBC driver.  Lucee 5 info here:

https://github.com/goochjj/JDBC-extension-factory/tree/MSSQL-6.0.7507

Check out the branch, ant, copy the mssqljdbc4-6.0.7507 lex into the lucee-server/deploy folder.  

For Lucee 4.5, get the drivers from here:

https://www.microsoft.com/en-us/download/details.aspx?id=11774

The sqljdbc 6.0.7507 package - you want sqljdbc41.jar  (JRE 7) or sqljdbc42.jar (JRE8) depending on your java level.

Connection strings reference here:

https://msdn.microsoft.com/en-us/library/ms378988(v=sql.110).aspx

You may want to specifically read the item for encrypt - as the new driver has an "authentication" property.

Also be aware the certificate will be validated when connecting - so make sure the server name matches the common name. Or pass trustServerCertificate=true.  In my experience SQL has been finicky about this - SSMS in particular seems to match against common name only, but JDBC honors subjectAltNames.  And I think I had issues with case sensitivity too.  (SQL 2012)

-G

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/c67f6efc-47ac-45ea-87d4-0debaa55fded%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Joseph Gooch]

With JTDS it looks like the option is ssl=require

http://jtds.sourceforge.net/faq.html#urlFormat

Don't have first hand experience with this one.

-G

On Thu, Jul 21, 2016 at 3:57 PM, <dan...@garciadev.com> wrote: