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?
What you do will depend on your SQL Server version, as well as whether you can afford to take the SQL Server service down in order to establish new credentials. The first two methods here do not require restarting the instance:
# For SQL Server 2005, 2008, and 2008 R2 instances
You can connect using the `NT AUTHORITY\SYSTEM` account (or other backdoor methods). There are some details in some of the answers here:
I also have a tip over on [MSSQLTips.com](http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/) that addresses this problem:
- [Recover access to a SQL Server instance](http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/)
Essentially, you download [PSExec](http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx) from Microsoft, then use it to launch Management Studio once you have it installed:
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)
# For SQL Server 2012 and newer instances
Starting with SQL Server 2012, `NT Authority\SYSTEM` was no longer given rights to SQL Server by default. So another way to do that in these newer versions has been [detailed by Argenis Fernandez](https://www.0xsql.com/2012/01/12/leveraging-service-sids-to-logon-to-sql-server-2012-2014-and-new-2016-instances-with-sysadmin-privileges/):
1. If the SQL VSS Writer service is running, stop it, and suspend all maintenance plans or 3rd party backup software that might rely on it.
2. Open `regedit.exe` and change the value of `HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SQLWriter\ImagePath` to point to `SQLCMD.exe`, which is going to be in `C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\**<...110|120|130|140...>**\Tools\Binn`. After editing, the registry value should look something like the following (sorry for the scrolling):
"C:Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.exe" -S .\instancename -E -Q "ALTER ROLE sysadmin ADD MEMBER [YourDomain\YourUserName];"
3. Try to start the SQL VSS Writer service again (you'll get an error; that's okay).
4. You should now be able to connect as `sysadmin` using `YourDomain\YourUserName`. So stop the SQL VSS Writer service, fix the registry, and restart the service (if you need it to be running, or if it was running before you started this).
I've gone through this in a lot more detail in a second tip:
- [More on Recovering Access to a SQL Server Instance](https://www.mssqltips.com/sqlservertip/4672/more-on-recovering-access-to-a-sql-server-instance/#comments)
Though when I wrote that tip I used a more cumbersome approach of making a copy of `SQLCMD.exe` and replacing `sqlwriter.exe` - much easier to just point the service at `SQLCMD.exe` directly.
# If you can afford to take the SQL Server service down
There is an officially supported path from Microsoft that requires restarting the instance in single user mode:
- [Connect to SQL Server When System Administrators Are Locked Out](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?redirectedfrom=MSDN&view=sql-server-ver15)
There is also a function in [dbatools.io](https://dbatools.io/), a Powershell solution for managing SQL Server, called `Reset-DbaAdmin`:
# Security is not the main issue here
I see plenty of people calling for Microsoft to "fix" these so-called "vulnerabilities." These are valid approaches to recovering access to an instance of SQL Server that you rightfully own. They all require elevated privileges on the physical host where SQL Server resides; as I've said to several people, if you don't want developers messing with SQL Server installations, don't make them administrators.
You can follow the steps mentioned in the link below to reset the SA password:
* [Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005]
Steps summarised below:
> 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
* [Connect to SQL Server When System Administrators Are Locked Out]
* [Leveraging Service SIDs to Logon to SQL Server 2012 and SQL Server 2014 Instances with Sysadmin Privileges]
* [Recover access to a SQL Server instance using PsExec]
Ultimately, you could always copy the database files to another instance, or even reinstall SQL Server (adding a local account as sysadmin during that process).