SQL 2008 SA Password Gone with the Wind

Question

Situation: There is a SQL 2008 instance here that we would like access to. The person who setup the instance is no longer with the company and, apparently, did not set the instance up with the proper users as admins. However, the proper users are admins on the machine that is running the SQL instance.

Some informative links I've been able to dig up on the subject are included here for reference. All have been tried and the results are mentioned below. ('h' omitted per serverfault's rule against new users posting more than one hyperlink) [fixed by edit]

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/81970e88-104d-4e89-ade8-746def18108e/

http://msdn.microsoft.com/en-us/library/dd207004.aspx

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

When the disaster flag or single user flag is used to attempt to gain access the following error is still generated:

LOGIN FAILED FOR USER XXXX Error 18456

Any idea what the problem is with the solutions we're trying? If it matters the machine is on a totally different domain (across the world even) and attempts to login as the the service that we set to run the SQL instance (we have that password, btw) fail as well.

Thanks in advance for your time.

Answer 1

Try enabling mixed mode by changing the authentication mode from Windows Registry by modifying the LoginMode subkey and then restart sql server service in single user mode using -m parameter. Then go to this folder using command prompt C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn and then try typing this SQLCMD -E and then execute these Tsql code to add windows administrators as login

use master;
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go

now restart the service without -m option and change the SA password

good Luck

EDIT:

You might want to take a look at this article. it has screen shot and everything