Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows authentication.

My plan was to simply log into the server, connect via Windows Authentication, and reset sa to use a new password. Since I cant' connect via Windows Authentication, this won't work.

How else can I reset the sa password?

share|improve this question
    
Found the answer. I could not log in as a a domain admin, but using local admin worked fine. –  Daniel Williams Aug 16 '11 at 18:24
    
Do you mean that a member of the local machine 'Administrators' group was able to do this? For posterity, what exact steps did you take? –  p.campbell Aug 16 '11 at 18:26
    
The account was the local Administrator account itself. This account is part of the local Adminsitrators group, as is my domain account. But only the local Administrator account was able to use Windows authentication to log into Sql Server. I hope that helps. –  Daniel Williams Aug 16 '11 at 18:43
    
starting the instance in single-user mode will also allow any local account that is part of the local Administrator group access to the instance. Edit: The link @user1419 points this out. –  Shawn Melton Aug 16 '11 at 19:39

2 Answers 2

up vote 7 down vote accepted

You can the steps mentioned in the link below to reset the SA password:

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

  1. Open SQL Server Configuration Manager from Start Menu > Programs > Microsoft SQL Server 20xx > Configuration Tools > relevant to the newest version of SQL Server you have installed (e.g. if you have 2005 and 2012 installed, use the 2012 version). Don't have a Start Menu? On Windows 8's Start screen, start typing SQL Server Con... until it shows up.
  2. Stop the SQL Server instance you need to recover by right-clicking the instance in SQL Server Services and selecting "Stop"
  3. Right-click the instance you just stopped, click Properties, and in the “Advanced” tab, in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option (on newer versions, you can go directly to the "Startup Parameters" tab, type "-m" and click Add, without worrying about the syntax, the semi-colon, or anything else).
  4. Click the “OK” button, and restart the SQL Server Instance
  5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "CONTOSO" domain to the sysadmin role:

EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server instance one more time.

NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

share|improve this answer
    
thank you- single-user mode looks quite useful –  Daniel Williams Aug 16 '11 at 20:14
3  
Is it possible to summarize the steps on that page here? Link-only answers are generally better put as comments. –  Nick Chammas Jan 9 '12 at 20:53
1  
@Nick it took me a long time but I finally got to this. :-) –  Aaron Bertrand Sep 12 '13 at 13:25
    
Do the aforementioned steps still apply in a mirrored situation? I tried these steps and the "Startup Parameters" section is grayed out and non-editable. I followed the steps as described :( –  Sean Perkins Jul 24 at 6:45

You can connect to the instance using the NT AUTHORITY\SYSTEM account (or other backdoor methods, such as here or here). I have a tip over on mssqltips.com that addresses one of these approaches. Essentially, you download the SysInternals tool PSExec from Microsoft, then use it to launch Management Studio:

PsExec -s -i "C:\...\Ssms.exe"

This will connect as NT AUTHORITY\SYSTEM and will allow you to do things in Object Explorer, like:

  • Change the instance to SQL Server and Windows Authentication mode - right-click the Server name, hit properties, and change the radio button if it's currently set to Windows only:

    enter image description here

  • Set the password for the sa account - expand Security, expand Logins, right-click sa and hit Properties, and in the resulting dialog there will be two password entry fields:

    enter image description here

  • Add your own login as a sysadmin - right-click Logins, New Login... enter your login name (in the form DOMAIN\username) then move to the Server Roles tab and check the sysadmin box and click OK:

    enter image description here

  • (or, if your login is already listed, right-click, Properties, and make sure sysadmin is checked under Server Roles)

share|improve this answer
    
will this method allow me to enable the SA account as well? I'm stuck in a position where my SA account is disabled and I have an AD group with the Public role. Also, your method listed, will I be able to perform these steps w/o taking down the instance? I ask because my server is in a production environment now. –  Sean Perkins Jul 16 at 15:14
1  
@Sean yes, you should be able to re-enable sa, and/or create another SQL auth account, or add your AD user (not group!) specifically to a more elevated role. None of these will require restarting SQL Server (the only case where that would be required is changing between mixed authentication and Windows only). –  Aaron Bertrand Jul 16 at 15:42
    
I get the following error when I attempt the process your delineated. I made sure to take a screenshot of the only accounts listed as well. Was there a change at some point in SQL 2012 that will not allow this method? My current version is 11.0.3128. Further, it doesn't matter what type of change I make, I'll get some variation of an error stating that I don't have permissions. One final thing, I doubt this makes a difference, but I opened PowerShell with Admin rights, executed cmd.exe, then psexec -i -s "C:\......smss.exe" ![Account Creation Error](i.stack.imgur.com/ITOja.p –  Sean Perkins Jul 16 at 20:20
    
The attached pic isn't showing anything now, but it had "SA" and "NT AUTHORITY\SYSTEM" as the only listed accounts. The error message I received was "User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)" –  Sean Perkins Jul 17 at 12:31
    
@Sean PowerShell? PSExec has nothing to do with PowerShell. Are you a local administrator on the machine? Are you sure? –  Aaron Bertrand Jul 17 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.