Cannot log into local instance of SQL Server 2008 R2

Question

Hi,

I am working on a dev machine at work which had SQL Server 2008 R2 installed on it but it was a bit screwy so I completely uninstalled it and reinstalled if again.

Everything seems ok until I create a login, apply the necessay permissions etc. Logout of SQL Server and then try and reconnect using the account I just created. The login fails/ I tried this many time and got no luck. I remoted to my machine at home and did the same there as I was convinced I was doing something wrong but it worked first time.

I even reset the password on the sa account and that couldn't log in either.

How can I investigate what the problem is?

Thanks

Answer 1

Can you connect to SQL Server with Windows Authentication?

Have you enabled SQL Authentication Mode on your server? Maybe you need to grant permission on TCP endpoint to your SQL User.

Answer 2

What error do you get? Any messages in the SQL Server error log? 

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

That sounds familiar. I can connect with Windows Authentication and I hadn't enabled SQL Authentication Mode. Anyway just enabled it and restarted SQL Server and Bob's your Uncle (probably that is not true).

Sorry for being thick and thankyou for your help!!!!!!

Answer 4

Where are the error logs stored usually?

Answer 5

The logs are stored someplace similar to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

If SQL Server was installed in Windows Authentication mode, then after changing to mixed mode, you will also need to enable the sa account if you want to use it.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty