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.
-
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.
-
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.
-
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&sendStringParametersAsUnicode=true&SelectMethod=direct&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”/>
-
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.
-
Restart the Lucee service.