Okay, this is making me nuts. Please please help me. Because I really can’t take anymore of this not working.
There must be 100 different solutions to try to fix datasource connection problems? So far, none have worked.
Working on clean machine with fresh installs of:
Lucee.
MSSql server.
SSMS.
Checked firewall to make sure they can pass through.
Tried the jTDS and MSSQL drivers in Lucee when trying to add the datasource. Tried switching to “Mixed Authentication” in SqlServer, and all combinations.
Have also downloaded the latest ntlmauth.dll and because something to do with jtds. My brain is fried at this point.
Also, Lucee can run cfm pages with no problem, as long as they don’t need a database connection.
I am so tired of spending time on this stuff instead of being able to write code.
Is there a troubleshooting guide? I’d pay for it if there were.
Thanks in advance.
Specs =
Windows 10 Home:
(build 19041.867)
SQL Server:
14.0.1000.169
SSMS: 2020 v18.8
Lucee v: 5.3.7.48
Tomcat v: whatever was built in the above version of Lucee.
Java 8, update 251- (build 1.8.0_251-b08)
@lamaree I checked got the same issue when I did not give the username while creating datasource. Can you check with username?
Are you running SQL Express? It doesn’t use port 1433. Each instance will have a special port number.
I tried using windows username and password. With Windows authentication and with mixed authentication.
Neither worked.
I’m not using express.
Using full version.
I set all ports to 1433.
Still doesn’t work.
Could you please clarify which username and where you mean?
I tried entering my windows username when creating the datasource in Lucee (and without), which is the same as the owner of the db I assume, since I created it and SQL username when I login to ssms.
But, that didn’t work either.
I wound up setting ALL ports on sql server to 1433 and all ip addresses to 127.0.0.1.
Should I not have done that?
Also, when choosing the MSsql driver in Lucee I get this error:
“Login failed for user ‘’. ClientConnectionId:704a36fe-ff8f-4118-96c3-8f76dbef2e65”
When using the jtds driver, I get this error:
“I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.”
In lucee datasource creation → username field, set the username which you defined while installation of DB.
First off, make sure you have allowed IP4 connections in the named pipes for the MSSQL Server
open SQL Server Configuration Manager;
switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
double-click the TCP/IP protocol;
select the Yes value in the Enabled field;
switch to the IP Addresses tab;
find the IPAll section;
clear the TCP Dynamic Ports field in that section;
specify the 1433 value in the TCP Port field:
Next make sure you have allowed the exception in the windows firewall or turned windows firewall off
Now in SQL Manager go to users
Add a user call it LUCEE
Add a password, make the password : password123 (or whatever you want)
Go to permissions
Give the user LUCEE, DB Owner for the Database you want to connect to
now Go to Lucee Admin
click Data Source
In the Name, Give it a name you want to reference it as, like MyDataBase
In the Type: choose "Microsoft SQL Server (Vendor Microsoft)
in the Database field, put the name of the database you gave LUCEE access to in the SQL Manager
In the Username field, put the name LUCEE (the user you created in the SQL Manager)
In the Password Field, put the password for the user LUCEE (password123)
Click Create
1 Like
First, thank you very much for taking the time to write all of that out.
So, I tried the steps.
But, SQL server won’t allow me to create a “SQL user with login” .
I have attached an image of that.
It did allow me to create one without SQL login, and I did make it owner of the db. But I can’t connect to the db using that name/acct either. Just same errors.
Sigh…
Sounds like you don’t have mixed mode enabled and can only do Windows Auth.
So, I’m trying to alter the user “Lucee” to add a password.
Ran this:
ALTER USER Lucee WITH PASSWORD = ‘’;
Got this error: Does this hopefully shed more light on what the issue might be?
“Msg 33234, Level 16, State 1, Line 1
The parameter PASSWORD cannot be provided for users that cannot authenticate in a database.”
I tried to alter the user “Lucee” to add a password.
Ran this:
ALTER USER Lucee WITH PASSWORD = ‘’;
Got this error:
“Msg 33234, Level 16, State 1, Line 1
The parameter PASSWORD cannot be provided for users that cannot authenticate in a database.”
Should I reinstall SQL Server? Can I change it so that mixed mode is enabled?
To enable mixed mode. Run Management Studio. Right click on the server connection and select properties. Select the Security page and select Sql and Windows Auth. Then create a login for your database(s) and assign rights. db_owner is always quick but not recommended for anything production.
I mainly use sql auth since my program started on SQL 2000 and it is the easiest to setup. I have done NTLM auth with the Microsoft driver.
Haven’t tried with jTDS but I saw the ntlmauth.dll is needed and supposedly goes somewhere in the java directories.
Mix mode is just a few clicks away.
- In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
- On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
- In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
- In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
It’s already in mixed mode though : (
Make sure you have named pipes set to tcp and the sql named service running
As I am way too tired to set this up again, here is a link that will step you through setting up your sql instance for network connection. SQL Server network configuration
Does this make sense?
When I open and connect the server “Windows” is selected, and you can see the choices I have. That’s the only way it will run.
Yet, the settings say (see previous image) that “SQL Server and Windows Authentication mode” is selected.
I’m so confused.
All pipes have long been tcp/ip and 127.0.0.1.
I guess it’s time for reinstall.
Thanks for your help!
