7

I have this problem.

I need to do some administrative tasks on a MS SQL Database using SA account.

  • OS authentication is not set.
  • SQL Server Manager Manager Studio is not installed.
  • I have no other admin database account.
  • I do have access to a Windows admin account
  • The only tools installed on the database server are as shown:

enter image description here

How can I activate OS authentication so I can log into the database an reset the SA password ?

Improve this question
0

1 Answer 1

Reset to default
22

Well, you can start by installing Management Studio. (How else do you plan to perform maintenance tasks once you have access to the instance?) I recommend the most recent version of Management Studio which is fully functional, manages 2005 instances just fine, and is completely free of any licensing requirements. You can download from here:

(If your operating system is 32-bit, you should fix that first; if you can't, you can download the 32-bit version of SSMS 2014 SP1 toward the bottom of this page.)

Once you have Management Studio installed, what you do next 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 that addresses this problem:

Essentially, you download PSExec 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:

  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:

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:

There is also a function in 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.

Improve this answer
9
  • Does SQL Server have a command line admin utility ?
    – Tulains Córdova
    Commented Sep 11, 2013 at 18:04
  • 1
    Yes, it's called sqlcmd but it gets installed with Management Studio. Why would you prefer a command-line utility for things that are much easier done through the UI?
    – Aaron Bertrand
    Commented Sep 11, 2013 at 18:08
  • I get the point, I'll ask the server admin to install MSSMS but first another question: How come this server has a running SQL Server instance with no SSMS installed ? ( I wasn't at this job when this was set up and there's no ducumentation ).
    – Tulains Córdova
    Commented Sep 11, 2013 at 18:14
  • 4
    @user1598390 because when you install a database engine, the management tools are an optional choice. Typically they are not installed because a server shouldn't have client tools on it and should be managed remotely; that said, my personal preference is to install them there because it's better to have them and not need them than to need them and not have them. Why it was done that way in your shop, we have no idea, you'll have to ask them, sorry.
    – Aaron Bertrand
    Commented Sep 11, 2013 at 18:18
  • 1
    @alex Well, you need to download PsExec first, and make sure you run the command line from the same folder where you extracted the executable.
    – Aaron Bertrand
    Commented Aug 23, 2016 at 2:55

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