Just installed latest Windows lucee with MSSQL server Express and am getting the dreaded “Login failed for user” when trying to create a datasource for my database (in MSSQL Express). Express is working fine in so far as creating db, table and queries etc. using SMSS.
When I create a datasource (I tried both contexts, web and server on lucee btw) no matter what username and password I use it says “Login failed for user”. I have an “sa” user and I tried that first, and no luck. I have the database, the server (localhost) and the port (1433, which I changed in the protocol section in SQL Configuration for TCP/IP as it was set to another larger number previously, otherwise SMSS would not work).
Any advice appreciated I’ve followed loads of threads, and no joy. CF works first time, Lucee won’t play ball
when you install MS SQL server you are given a choice to install it on how you are to authenticate to the server.
Re-install it with mixed “authentication”
After install is complete open the MSSQL Studio
Create a database
Once created, go to logins in the left hand panel
create a user, call it lucy or whatever you like
give it a simple sql login and assign it to the created database as DBO
After your user is created
Click on the database, click on security,
Verify the user you created is part of the “DBO” role for the database
Now go reconfigure the TCP IP connector service, bind it to 127.0.0.1 or ::
Restart the service
Hi, thanks so much for this - I managed to get it working. I went in to the schemas tab in MSSQL for the user I created and assigned it to DBO. I then restarted MSSQL and it worked. Thanks Terry
Glad you’ve resolved things, but it seems odd that cf would not have required that change also. (You’d said, “CF works first time, Lucee won’t play ball”. Did you mean cf was talking to the same server and db, and using same user?)
Not being antagonistic. Am really simply curious, for the sake of helping future users. If you prefer to let it go, that’s fine.
CF has never required me to make changes to schemas in the SMSS user logins section. In CF, I’ve always just added a datasource for MSSQL and it found the server/table/user first time. Over many decades…
Ok, but I wasn’t questioning any general experience you have but rather was asking if in this specific case you’d tried connecting to the same db on the same db server, the same way from cf. If so, you can just confirm and we can leave it at that (regarding my question).
Yes, it was CF and Express on the same machine (I mentioned localhost for the db server previously). I just assumed that creating a MSSQL DSN to a database on the same server as Lucee would work first time (as it has for me on CF many times). My MSSQL was already in “mixed mode”, indeed the Express installer did this by default for me. Anyway, all’s well that ends well.
Assuming that you aren’t using a Windows Domain account for accessing the database from Lucee, as well as requiring mixed authentication, SQL Server Express by default does not enable TCP/IP.
While you can manage databases with SSMS, you may need to Run SQL Server Configuration Manager and enable TCP/IP under SQL Server Network Configuration > Protocols
If running SQL Server as an instance, the port may not be 1433. The port can also be found using SQL Server Configuration Manager.
Yes, I went through that before many times. The process you mention just gets me to be able to use SMSS with MSSQL and I had that working before my datasource issue. It was the schema setting change (to ‘DBO’ for the SQL user) that allowed me to create the data source in Lucee (my first time using Lucee btw). I’ve never had to do that on CF when the database was MSSQL - this is the point I was making and it’s just my personal experience.
Again, it’s not clear from this that you were necessarily running BOTH cf and lucee from the same machine. It sounds instead like you could be relating your current work on a new machine (having Lucee and mssql) to past experiences on another machine (which had CF and msssql). And fair enough.
If so, I’m simply wondering if cf on that same machine WOULD have also tripped over this. Maybe you didn’t want to try that, and I get it.
I’ve just been pressing that unless you do that, you/we cannot know if the problem may have been something ELSE about your mssql setup there which required that extra step in Lucee…which might also have been required in CF.
I’m not trying being contentious as sport (and you’re shown as being new here, so I especially don’t want you to misconstrue my questions). I’m simply trying to clarify things not only for you but for the sake of others who might find this thread in the future. You’ve concluded something extra needed to be done done for Lucee, which I’ve not heard or experienced before.
We can leave it at “we’ll see” if perhaps anyone else confirms this in the future. I realize it has “ended well” enough for you.
(Finally, to be clear, I’d made no mention of mixed mode as a possible culprit. That was others.)
It would not have mattered to me if CF and Lucee were both being used on the same machine - the same issue would still have arisen in that I could not add a MSSQL datasource in Lucee unless I changed the schema settings for the MSSQL user. I am relating this to other experiences with CF since I have never used Lucee before and assumed (wrongly) that adding a DSN would simply work with MSSQL without the schema settings being set to DBO. Maybe they do in some cases? I do not know. What I do know is that my Lucee data sources now work with Peter’s solution. That was the conclusion for me on this occasion.
I’ve followed loads of threads, and no joy. CF works first time, Lucee won’t play ball 
