Using a Windows domain account in a Lucee MS-SQL datasource

When creating a MS-SQL datasource with the Lucee administrative console it expects the Username to be a SQL account and there is no option to use a domain account.

Following is a method that allows you to use a domain account with Lucee running on Windows to connect to a MS-SQL Server datasource.

  1. Run services.msc and change the account running the Lucee service from using the local system account to the domain account that you will be using to access the MS-SQL database.

  2. As Lucee will need to write to at least the WEB-INF folder, ensure that the domain account has write permissions to the Lucee folders and to the folders where the web pages are. Considering that the default service account was “System” then making the domain account a local administrator is no less secure and simplest.

  3. Edit “C:\lucee\tomcat\lucee-server\context\lucee-server.xml” and around line 38 find the data-source and edit it removing the password, adding the domain user name (without the “domain\” part) and adding the “&integratedSecurity=true” option to the custom parameter like this example.

<data-source allow=“511” blob=“true” class=“com.microsoft.sqlserver.jdbc.SQLServerDriver” clob=“true” connectionLimit=“100” connectionTimeout=“1” custom=“DATABASENAME=XXXXXXX&amp;sendStringParametersAsUnicode=true&amp;SelectMethod=direct&amp;integratedSecurity=true” database=" XXXXXXX " dbdriver=“MSSQL” dsn=“jdbc:sqlserver://{host}:{port}” host=“YYYYYYY” id=“mssql” metaCacheTimeout=“60000” name=“ZZZZZZZ” param-delimiter=";" param-leading-delimiter=";" param-separator="=" port=“1433” storage=“false” username=“UUUUUUU” validate=“false”/>

  1. You also need to download a DLL and save it in a folder that is in the path (typically C:\Windows\System32). Assuming that you are using the Microsoft JDBC Driver 6.2.2.jre8 supplied with Lucee in a 64bit environment then you need to download the matching “Microsoft JDBC Driver 6.2 for SQL Server” from https://www.microsoft.com/en-ca/download/details.aspx?id=55539, extract the 64bit version of sqljdbc_auth.dll and save it in C:\Windows\System32.

  2. Restart the Lucee service.

You can also use the Other… Datasource.

Hi,

This answers most of my questions and I’m hoping you can resolve my last issue… in our application we have dedicated Active Directory accounts for each client, meaning when we setup a new Lucee data source it is for that particular client and has it’s own AD account.

I understand how you need Lucee to run under an AD account and that is fine, however will this give use the ability to setup additional data sources with different AD accounts that will connect to our db?

Thank you for your help!

Kelly

Kelly,

I don’t know how to set up different AD accounts for different datasources. I would like to know how too. The method above is something I discovered by trial and error. Maybe the Lucee team have an idea.

Thank you Peter, I’m going to take your original suggestion and give it a try… I’ll let you know what happens.