In the Software Development game there are always plenty of annoying problems that will just eat up your time, what I really hate is being caught out by the same annoying problem more than once… I have been caught out by this one before so this time I am going to write it down!
So the situation is that you just can’t login to Microsoft SQL Express 2008 via the Management Studio or by any other means using the ‘SQL Authentication’ method. You can login using ‘Windows Authentication’ fine, and can then add SQL users and change their passwords and configure their permissions etc. etc. but you can never login with these users…
If you are experiencing the above the most likely cause that ‘SQL Authentication’ isn’t enabled at all! And Management Studio doesn’t bother to remind you of this when it lets you add, configure and enable such logins.
Well, to check if ‘SQL Authentication’ is enabled, and to enable it if it’s not try the following:
Login into Management Studio, right click on your server and choose ‘properties’. Click on the ‘Security’ tab, make sure that the ‘SQL Server and Windows Authentication Mode’ radio button is checked and hit OK.
Hopefully all of your SQL logins will work now, ours did!
If after you have done this, you still can’t login as ‘sa’, edit the users’s properties and under ‘Status’ tab, make sure that the ‘Login:’ radio button is set to ‘Enabled’ – It may have been disable during installation.