What version of MS SQL?
Did you verify that you can manually login to the SQL server with the given username / password using MSSS or sqlclient from the lucee installed machine and others?
Is SQL Agent running?
Is port 1433 blocked on either the client or the server?
What language settings do you have on the MS SQL Server?
Is the MS SQL Server fully patched
What language options did you set when you installed the database?
Does the user connecting to the MSSQL Server / DB or table have the correct permissions?
Does the MS SQL Server use mixed mode, windows mode, or SQL login permissions?
If all the above tests are false, then create a user call it something all lower case like luceedbtester
assign it a lowercase password with no special characters like gh0stluceetest3r
assign it the permissions db public and dbo owner to newly created db
sign in locally (at the sqlbox) with the newly created user
if that works try to sign in across the network
if that works try again with lucee
if that works, buy lucee foundation or @Zackster a beer
Do you mean to say that Lucee doesn’t support using Windows Auth for datasource logins. Adobe supports this. While I’ve never used it myself in Lucee, I was pretty sure I had seen people online who got it working before. I believe the process is normally to run CF as the service account and then just don’t specify a username or password. It sounds like perhaps Lucee should stop making assumptions about whether or not a username will be required or not. It seems like Lucee is trying too hard to validate several things which shouldn’t be required in all cases.
In Adobe CF 9+ you do have to specify which DB you are assigning to the user, unless of course you are not correctly assigning database permissions.
The correct procedure is
Create a database
Click the Security Tab
Add a user for the application_X
give assign the user for application_X to the Database with the correct permissions.
Assign the user map credentials to the Database the user for application_X is using.
I strongly strongly recommend using 1 user for read operations, 1 user for write operations, and custom permissions for anything else.
DBO allows for creation of functions and views, and if say someone was a lazy admin and used Windows Integrated Security then potentially
bad actor could create a view or custom function to write a bit of custom code to do all kinds of bad things in record time.
By default, the Windows install of MS SQL Server if you blindly click through everything, installs a special user with network permissions. The network permissions include packet filtering.
I will stop ranting, just Yeah. If you are using MS SQL with DBO, secure it now as the alternative is not good.
But this seems to only be a UI problem. JDBC driver will def work without ;databaseName= attached.
For that matter, does Lucee have an integratedSecurity attribute?
In the past I’ve just used a generic datasource with the MSSQL driver if the wizards didn’t let me configure the datasource how I needed to.
That said, I’ve also had datasources in Adobe that have no database specified and no username or password specified, because I supply the credentials on the cfquery tag.
So it sounds to me like perhaps Lucee is being more strict than it needs to be here.
WRT Native Auth- I haven’t tried it w/ Lucee or in an OSGI environment, but I’ve definitely done it in Java before. It would likely require additional config, because the JDBC driver needs a DLL to be able to use the windows auth token… it needs to know where to find that dll, and the DLL has to be there, and I’m not sure the lucee extension provides the DLL. (I don’t know one way or the other)
We do what Terry does - we use SQL Authentication with proper least privilege on the user accounts.
The issue with MS SQL Server cfquery method is if for some reason your tomcat instance stops, but your front end server continues to try to execute the page.
Now you have Your username, password not to mention names of your table(s) columns, variables all for the world to see.
Where if you leave it in the server config, if tomcat crashes, they may get your query details, but minus the username and password, which depending on how you set up your code, they may not actually have anything to show other than some obscure code that queries a cached table for the actual sql statement.
Hey Terry, that’s the first I’ve heard of ACF using dcom for sql server (or indeed any) integration. Is that about this trusted auth specifically, or any connection? And does your your statement about that come from specific confirmation by anyone, or is it perhaps just something you’ve heard or speculate?
Sincere questions, which may fill a gap in my own knowledge. Thx
If you are using a custom CFX tag, which was all the rage many moons ago, you might have DCOM objects.
If you are running anything that uses ODBC connector(S) changes are you are hitting dcom
This comes back to an OS engineering more issue.
Now add Coldfusion 8 (around page 1000K)
Adobe dropped Com / COBRA in their latest version, but the real issue isnt CFX tags or DCOM being dropped by CF or being picked up or just not upgrading, but the issue with the OS no longer allowing DCOM bypass as of mid next year.
Now it gets better, if you blindly click through the install, then never bother configuring the port for your database and blindly just add a database.
The installer will add “CFmlsEngine Host”, which has access to the network service, “Local Service, System, Self and Network Service & the privileged account group that installed the software, usually HOSTMACHINENAME\Administrators”
Sorry for the many edits, been in and out of meetings all day.
I wasn’t sure it was DCOM. I do configure DCOM on the SQL server itself, because it does talk over DCOM when distributed transactions (and cross-server joins/queries) are used, but I didn’t think the JDBC driver used it. My understanding was it was a DLL used in a JNI manner to access native windows calls to retrieve and reuse the kerberos tickets of the service account or logged in user.
That said there was some way to use a java kerberos library too. But this isn’t the way I do things so I’m not an authority.
For that matter the MSSQL JDBC driver is open source and in github now so someone so inclined could dig to their heart’s content.
Download the DLLs
For some features (e.g. Integrated Authentication and Distributed Transactions), you may need to use the sqljdbc_xa and mssql-jdbc_auth-<version>.<arch> DLLs. They can be found in the package that can be downloaded from Microsoft. mssql-jdbc_auth-<version>.<arch> can also be downloaded from Maven.
I agree with Terry that the “Distributed Transactions” part likely uses DCOM. In our scenario we wouldn’t do that, I’d just read from one server and update another, why make it complicated.
Note that both the DLL method and the JavaKerberos method are mention.
TL;DR: again, remember the recommendation was to use appropriately provisioned SQL auth accounts, rather than service auth, so you have more control and encapsulation.
I have some very legacy old code, older hardware and a mandate to keep it all “just working”. So when Microsoft says they will no longer support something that dates back to the 90s, starting mid next year it makes for fun challenges.
Thanks for the clarifications, guys. I guess time will tell whether these are edge case or showstopper issues. There are certainly lots of variables, so I’m suspecting the former is more likely…which could still suck for those in such edge cases.
But yes, back to trusted auth, it has its place, while sql auth (or even windows auth) may make sense in other cases. Trusted auth does work in cf. Fwiw, in 2021 with the Adobe sql server driver (from progress/data direct), one needs to add this to the connection string: