Connecting to SQL Server using a domain account

dev support
1

Connecting to SQL Server from Lucee using a SQL account is straight forward. Connecting to SQL Server from Lucee using a Windows Domain account is achievable by running the Lucee service as that domain account, selecting integratedSecurity as the connection method and installing the DLL matching the Microsoft JDBC Driver version (e.g. mssql-jdbc_auth-12.4.2.x64.dll)

However, when using the above method of running the Lucee service as a domain account, you can’t have different accounts for different databases.

Can someone explain the SQL Server database settings in the Lucee Administrator? E.g. ActiveDirectoryIntegrated, ActiveDirectoryPassword and ActiveDirectoryInteractive. I know there is a link to https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver16, but it is confusing. This isn’t using Azure that many of the options relate to. Just connecting to a SQL availability group in the same domain.

NTLM settings looked promising “When using authenticationScheme=NTLM, you must specify the Windows domain by using the domain or domainName property, the Windows credentials in the user or userName property, and the password property” but I couldn’t get them to work. I tried multiple combinations like entering the user name as plain username, domain\username format and UPN format. I tried selecting ActiveDirectoryPassword option, but every combination failed.

OS: Windows Server 2019
Java Version: 11.0.23
Tomcat Version: 9.0.90
Lucee Version: 6.0.3.1

2

I found a solution.

  • Enter the “Username” as the short username (sAMAccountName in Active Directory)
  • Enter the “Password” as the Active Directory password
  • Select NTLM as the “Authentication Scheme”
  • Enter the “Domain” using only the first part in a multi-part domain (e.g. lucee if the domain was lucee.org)
  • Select “Integrated Security” = true

It is recommended to TrustServerCertificate.
The password is encrypted in the “C:\Lucee\tomcat\lucee-server\context\.CFConfig.json” file.
The DLL (e.g. mssql-jdbc_auth-12.4.2.x64.dll) is not required.

2 Likes