Recover access to a SQL Server instance

By:   |   Comments (96)   |   Related: > Security


Problem

Starting with SQL Server 2008, the local Administrators group is no longer added by default during SQL Server setup; you even have to use a manual step to add the current user as a local administrator. This means that it is possible, especially if you don't use mixed authentication (or have forgotten the sa password), that you can be completely locked out of your own SQL Server instance. I've seen cases where an employee has moved on, but their Windows account, being the only one with Administrator privileges for SQL Server, had been completely obliterated from the system. Of course that person was the only one who knew the sa password as well, and being a local admin or even a domain admin might not help you.

The typical workaround I have seen employed is to restart SQL Server in single user mode. However, this approach requires at least some downtime; in some systems, this would be unacceptable. And depending on what needs to be managed on the server, it might not be feasible to wait for a scheduled maintenance window.

A more extreme workaround is to shut down SQL Server, copy all of the MDF/LDF files, install a new instance of SQL Server, and attach all of the user databases. In addition to downtime, the problem here is that you must also re-create all of the logins, linked servers, jobs, and other elements outside of the database in order to have a fully functioning system again. Plus, unless you uninstall the old instance first, the new instance will have a new instance name, and therefore all client applications will need to be updated.

Solution

Thanks to Mark Russinovich of Sysinternals fame, there is a very painless way to solve this problem without any downtime: PsExec. While it wasn't one of its primary design goals, PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account, which - unlike "regular" Administrator accounts - has inherent access to SQL Server.

The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, "Run as Administrator"). Run the following command, adjusting for your actual path to Management Studio, which may be different:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).

You will get an error message if you are not an Administrator. You will need to agree to the license terms in order to proceed. When Management Studio launches, it will prompt you to connect to a server. You will notice that the authentication mode is Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM:

Connect To Server dialog using NT AUTHORITY\SYSTEM

Once you connect, in Object Explorer, you will see that you are connected to the server as NT AUTHORITY\SYSTEM:

Object Explorer NT AUTHORITY\SYSTEM

Now, you can go in to Security > Logins and add your account as a sysadmin, add other admin accounts, update the sa password, and do anything else you need to do to make your instance manageable. As you can see, I was able to connect in this way to both SQL Server 2008 and SQL Server 2012 instances from an instance of Management Studio 2012. I also confirmed that this process works when connecting to a SQL Server 2008 instance using the 2008 version of SSMS. In both cases, I was logged in to Windows as a local administrator, but the account had no explicit access to either SQL Server instance.

You can connect to additional instances on the local server using the Connect > Database Engine dropdown in Object Explorer, or by right-clicking a query window and choosing Connection > Change Connection.

Caveats

  • You must be a local Administrator to masquerade as NT AUTHORITY\SYSTEM.
  • You may need to disable UAC.
  • You may also be able to do this remotely, but since I work primarily in virtual machines, I did not test this.
  • If you change the sa password, and you've used the sa account for external programs (which you shouldn't do), you will need to update those programs. Here is a tip for updating the password used in maintenance plans.
  • Leave the command prompt running in the background until you're done with all of your changes - if you inadvertently Ctrl+C from within the command prompt, SSMS will vanish.

Conclusion

It is quite common to get locked out of a SQL Server instance, and having been there, I know it is quite frustrating. PsExec can get you into your systems without a lot of the headache that can be caused by more brute force methods. You should download PsExec and keep it handy; it can prevent you from going to extreme measures, and having it already available can help in cases where the SQL Server machine you're trying to recover does not have ready access to the web.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, February 24, 2021 - 10:31:43 PM - Chris OConnor Back To Top (88299)
WONDERFUL !! Thanks so much - this is exactly what we needed. And older SQL2008 server - was a slightly different path to SSMS - but we were able to add logins and a new "sa2" account

Monday, September 28, 2020 - 12:02:28 PM - mxmostafa Back To Top (86540)
if NT AUTHORITY\SYSTEM: is disabled by sa , What should we do?
I Need To this to login to server but NT AUTHORITY\SYSTEM: is disabled
i start with psexe but can not login
please send me with email Thanks,

Friday, May 15, 2020 - 6:10:01 AM - Sementadmin Back To Top (85669)

Thanks so much!

Thursday, November 21, 2019 - 8:11:21 AM - wojcikpat Back To Top (83157)

Nice solution, very easy.

works for me.

We change from local account to domain account and we forget to add it in SQL server and we lost possibility to log in to DB

Monday, August 12, 2019 - 2:29:32 AM - CBS Back To Top (82031)

Thanks, This worked for me (I'm on SQL 2017)

Tuesday, August 6, 2019 - 4:36:53 PM - Aaron Bertrand Back To Top (81985)

AP55, this article was written in 2012, before the first service pack was released for that version. So, some security fix may have patched it up between the time I wrote this article and tested the methods herein, and today, four service packs later, you're probably on a newer, more secure version of Windows that didn't exist at that time either.

If this method doesn't work for you, did you try any other approaches? Here is an article I wrote 5 years later:

https://www.mssqltips.com/sqlservertip/4672/more-on-recovering-access-to-a-sql-server-instance/

I agree, everything you read online should be taken with YMMV.

Tuesday, August 6, 2019 - 3:27:33 PM - AP55 Back To Top (81984)

For future readers,

No, it does not work out-of-the-box on SQL Server versions > 2012.

For 2012, Aaron contends that it works by default despite what the documentation says (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/bb500459(v=sql.110), and what others in the comments as well as myself have experienced.

In other words, YMMV.

Tuesday, May 28, 2019 - 3:32:39 PM - Teri Back To Top (81238)

I'm having the same problem with  SSMS 14.0.17289.0, SQL Server 17. After logging in with NT AUTHORITY\SYSTEM account, I looked at the properties for that account in SSMS, and the only server role checked is public. Was that changed since SQL Server 2012?

If so, any other ideas??

Wednesday, April 17, 2019 - 9:20:53 AM - Aaron Bertrand Back To Top (79585)

Teofil, sure, sounds like someone disabled or renamed the sa account, which is atypical. So you need to figure out what sysadmin account is being used in its place, or create one.

Wednesday, April 17, 2019 - 5:43:53 AM - Teofil Back To Top (79581)

 It looks very smart, BUT with mine it did not work :(

I have SQL 2016 installed (SQL Server 14.o.1000 is found in brackets) and trying your method I got the err:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

Any idea?

Wednesday, January 30, 2019 - 2:28:09 PM - Ransi Jona Back To Top (78916)

 Great post Aaron. Thank you !

Monday, September 4, 2017 - 2:48:10 PM - aj Back To Top (65847)

Thank you, this worked for me, straight forward!

 

Wednesday, July 12, 2017 - 6:35:07 PM - Dave Back To Top (59300)

 

 Does this work on windows server 2012 as well?    I've made it work on windows server 2008 R2 to do this to access a sql 2012 instance, but we're having issues with it on a Windows server 2012 box.    It lets us connect properly as ntauthority\system, but when we try to add a user, it then tells us that we don't have permission to add anyone.

Thursday, June 29, 2017 - 2:30:05 PM - Naresh Back To Top (58564)

 

 Thank you so much. It  worked like a charm..Thank you!! Thank you!!

Thursday, April 20, 2017 - 1:20:53 PM - Bruno2049 Back To Top (55065)

Big thanks for the post, worked like a charm.

Thursday, April 20, 2017 - 11:30:09 AM - Kiran Back To Top (55064)

I know this is an old post, but just wanted to give you a thumbs-up - saved me a ton of time trying to figure out how to get access to my server. Cheers!

Monday, April 17, 2017 - 1:49:12 PM - JohnP Back To Top (54997)

 Hi, YOU ARE THE MAN! I tried prior to PsExec things like disabling UAC without any success. I had the problem that even with Local Admin Rights, I got the message "you have no access". Then i did the PsExec thingy and it worked perfectly once i logged as NT Authority \ System, I saw all sessions and could alter the sa PW. Perfect Solution!

 

Monday, April 10, 2017 - 3:45:38 AM - Dar Back To Top (54560)

I strolled through the comments and couldn't find an answer to all the questions on how to change the password or enabling the sa login.

this is the error me and many others got:

 

Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

 

Please help !!

 

 

Tuesday, March 28, 2017 - 12:19:40 PM - Toni Back To Top (53819)

 Thank you Aaron.  This is an invaluable tip!  I just had to use this on a 2014 SQL Express install.

 

Friday, December 2, 2016 - 11:03:42 AM - L. Corado Back To Top (44889)

 This document was useful to solve the issue I was having.

 

Friday, June 3, 2016 - 11:02:35 AM - Mike Hinds Back To Top (41607)

This appears not to work for 2016. Specifically, a 2016 Express instance has only BUILTIN\Users and a disabled SA. Running PSEXEC as system, I do not have the authority to add a login to the Express instance.

What might be my Plan B ?

 

Tuesday, May 24, 2016 - 6:29:26 PM - Rod Heitrich Back To Top (41554)

Thanks a lot Aaron - worked like a charm and problem solved.  Much appreciated!!

Thursday, March 17, 2016 - 7:45:08 AM - Jakub Back To Top (40968)

Thanks for great tip how to get over my problem... It saved me a lots of time :-) 

Friday, October 2, 2015 - 2:58:49 PM - Binaya Regmi Back To Top (38814)

Thanks Aaron for this very useful tip.

Monday, September 7, 2015 - 8:59:01 AM - Riekert Back To Top (38606)

Thanks! Problem solved. (SQL 2008 R2)

Wednesday, August 19, 2015 - 3:26:35 PM - Mike Back To Top (38488)

I can't thank you enough. This worked wonders for me. Saved me a complete system rebuild!!

Wednesday, July 1, 2015 - 6:48:40 AM - Vivian Back To Top (38091)

So many thx for this. Worked beautifully

Wednesday, June 24, 2015 - 10:38:16 AM - Aravind Back To Top (38013)

I have done with psexec and the NT AUTHORITY\SYSTEM also doesn't have access. Could you say if any other gaps to connect the server without getting the server down.

Tuesday, June 2, 2015 - 6:06:50 PM - Keith Back To Top (37364)

Great to find this tip - a departing employee gave me access to all the machines in a cluster but never set me up with a database login.  This saved me a headache.  Thanks!

Monday, March 16, 2015 - 1:39:41 PM - Harshal Back To Top (36544)

Thank you Aaron, worked like a charm. BTW good to read the word masquerade.

Wednesday, March 11, 2015 - 10:56:23 AM - Jason Coombes Back To Top (36491)

Hi Aaron,

Thanks for the quick response.

You're right, I could use the domain service account although I just looked and that isn't provisioned as a sysadmin either (I just remembered that we had tried this approach but it had slipped my mind when I commented here).  This is SQL2012 SP2 but I don't believe that applying the SP has changed any of this configuration.

Luckily in my case this is a new instance that had been setup by someone who is now on leave and hadn't provisioned any other accounts into the sysadmin role.  I used the alternative approach to stop and start SQL using my local administrator account which allowed me to connect and create my own sysadmin login.

It would be interesting to know under what conditions the NT AUTHORITY\SYSTEM is and isn't added as a sysadmin. Something for me to investigate.

Wednesday, March 11, 2015 - 9:39:57 AM - Aaron Bertrand Back To Top (36485)

Hi Jason, yes, that's possible, I haven't tested explicitly (though I typically do use my own domain account for the service on VMs, so it should have failed in at least one test).

If the service is running as a domain account then surely you can get that user to help; if that user has moved on (and IT can't fix the issue by resetting the AD password, and another domain admin account proves unsuccessful), you may have to resort to installing a new instance of SQL Server and moving your stuff (and learning to use a non-domain account for the service in the future, or at least a generic and dedicated account which can have its password changed easily).

Wednesday, March 11, 2015 - 7:45:21 AM - Jason Coombes Back To Top (36484)

Aaron,

Thank you for this very useful tip and I'll certainly book mark this for future reference.

I have one question which is to ask what account is running the instance of SQL 2012 for this to work?  I suspect that it's running under the NT AUTHORITY\SYSTEM which is why it is provisioned as a sysadmin by default.  I expect those posting saying it doesn't work have installed SQL 2012 as a specific service account which means NT AUTHORITY\SYSTEM isn't provisioned as a sysadmin.

I'm basing this on a SQL2012 installation that uses a domain account for the SQL Server service account and NT AUTHORITY\SYSTEM isn't provisioned as a sysadmin.

Either way I'd be interested in a response just to inform myself and others for future reference.

Thanks again for a great article.

Jason

Wednesday, March 4, 2015 - 9:48:19 PM - Srini Back To Top (36448)

Hello Aaron Bertrand,

Sorry, I had a bitter part of the solution when I implemented it with PSExec with Administrator rights.

Once I break into MSSQL with "NT AUTHORITY\SYSTEM", I can see the instance as a normal user only and not with admin rights (But before without your idea, I had no clue what's running in side) at instance level.

Once I try to change sa password or provide sa rights to "NT AUTHORITY\SYSTEM" account it failed with "Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ‘sa’, because it does not exist or you do not have permission".

nothing much was possible for me when I did with the actual default Administrator account of windows server 2008R2 running with MSSQL 2012 enterprise edition

 

 

 

 

Thursday, January 15, 2015 - 10:18:26 AM - dale withroder Back To Top (35957)

thanks for this.

just started a new job where this was an issue and it worked perfectly.

Monday, December 22, 2014 - 5:00:57 PM - Aaron Bertrand Back To Top (35734)

Dominique, this is not a permissions problem at all, it's certainly one of these things:

1. You've used an instance name that is incorrect, an instance name when you're trying to connect to the default instance, or you left out the instance name when you need it. Also check you've used .\instance and not ./instance.

2. The service is not running.

There are other potential causes (Google your error message for literally millions of hits), but most of these won't be relevant in most cases when you are connecting to the local machine.

Monday, December 22, 2014 - 3:55:59 PM - Dominique Back To Top (35733)

Hello,

I tried the psexec command and it will open the SSMS screen but when trying to connect to the instance with NT AUTHORITY\SYSTEm Windows Authentication it is giving me an error:

TITLE: Connect to Server

------------------------------

Cannot connect to VOPVMREPORT1\VEEAMSQL2008R2.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

The database is 2008 R2...

Any idea?

Thanks,

Dom

Thursday, December 11, 2014 - 12:49:27 PM - Aaron Bertrand Back To Top (35575)

Ben, what does "seems like" mean? Do you get an error message? If so, what is it? Also did you try any of the methods in the other links I've posted?

Thursday, December 11, 2014 - 10:51:58 AM - Ben Dawson Back To Top (35573)

Looks like the NT AUTHORITY\SYSTEM account has been stripped of sysadmin rights on the instance (SQL 2012) I am trying to reset the sa password on. Short of re installing, is there any other options?


Friday, November 21, 2014 - 5:19:24 PM - vp Back To Top (35367)

Amazing, thanks!

Tuesday, November 18, 2014 - 4:36:12 AM - Amr Gamal Back To Top (35319)

Thanks you very very very much. it works with me ;)

Wednesday, November 12, 2014 - 11:41:07 AM - HemangSQL Back To Top (35268)

Worked great on SQL 2008 R2. Thanks for saving my day!!!!

Friday, November 7, 2014 - 6:39:10 PM - Eunice Harris Back To Top (35230)

I don't have this problem....yet.  When I run Brent Ozar's sp_Blitz, it notes that NT AUTHORITY\SYSTEM and NT AUTHORITY\NETWORK SERVICE are SA users.  Is it ok that they are?  I and sa are the only other SA users.  I could see removing NT AUTHORITY\SYSTEM and NT AUTHORITY\NETWORK SERVICE from the list of sa's, but then I might run into the problem above.  If I should remove them from sa privileges how would I do that?

Thanks, Eunice Harris, IISCC

Thursday, November 6, 2014 - 3:40:01 PM - Rick Back To Top (35208)

Worked on a SQL2005 with SSMS Express.

Thanks!!!

Wednesday, November 5, 2014 - 6:00:40 AM - Nico Back To Top (35187)

Thanks, saved me! No other method worked. This was painless and easy.

Wednesday, October 15, 2014 - 1:02:11 PM - Julio Espinoza Back To Top (34970)

Thank you very much, It worked perfectly with Windows 2008 R2 x64

Thursday, September 25, 2014 - 10:11:36 AM - Tibor Back To Top (34713)
Hey man, thanks a lot, this article and PsExec helped me from losing my mind :).

Tuesday, September 23, 2014 - 9:36:21 AM - Ranga Chisi Back To Top (34676)

Worked for me. Thanx man. Thought would be stuck here but you saved the day.

 

Monday, September 22, 2014 - 7:14:14 PM - designworks Back To Top (34663)

just a tip that worked for me on Server 2008 R2 and should work for anyone else too, holding shift and right clicking the SSMS.exe program should give you the option to 'run as another user'. Simply put in the local admin account and password and there is no need for extra steps involving PSEXEC.exe.

Thursday, September 18, 2014 - 10:45:18 AM - Theron Knapp Back To Top (34603)

It seems like every time I have an issue with SQL Server, I find that Aaron has already written or blogged about it and provided a solution.  Another great example!

Thanks Aaron!

Friday, June 13, 2014 - 11:19:23 AM - Alberto Back To Top (32231)

Thanks! Great post! Works peferctly to me.

Tuesday, April 8, 2014 - 2:49:50 PM - Joachim Berger Back To Top (30011)

Worked perfect for me. Thank you!

Thursday, April 3, 2014 - 2:03:32 PM - Brian Back To Top (29968)

Thanks.  This article worked like a charm.

 

Friday, March 14, 2014 - 7:35:33 AM - Megatc101 Back To Top (29756)

Just want to say this is AWESOME and yes I'm shouting.

Tried the suggested solution on the Microsoft support pages , but kept getting errors about not being able to connect in single user mode, this worked like a charm.

 

Thanks for posting.

Monday, March 10, 2014 - 1:01:08 PM - neymar Back To Top (29691)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatt

 

no se ingles, me funciono correctamente, muchisimas gracias!! 

Tuesday, February 25, 2014 - 12:46:44 AM - andrew Back To Top (29559)

 

You're a life saver! this totally worked for me. The path was wrong, however, I just copied the path from the sql management studio shortcut on the desktop and it worked!

 

Once again, legend & lifesaver!

Monday, January 20, 2014 - 6:39:42 AM - Jeoffrey Back To Top (28140)

Many many thanks! This saved me a lot of time!!

Thursday, December 19, 2013 - 2:05:48 AM - So Relieved... Back To Top (27841)

Thank you thank you thank you.

What an AWESOME Christmas present. I had emabrrasingly disabled the group that I connected with and as it was the ONLY sysadmin group oops..

I tried and tried to log in as single user but this was impossible in a cluster with a plethora of monitoring gear and I was running out of time.

Please take to your Christmas the knowledge that you made at least one DBA VERY HAPPY indeed. I will donate $20 to the nearest poor person I see - there's a few around.

Whilst there may be security issues - if run from the server where one has adminsitrator permission there is lttle risk or you wouldn't have such permissions (generally). Again, All the best to you AAron.

Wednesday, October 23, 2013 - 10:40:10 AM - Steven Back To Top (27240)

Before finding this trick, I have resetted my forgotten sa password using the utility - SQL Server Password Changer. Your trick seems to be more awful and I'll also give it a try with my database later.

Friday, October 18, 2013 - 7:15:14 AM - Randy Ansems Back To Top (27184)

Itried this and I get the message: "Cannot alterthe login for 'sa', because it does not exist or you do not have permission. MS SQL Server Error: 15151"

Any thoughts?

Monday, September 23, 2013 - 9:45:12 PM - Ramon Araujo Back To Top (26907)

Awesome!!! Thank you very much. Ping back from Windows 7, SQL Server Express 2008 R2.

Thursday, September 12, 2013 - 9:50:43 AM - Alan Back To Top (26767)

I just wanted to say a big thanks to Aaron.

This solution worked on an XP SP3 machince with SQL 2008 Express, that no-one knew the SA password to.

Local Administrator re-instated !

Wednesday, July 31, 2013 - 2:45:35 PM - Dan Back To Top (26078)

I did this on a Windows 2008R2 server running SQL and it totally worked. Thanks its a lifesaver.

Wednesday, July 31, 2013 - 3:11:38 AM - Elmozamil Back To Top (26066)

Hello, It is really interesting post.

Wednesday, July 24, 2013 - 9:50:07 AM - YOU SAVED MY LIFE Back To Top (25980)

i've been trying to solve this problem from 10 straight hours ... AND FINALLY i saw your post , 
words can't describe how much i'm thankful :):):):) 

