SQL Server Express: Login failed for user X: Error 18456
After creating a new SQL server login with a password in SQL Server Management Studio and then adding a user with the same name as the login to a database, I was having a lot of trouble getting my code first entity framework model to connect to the database. Every time I tried to specify the connection for a context then get a list of objects from the context, I got a SQL exception indicating that the login/ password did not work and could not be logged into the database.
After some research, I found that by default SQL Express only works with Windows Authentication for logins and you have to configure the server to work with both Windows Authentication and SQL Server logins with passwords. To access the configuration setting, right-click the topmost tree node (the server name) in SQL Server Management Studio and show the Server Properties.
In the Server Properties dialog, click the Security settings node in the Select a page panel, then under the Server authentication settings, check the SQL Server and Windows Authentication mode. Then press OK to close the dialog.
To get SQL Server Express to pick up the new setting you need to stop and start the server. To do this, again right-click the server name in SQL Server Management Studio and choose Restart from the popup menu. OK and dialogs that come up asking for permission to restart the server. You should then be able to create a connection string to use with an entity framework context using the server name, login/ user id and password you have set. The connection string will be of the form:
Data Source=<server name>;Database=<Database name>;User Id=<Login>;Password=<Password>