login failed for user sa at SQL Server 2008 R2

Question

After upgrading to Service Pack 1, sometimes i got failed to login my server

at this time, Server Configuration Manager is like below

Do I need to change anything? thank you.

--

Once I could login, Server Configuration Manager is like below

During the failure time, I try to start "SQL Server (MSSQLSERVER)" but got fail to start. What is wrong in my R2 express? Please share me your idea.

---

After running "SQL Server (MSSQLSERVER)", I could login in to my server as 'sa'.

Answer 1

Although my below snaps mention SQL Server 2008/express I would suggest to check for the below points.

• Check whether you need to put the local computer name prefix before your DB name

  

• If you connect using Windows authentication with the account you've created when you create your DB instance you can troubleshoot with right click database --> Database properties and select the below security option.

  

• Check sa login under security option by double clicking. You can change the password if required as below.

• Also if you see USER MAPPING option on the same properties view, check it and makesure sa is checked into the database you want to login.

  

  • Restart the SQL Server service (Shortcut: Winkey + R then services.msc)

  

  • Try to login again and good luck!