Hi all, I am surprised that I have never had/needed a local sql server database, but now I have one and I am struggling to get the login/user/permissions correct in order to allow access via CFML Admin datasource using

localhost:1433

I have read tutorials online but I still cannot get it to work. I can connect to the localhost:1433 using Windows Authentication in both SSMS and DBeaver, but when attempting to set up a sql server login and user with the proper permissions, it will not work. The error from Lucee Admin Server:

Login failed for user 'cflogin'. ClientConnectionId:b58ccf3c-7ceb-4622-bec8-00aeb2958ae6

The following is what I used to set up the login/user/permissions

CREATE LOGIN cflogin WITH PASSWORD = 'test'
CREATE user [cfuser] for login [cflogin] with default_schema=[dbo]
GRANT CONTROL ON testdb TO cflogin

I have also tested by adding the user to an existing role

ALTER ROLE [db_datawriter] ADD MEMBER [cfuser]

Then, I checked the SQL Server Express settings and I believe everything is set up correctly as well. SQL Sever Express has been restarted. Would anyone have any ideas?

Check the IP Address tab and make sure all the IPs are active and enabled.

I only enabled 127.0.0.1

I don't know about CREATE - normally you'd want SELECT in there somewhere

Yeah, it has SELECT in the list for CONTROL

Can you login via that username and password via tools such as enterprise manager or Azure Data Studio. What does it say... You might make sure that Sql Server authentication is enabled... From: https://www.dundas.com/support/learning/documentation/installation/how-to-enable-sql-server-authentication

@Michael Schmidt Thanks, I wish I had come back to check on this thread earlier. Your thought was exactly the issue. I needed to set authentication mode to be mixed in SSMS. After I had set this, everything worked perfectly. Thank you, again. 🙂 https://www.top-password.com/knowledge/sql-server-authentication-mode.html

Since it's a local server, CONTROL may not be that bad. But I wouldn't give that in a Production system. CONTROL pretty much gives owner-lite permissions to that login for that database, which would also allow

cflogin

user to grant permissions to other users. If all you need are SELECT, INSERT UPDATE and DELETE, I'd grant those explicitly, and nothing else.

Hi @shawnoden, agreed, I definitely would not be using CONTROL on production. This is just for my local environment. Thank you for the great illustration and references! I will be combing through them! 🙂

Database permissions can get very complicated very fast. 😵