Wednesday, May 22, 2013 - 1:55:23 PM - Aaron Bertrand Back To Top (24083)

Two other articles that might be useful:

 

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

 

http://sqlblog.com/blogs/argenis_fernandez/archive/2012/01/12/leveraging-service-sids-to-logon-to-sql-server-2012-instances-with-sysadmin-privileges.aspx

Thursday, May 2, 2013 - 8:27:08 AM - Prashant Thakwani Back To Top (23683)

Thanks Aaron.

 

Tuesday, April 30, 2013 - 10:30:06 AM - Aaron Bertrand Back To Top (23631)

Prashant, it doesn't really matter. What it means is if NT AUTHORITY\SYSTEM is not on your system, or is not in the sysadmin role, then yes, you are absolutely correct, this method won't work. If you want it to work, then add NT AUTHORITY\SYSTEM. If you don't want it to work anyway, there's nothing to do.

Tuesday, April 30, 2013 - 9:08:23 AM - Prashant Thakwani Back To Top (23629)

Aaron,

Yes, I checked with all of my SQL Server 2012 instances (around 8) and none of them is provisioned using NT AUTHORITY\SYSTEM, which is inline with what the Microsoft documentation Says. I am still wondering, how it comes for you and Greg (though with different roles, Sysadmin for you and Public for Greg).

