SQL Server: unable to log in as a newly created user
A few days ago, we created a new server instance with MS SQL Server 2019 installed. The main authorization method during the days of the server configuration and preparation was Active Directory driven Windows Authentication. Simply, all users belonging to the particular group in our Active Directory were able to access the server.
Once the server was prepared, there was a time to connect it to one of our web servers’ infrastructure. We created a new login, map it to a particular database, and tried to connect. The password and connection parameters were configured just a few minutes ago, but we received such an error:
Of course, such an error also occurs when the username or password is wrong, or the user is disabled. This was not the case this time. Let’s take a look at the server logs (Management Studio -> Maintenance -> SQL Server Logs -> Current):
It looks like we have our issue at the glance. The server is configured for integrated authentication only. How to change this setting? Being in the SQL Server Management Studio, connector to the server, right-click on the server name and select Properties:
In the Properties popup, switch to the Security page and select “SQL Server and Windows Authentication mode”:
The setting requires the server to be restarted in order to work. Go to Services and restart SQL Server service in order to apply the setting.