Hi all, I am surprised that I have never had/neede...
# sql
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
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:
Copy code
Login failed for user 'cflogin'. ClientConnectionId:b58ccf3c-7ceb-4622-bec8-00aeb2958ae6
The following is what I used to set up the login/user/permissions
Copy code
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
Copy code
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
I will enable them all
I enabled all of the IPs in the IP Address tab and made sure they were all active, then restarted SQL Server Express. Unfortunately, I am still receiving the same error. I also tried in the Host just to test, but the same error occurred.
When giving
permissions, this is essentially the
combined permissions, is that correct?
I don't know about CREATE - normally you'd want SELECT in there somewhere
Yeah, it has SELECT in the list for CONTROL
I don't know what else could be hanging this up
Going to start from scratch, again.
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
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.
👍🏼 1
message has been deleted
👍🏼 1
👀 1
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. 😵
👍🏼 1
Did you ever get this resolved? Make sure TCP Port 1433 and UDP 1434 are open on your firewall. Also, make sure to restart SQL Server after you make changes to TCP connection properties on the server.