I will try to Provision SQL Server 2012 on one for the fresh Windows 2008R2 server and will update here if getting the different result than earlier.

 

Thanks

Prashant T

Wednesday, April 24, 2013 - 3:03:40 PM - Aaron Bertrand Back To Top (23548)

Greg ok, in any case, like I said, you can either add them to the sysadmin role if they're not already and in the future you may want to use this technique, or know that it won't work if you leave it as is.

Wednesday, April 24, 2013 - 2:50:33 PM - Greg Robidoux Back To Top (23547)

Hi Aaron, I just checked a couple of my servers and the NT AUTHORITY\SYSTEM is in the PUBLIC server role. There are a bunch of NT SERVICE \ logins that have the sysadmin role. Not sure if this a change or not.  Both instances I looked at are running 11.0.2100.

Wednesday, April 24, 2013 - 2:34:10 PM - Aaron Bertrand Back To Top (23546)

That said, if you have an installation where NT AUTHORITY\SYSTEM is not in the sysadmin role (whether it has been removed or SQL Server didn't put it there in the first place), then yes, you are out of luck. You'll be able to connect (assuming the login hasn't been removed altogether), but you won't be able to set the password for existing logins, create new logins, etc.

Wednesday, April 24, 2013 - 2:25:46 PM - Aaron Bertrand Back To Top (23545)

Prashant that may be what the documentation states, but every single SQL Server 2012 instance I can currently access has NT AUTHORITY\SYSTEM in the sysadmin role. I did not modify this and these are not upgrades; it is how SQL Server 2012 was installed. Do you have any instances where you have not made a change and this is not the case?

Wednesday, April 24, 2013 - 1:25:29 PM - Prashant Thakwani Back To Top (23543)

Thanks Aaron. I think you answer the question.

However, I was talking about the change in SQL Server 2012, which says that

             BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.

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

 

 

 

Wednesday, April 24, 2013 - 8:50:15 AM - Aaron Bertrand Back To Top (23539)

Prashant, this doesn't happen by default (unless you're confusing NT AUTHORITY\SYSTEM with Builtin\Administrators).

If you *manually* delete NT AUTHORITY\SYSTEM, *and* haven't added yourself and/or a domain admin as an administrator account, *and* lost the sa password, that seems like a perfect storm to me, and I can't say I have a whole lot of sympathy in that case. Shut down the service, install a new instance somewhere, and restore your latest backups. If you don't have recent backups available, then I have even less sympathy, as even more preventable things have gone horribly wrong. Grab the mdf/ldf files and try to attach them to the new instance.

Tuesday, April 23, 2013 - 10:01:57 PM - Prashant Thakwani Back To Top (23533)

Aaron, good stuff.

 

However, trying to understand on how it works when the NT AUTHORITY\SYSTEM account is removed from SQL Server 2012 instance (by default)?

 

Thanls

 

Tuesday, April 16, 2013 - 10:21:55 AM - Jasper Back To Top (23375)

Thanks a lot, this saved me a lot of troubles!

Friday, March 1, 2013 - 9:33:09 PM - Sergiy Back To Top (22523)

Works like a charm!!!!!

 

Thank you very much for this topic!!!!!!!!!!!!!!!

 

You saved my week!!!!!!!!!!!!!!!!

Friday, March 1, 2013 - 1:51:19 PM - Aaron Bertrand Back To Top (22515)

Jason, you might have to surround the path to Ssms.exe with double quotes, as I did in the sample code. It looks like it is being truncated at the first space.

Friday, March 1, 2013 - 10:56:23 AM - Jason Back To Top (22507)

This did not work for me. I have to change my path information to locate the Ssms.exe directory one I did that I excuted the command with the new path information, and recived an error..

 

PsExec could not start D:\Program on USCLA123A:

The system cannot find the file specified.

Am I missing something here?

Tuesday, September 4, 2012 - 4:39:19 PM - Kelly Back To Top (19391)
I was able to use this process on a Windows 2008 R2 server running SQL Server 2008 R2 where the SQL Server services were running as Local System. When I tried this on another server running SQL Server 2012 where the services were running as a local user, I was not able to add a user. After it failed, I checked and found that the SQL Server installation had only granted the public server role to the NT Authority/SYSTEM login. I don't have a SQL Server 2008 R2 system handy where the services are not running as Local System.

Tuesday, September 4, 2012 - 1:58:15 PM - TimothyAWiseman Back To Top (19388)

@K Brian Kelly  You have a good point; I can see how this could be considered a security issue.  With that said, I don't really expect locking my front door to keep someone out if I gave them the key to my garage door. 

 

As Aaron said, Admin on the machine is essentially the keys to the castle.  You can put some limitations on an admin, especially through use of cryptography, but they can legitimately override most restrictions and can circumvent many other restrictions if they are willing to be more devious (an admin can likely put in place a key logger to eventually get those encryption keys for instance). 

Friday, August 31, 2012 - 4:21:07 PM - Peter Szegedi Back To Top (19358)

Yep, I just mentioned this as an alternative. Impersonating System is a safer way.

Friday, August 31, 2012 - 3:58:37 PM - K. Brian Kelley Back To Top (19357)

Peter, note Argenis' caveat: you can only do this if there's a currently established session with the sa login. No session, no password. Basically, the password for an SQL Server-based login is visible in memory on the system if the session is established.

 

Friday, August 31, 2012 - 3:57:21 PM - Aaron Bertrand Back To Top (19356)

@Peter that sounds a lot more invasive and dangerous than using PSExec.exe or even incurring some downtime using he other methods.

Friday, August 31, 2012 - 3:20:07 PM - Peter Szegedi Back To Top (19354)

If your client SQL Server is under SQL 2012 you can also (proc)dump the sqlservr.exe process. Then run strings.exe to filter it. Then search in the filtered file for the following string: <instancename>sa (for example if you instance name is TestSQL then you searching for TestSQLsa) Right next to this string you will see the sa password. Watch out the procdump will cause some hanging on the instance while it dumps everything from the memory to the disk.

Source:

http://sqlblog.com/blogs/argenis_fernandez/archive/2012/01/20/on-the-topic-of-lost-sa-passwords-on-sql-server-2000.aspx

Friday, August 31, 2012 - 2:24:15 PM - K. Brian Kelley Back To Top (19353)

Tim,

  it is a security issue because it is a bypass of a security control. Of course, you expect such controls to be a deterrent, not 100% effective. From an audit perspective, if the data is that sensitive, you need to impose other controls, like event auditing to see who is logging on and reporting that accordingly.

Friday, August 31, 2012 - 1:09:06 PM - TimothyAWiseman Back To Top (19351)

I am not certain you could call this a security issue.  A local admin probably should be able to get into SQL Server, and even if you truly structured it so they couldn't, then that still does not protect the data from them since they can access the files.

 

You could limit their access to the data through encryption, and there are types of logging that can make it very difficult to make changes without leaving tracks, but a local admin will always be able to cause a vast amount of mischief if they were so inclined and you should never give anyone admin access unless you can trust them.

 

Thursday, August 30, 2012 - 4:41:18 PM - Aaron Bertrand Back To Top (19329)

Gene, yes, if you give someone local administrator rights, there are many ways they can break into SQL Server, not just the one I showed here. This is just the one that has least impact on a running system. SQL Server tried to make it harder for local IT administrators (but non-SQL administrators) to gain access to SQL Server, but basically a local administrator owns the machine and can take ownership of anything running on it.

The security problem this highlights is a different issue altogether. Keys to the castle, and all that.

Aaron 

Thursday, August 30, 2012 - 12:56:51 PM - Gene Wirchenko Back To Top (19325)

Does this mean that if you have administrative access to a system that you can break into SQL Server? Or am I missing a bit here?

Thursday, August 30, 2012 - 12:20:11 PM - TimothyAWiseman Back To Top (19324)

Aaron, thank you for posting this.  I have had to do this the hard way in other instances and this may save me quite a bit of time when I run into this again. 

I would reiterate the comment about not using the SA account for other programs though.  Generally, a policy of least priveleges is good for security, and it is fairly rare that a program needs admin level priveleges to the SQL Server (though it does come up).  Even when a program really must connect to the server with its own admin credentials, I would give the program its own account if only to avoid problems when I change the SA password and to make tracking the source of activity a little bit easier.

Thursday, August 30, 2012 - 10:32:24 AM - ron Back To Top (19322)

thanks, guys.

Thursday, August 30, 2012 - 9:57:10 AM - Aaron Bertrand Back To Top (19321)

Ron please see:

http://support.microsoft.com/kb/313565

The KB article says Windows 2000 (and Microsoft will try to coerce you to use today's GUI equivalents, e.g. task scheduler) but AT is alive and well even in Windows 8.

Thursday, August 30, 2012 - 9:53:56 AM - K. Brian Kelley Back To Top (19320)

The AT command is a command-line hold-over from the days before Task Scheduler. It allowed you to schedule processes to run on Windows systems. When Task Scheduler was added to the OS, AT wasn't eliminated. Instead, using AT creates a Task Scheduler task, but running with the security account specified for any tasks coming from the AT command.

More at TechNet: http://technet.microsoft.com/en-us/library/cc772590(v=ws.10).aspx

 

Thursday, August 30, 2012 - 9:36:52 AM - ron Back To Top (19318)

KBK, please explain "use the AT command" if you have time. tks.

Thursday, August 30, 2012 - 8:36:27 AM - K. Brian Kelley Back To Top (19316)

If you're in an environment where you can't get psexec onto the system, you can use the AT command and schedule it to start a minute or two after you execute the command. By default, task scheduler tasks that are scheduled using the AT command run as System, though that should normally be changed to prevent the AT command from being exploited in this manner.

 














get free sql tips
agree to terms