MS SQL Datasource with SSL

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

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.750
https://github.com/goochjj/JDBC-extension-factory/tree/MSSQL-6.0.75077

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)

-GOn Thu, Jul 21, 2016 at 3:57 PM, <@daniel> wrote:

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


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+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/c67f6efc-47ac-45ea-87d4-0debaa55fded%40googlegroups.com
https://groups.google.com/d/msgid/lucee/c67f6efc-47ac-45ea-87d4-0debaa55fded%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.