[Boxlang 1.14] Configuring a SQL Server Datasource (DSN) in .cfconfig.json to Work with Adobe, Boxlang, and Lucee

BoxLang
1

I’m working on migrating some apps from ACF to Boxlang using SQL Server as the database engine. Whenever I tried to communicate with the database, Boxlang would throw an error: Unable to create datasource connection to URL [jdbc:macromedia:sqlserver://127.0.0.1:1433;databaseName=foo;SelectMethod=direct;sendStringParametersAsUnicode=false]

I figured that my database connection information in my .cfconfig.json file must be hardwired for Adobe, and Boxlang didn’t like it. After some experimentation, I was able to resolve the issue by streamlining the datasource definition in .cfconfig.json to:

"${DB_DATABASE}":{
	"host":"${DB_HOST}",
	"dbdriver":"${DB_DRIVER}",
	"database":"${DB_DATABASE}",
	"port":"${DB_PORT}",
	"username":"${DB_USER}",
	"password":"${DB_PASSWORD}",
	"sendStringParametersAsUnicode":false,
	"maintainConnections":true,
	"maxPooledStatements":"100"
}

I also updated the .env file a bit. Note, I removed the old DB_CONNECTIONSTRING or DB_CLASS properties. The key was setting the DB_DRIVER to MSSQL, which seems to be universally accepted by all engines.

# Database Information
DB_DRIVER=MSSQL
DB_HOST=127.0.0.1
DB_PORT=1433
DB_DATABASE=foo
DB_USER=sa
DB_PASSWORD=secret

Also, I learned that I needed to add this in my server-boxlang-cfml@1.json file to install the SQL driver.

"scripts":{
	"onServerInitialInstall":"install bx-compat-cfml --noSave && install bx-mssql --noSave"
}

Just for fun, I also tested this in Lucee@6, and it also worked perfectly.

So, my question is this: Are the changes I made to my .env and .cfconfig.json the 2026 Ortus-approved way to set up a SQL Server DSN that works with all three major engines? Or is there a better way?

I noticed that the .cfconfig.json file in the Coldbox “flat” template, has bunch of additional keys in it. If these aren’t needed for other major DB engines (posgres, mysql, etc), maybe they could be pruned a bit to the bare minimum?

// taken from current from Coldbox "flat" template
"${DB_DATABASE}":{
	"bundleName": "${DB_BUNDLENAME}",
	"bundleVersion": "${DB_BUNDLEVERSION}",
	"class":"${DB_CLASS}",
	"connectionLimit":"100",
	"connectionTimeout":"1",
	"custom":"useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC&useLegacyDatetimeCode=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true",
	"database":"${DB_DATABASE}",
	"dbdriver":"${DB_DRIVER:MySQL}",
	"dsn":"jdbc:mysql://{host}:{port}/{database}",
	"host":"${DB_HOST:127.0.0.1}",
	"password":"${DB_PASSWORD}",
	"port":"${DB_PORT:3306}",
	"username":"${DB_USER:root}",
	"storage":"false"
}
2

I think you can pass just a list of modules to onServerInitialInstall, like this:

"scripts":{
        "onServerInitialInstall":"install bx-compat-cfml,bx-mssql --noSave"
    }
3

Thanks for the tip, @lhoess. I didn’t know you could use a comma separated list. Appreciated!