0

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.

Improve this question
3
  • 1
    Did you create a seperate SQL Server Authentication-based user? They're completely separate from Windows Authentication users.
    – Philᵀᴹ
    Dec 28, 2015 at 8:27
  • As Phil said in order to use SQL authentication you need to create login inside sql server. If you did so, check if SQL Server is configured in mixed authentication mode.
    – Michael Cherevko
    Dec 28, 2015 at 8:31
  • 1
    You can't use the windows login (I.e. MYDOMAIN\username or local windows username) as sql login - they are separate things. You need SQL login "mylogin" assuming you have mixed authentication.
    – seventyeightist
    Dec 28, 2015 at 20:10

1 Answer 1

Reset to default
2

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:

SQL Server Error 18456 States

As you can see in this example I failed to login because my UserID is incorrect:

enter image description here

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 mode

More detail

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.