This just occurred to me lately. I have a fresh install of MS SQL Server 2012 express. Installation went smooth without an error but unfortunately I wasn’t able to create a new user to log in to the database other than using the existing Windows user for authentication. I kept getting error 18456 back from the SQL Server Management Studio and nothing else. The error message isn’t helpful at all. It turns out the reason why I wasn’t able to log in with a newly created SQL Server is that by default the SQL express server only use Windows Authentication mode for it’s server authentication setting. You need to manually find the setting and set to use “SQL Server and Windows Authentication mode”.
Here is the complete workflow on How to Create a new SQL user after fresh SQL Server install
Launch Microsoft SQL Server Management Studio, login with the default Windows authenticate user. Expand Databases > Security > Logins > New Login …
Give a login name, and choose SQL Server authentication and give the user a password. You can uncheck all the enforce password policy and expiration to save you the trouble of resetting passwords.
Under the Status tab, ensure the Settings for “Permission to connect to database engine” is set to Grant, as well as, Login is set to Enabled. Click OK to create this new user.
Now go back to the SQL database and right click > Properties.
Under the Security tab, make sure the “Server authentication” is set to “SQL Server and Windows Authentication mode”
After you have made the change, the SQL Server management studio will tell you to restart the SQL Server. “Some of your configuration changes will not take effect until SQL Server is restarted.”
Go to Start Menu > Services > find SQL Server (SQLEXPRESS) and restart the server.
That’s it, now you should be able to login to the SQL database with a newly created SQL user.