I recently installed SQL Server 2014 in my machine. I configured my user and provided high level access for the same. While connecting through SSMS 2014; if I choose 'Windows Authentication', I can able to connect to database engine successfully. However if I choose to connect through 'SQL Server Authentication' and provide my username/password; I'm getting Error: 18456.
The purpose of me trying this option is – I wanted to try out subscriptions for my SSRS report. Since I opted for 'Windows Authentication' in my report settings I couldn't able to do subscriptions for my reports.
Any suggestions will be very useful. Thanks.
Best Answer
A way of knowing why is this error happening would be to check the SQL Server log and look for details into why is this happening:
Try to login again with SQL Server Authentication, after getting your Error 18456 change to Windows Authentication and in your Object Explorer go to: Management -> SQL Server Logs -> Current - XX/XX/XXXX XX:XX:XX Now search for your newly logged error and identify the error State
With this State number assest the nature of the error using the following table:
As you can see in this example I failed to login because my UserID is incorrect:
Also make sure you have hibrid authentication. Check the server properties from the Object Explorer and go to Security section and make sure Server Authentication is set to
SQL Server and Windows Authentication modeMore detail