Error login failed for a user on SQL server pops up, when a user is trying to login to Server using SQL Server Authentication mode. This error basically indicates that SQL Server Authentication Mode is not enabled on the MS SQL Server.
Steps to Resolve MS SQL Server Error Login failed for User:
Step 1: Login to SQL Server Using Windows Authentication mode. Right click on the server node and then select the option Properties as shown below:
Step 2: In Server properties dialog select the option Security from the left side. In Server, authentication selects the radio button SQL Server and Windows Authentication mode as shown below.
Step 3: In step 3 click on Ok button, then a message that some of your configuration changes will not take effect until SQL server is restarted will pops up as shown below:
Step 4: To restart MS SQL services right click on the server node and then select the option Restart as shown below:
Finally, log in using SQL Server Authentication mode and this will resolve MSSQL Server Error 18456.