Getting login failed for sa when I haven't changed the password

Question

I've been developing a winforms app tied to sql server. I haven't rebooted in a while. Today i rebooted and now I can't log into sql. I used every account I know and their passwords including one that was working just before i rebooted and i get a 'Login failed' . I did take the database I use offline just before starting and I do have backups before then.

thoughts on what happened? Is there a way to bring the database back online OR somehow find out what passwords are? I even tried using windows authenication with me as an admin on the box AND sa (Yes, bad) and still no dice.

Answer 1

:-/ That's a rough place to be ... I wish you luck. Check out this blog post, not sure if you're using sql 2k5 or not, but if so, it may be helpful:

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Answer 2

Have you checked to make sure that the service is actually running? Also are you trying to connect using IPC, TCPIP or named pipes? Whichever way make sure it's enabled in the configuration tools.

Since admin's on the box are SQL admins the only thing I can think of is that the service is not running.

Answer 3

Thank you for that article!!!! Yes, I verified server was running. Also, it was actively refusing my connection so that hinted to me it was running.

Funny thing. SA was configed with my app database as it's default db. Since it was offline, no go. HOWEVER, sqlcmd did allow me to log in with it, eventhough EM did not. Ran sqlcmd per Raul's article and it works great!!!!

Thanks!! you saved me from an evening of SQL reinstalling!!!

Answer 4

Try using command prompt to change sql sa password, instead of reinstalling the MSSQL.

Osql –S yourservername –E

1> EXEC sp_password NULL, 'yourpassword', 'sa'

2> GO