Installing to SQL Server Using Windows Integrated Authentication

This is a quick post on installing Liferay DXP to a SQL Server database.

The Liferay documentation does include the database properties that make up the connection string. But things weren’t as clear when SQL Server is setup for windows integrated authentication, hence this post.

The steps below are presented assuming you are setting up a brand new Liferay installation and pointing it to a SQL Server database using the initial Basic Configuration page that comes up following the first startup of a Liferay server. You could use portal-ext.properties to specify the connection URL provided below.

  1. Extract the Liferay DXP bundle.
  2. Add the jdbc driver jar to the classpath.
    1. Download the Sql Server JDBC Driver and copy the relevant jar to your tomcat/lib/ext/.
  3. Start the server.
  4. When presented with the Basic Configuration screen, click Change to change the database from the hsql that ships with Liferay.
  5. Tweak your connection URL. This is important. Note the integratedSecurity=true.
    1. jdbc:sqlserver://hostname;databaseName=databasename;integratedSecurity=true;
  6. Clear your username and password. You’re using Windows integrated authentication, so you don’t need the database user.
  7. That’s not all though. If you attempt to test your configuration by clicking Finish with the above, you will see an error in the logs.
    This driver is not configured for integrated authentication.
    ...
    ...
    Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
  8. sqljdbc_auth.dll is the missing piece. You should be able to find that DLL file in an auth subfolder of your JDBC Driver download. Place that DLL file somewhere on your file system. IMPORTANT: THIS IS DEFICIENT. SEE COMMENTS AFTER READING. Then make these two changes to your catalina.bat
    1. Add the below line just before rem Execute Java with the applicable properties.
      rem Set the Java library path to help the JVM locate sqljdbc_auth.dll for integrated authentication
      set JAVA_LIB_PATH=c:\app\drivers
    2. After rem Execute Java with the applicable properties, there should be a few calls to run tomcat in different execution scenarios. For each of those calls, insert the JVM parameter, java.library.path, and assign the value of the environment variable %JAVA_LIB_PATH% as its argument. One such call is shown below with the new JVM argument shown in bold.
      %_EXECJAVA% %LOGGING_CONFIG% %LOGGING_MANAGER% %JAVA_OPTS% %CATALINA_OPTS% %DEBUG_OPTS% -D%ENDORSED_PROP%="%JAVA_ENDORSED_DIRS%" -classpath "%CLASSPATH%" -Dcatalina.base="%CATALINA_BASE%" -Dcatalina.home="%CATALINA_HOME%" -Djava.io.tmpdir="%CATALINA_TMPDIR%" -Djava.library.path=%JAVA_LIB_PATH% %MAINCLASS% %CMD_LINE_ARGS% %ACTION%

      Make sure you tweak all the Tomcat execution scenarios.

  9. Restart Liferay.

Blogs

I usually just copy the sqljdbc_auth.dll in the tomcat bin folder. If you do that, it is automatically found. In any case: you shouldn't change catalina.bat. If you need extra java parameters, add them to setenv.bat.

That’s great! I should have thought to try bin. 

And I did feel queasy changing catalina.bat.

Thank you!

--

Quick edit here:

As Christoph points out, I should have added my var to setenv.bat appending it to CATALINA_OPTS - that gets included in all execution scenarios.

Also, on Windows the java library path is the PATH environment variable, so in theory, I could have also added it to any directory included in PATH.  But tomcat's bin seems convenient and I can't think of a downside to it.

I'm going to leave the original post unchanged so the comments thread makes sense.

can u say the steps to configure liferay in localhost:8080?