Single User Mode – Back to Basics

In a recent article, I took you on a trip through how to hack (ethically) a SQL Server to regain sysadmin access. In that article, I made quick mention of restarting SQL Server into single-user mode. It only makes sense to show quickly how to get into single-user mode.

Before getting into that, I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Single-User

So, what exactly is this single-user mode thing? Single-user mode is basically the official back-door into SQL Server for various reasons such as:

  • Somebody deleted all of the logins that were in the sysadmin role.
  • The sa account is disabled or the password has been forgotten.
  • Somebody deleted any Windows groups that were members of the sysadmin role.
  • All members of the sysadmin role are no longer with the company.
  • You need to restore the master database
  • You want to keep SQL Server all to yourself because you are greedy!

These are some pretty solid reasons to need to be able to use the back door. But how exactly do we get to the back door?

Two Paths

As luck would have it, there are two ways to enable single-user mode. You can either get there by making some changes for the SQL Server service in Configuration Manager, or you can utilize a command prompt. I won’t cover the gui path beyond the gentle reminder that you must remember to undo your change when using that method.

My preferred method is through the command line. Using my SQL Server 2017 as the experiment, I would navigate to the Binn directory for that instance. In this case, as shown in the next image.

Before getting too far ahead of myself, I am going to stop my SQL Server.

Notice, I also queried to find all of my services related to SQL before stopping the MSSQLServer service via the net stop mssqlserver command. We will come back to some net start and net stop commands later.

With the service successfully stopped, I can now restart the service in single-user mode.

And then the validation that we are indeed starting in single-user mode…

But wait, did you notice that bit of trickery on the startup command?

This is a pro-tip for when you must use single-user mode. Inevitably, somebody will steal the single-user connection and you will be locked out of the session. By using an app name after the single-user switch, you are telling SQL Server to only accept connections for that specific application. Since most apps will not be using sqlcmd, you will have far less contention to gain that connection and you will be able to complete your task much easier.

You could also pass something like this instead…

In this case, I would be limiting the connections to a query from SSMS (and not object explorer).

Now that I have a single-user connection, I can add a sysadmin or restore the master database or just sit on it and play devious. It all depends on what your objective for the single-user session happens to be.

More Command Line

Remember that reference to the NET commands? Well, it turns out we can also start SQL Server in single-user via net start. Let’s check it out.

The command is pretty simple:

The effect here is the same as navigating to the Binn directory and starting SQL Server with the sqlservr.exe executable. The big difference is considerably less typing and less verbose output of the service startup.

When using the net start method, you do need to know the service name of the SQL Server instance. To get that, I do recommend the following powershell script.

This will produce results similar to the following.

From the results, I can pick the SQL Server service and then pass that to the net start command fairly easily.

The Wrap

Starting SQL Server in single-user mode should be a tool every data professional holds in the bag. This is an essential tool that can be used in multiple scenarios and ensure you are able to fully maintain and control your server. I have shown how to get to single-user mode via two command line methods and mentioned a GUI method. The nice thing about the command line methods is that you don’t have to remember to undo the startup switch like you do with the GUI method.

If you feel the need to read more about single-user mode, here is an article and another on the topic.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Lost that SQL Server Access?

As a data professional can you recall the last time you needed to support a SQL Server instance for which you had no access? What if you used to have access and then that access magically disappeared?

I know I run into this dilemma more than I would probably like to. It is rather annoying to be under a crunch to rapidly provide support only to discover you are stuck and have to wait on somebody else who hopefully has access.

It’s one thing to not have access in the first place. This is usually an easy fix in most cases. The really unpleasant access issue is the one when you have confirmed prior access to the instance and then to be completely locked out. More succinctly, you have lost that SQL access!

Whoa is Me!

All hope is now lost right? OK, that isn’t entirely true. Or is it? What if everybody else from the team is also locked out and there is no known sysadmin account. In essence everybody is locked out from managing the instance and now you have a real crisis, right? Well, not so fast. You can still get back in to the instance with sysadmin access. It should be no real secret that you could always restart the SQL instance in single-user mode. Then again, that probably means bigger problems if the server is a production server and is still servicing application requests just fine.

What to do? What to do?

Restart Prohibited

If you really cannot cause a service disruption to bounce the server into single-user mode, my friend Argenis Fernandez (b | t) has this pretty nifty trick that could help you. Truth be told, I have tested that method (even on SQLExpress) several times and it is a real gem. Is this the only alternative?

Let’s back it up just a step or two first. Not having access to SQL Server is in no way the same thing as not having access to the server. Many sysadmins have access to the windows server. Many DBAs also have access to the Windows server or can at least work with the sysadmins to get access to the Windows server in cases like this. If you have admin access to windows – then not much is really going to stop you from gaining access to SQL on that same box. It is a matter of how you approach the issue. Even to restart SQL Server in single-user mode, you need to have access to the Windows server. So, please keep that in mind as you read the article by Argenis as well as the following.

Beyond the requirement of having local access to the server, one of the things that may cause heartburn for some is the method of editing the registry as suggested by Argenis. Modifying the registry (in this case) is not actually terribly complex but it is another one of those changes  that must be put back the way it was. What if there was another way?

As luck would have it, there is an alternative (else there wouldn’t be this article). It just so happens, this alternative is slightly less involved (in my opinion). Let’s start with a server where I don’t have SQL access (beyond public) but I do have Windows access.

We can see on this SQLExpress instance on the TF server that my “Jason” does not exist. Since I don’t have access, I can’t add my own account either. Time to fix that. In order to fix it, I am going to create Scheduled task in Windows that will run a SQLCMD script from my C:\Database folder. The folder can be anywhere, but I generally have one with scripts and such somewhere on each server that I can quickly access.

From here, you will want to click on the “Change User or Group” button to change it to an account that does have access to SQL Server. The account that I use is not a “user” account but rather it is a “system” account called “NT AUTHORITY\SYSTEM” that is present all the way through SQL Server 2017.

To locate the “NT AUTHORITY\SYSTEM” account, just type “SYSTEM” into the new window and click “Check Names”. The account will resolve and then you can click OK out of the “Select User or Group” window.

With the account selected that will run this task, we can now focus our attention on the guts of the task. We will now go to the “Actions” tab.

Click the new button, and here we will configure what will be done.

I do recommend putting the full path to SQLCMD into “Program/Script” box. Once entered, you will add the following to the parameter box.

If you do not have an instance, then just the server name will suffice after the -S parameter. The -i parameter specifies the path to the SQL script file that will be created and placed in the C:\database directory (or whichever directory you have chosen).

That is it for the setup of the task. Now let’s look at the guts of the script file.

Save that into a script document named myscript.sql in the aforementioned directory and then execute the windows task. After executing the Windows task, it is time to verify if it worked or not.

Boom! From no access to a sysadmin in a matter of seconds. Here is that quick verify script – generalized.

The Wrap

Losing access to a SQL instance is never a desirable situation – for the DBA. When the people that are supposed to have access, lose that access, all hope is not lost. There are plenty of methods available to regain the requisite access to manage the server. Today, I shared one such method that I view as being extremely easy. If you lose access, I would recommend taking the steps shown in this article to regain that access.

While not in the back to basics series, I do recommend checking out my other posts in that series. Some topics in the series include (but are not limited to): Backups, backup history and user logins. I would also recommend reading this audit article. If you are able to elevate your permissions, then obviously anybody with server access can elevate their permissions too. For that reason, you should regularly audit the permissions and principals in SQL Server.

T-SQL Tuesday #102: Giving Back

Comments: 1 Comment
Published on: May 8, 2018

bleeding heartLast month we had the opportunity to discuss some of the most important tools for a data professional. I took that opportunity to discuss how it is important to blog. As it turns out, that article correlates fairly strongly to today’s article.

These are maybe some of the questions that Riley Major (b | t) would like for us to examine about our own deep dark secrets and psychological makeup:

  • Why do we give back?
  • How do we help give back?
  • What do we plan to do to give back?

in this, the 102nd, installment of TSQL Tuesday.

If you are interested in reading the original invite, you can find that here.

“Now I will give you an opportunity to give back. Everyone reading this has benefited from their fellow data professionals. And that benefit puts you in a position to share alike. You’ve learned something, so you can teach. You’ve been supported, so you can help. You’ve been led, so you can lead. But you don’t have to do it alone. We’re all going to do it together.

So here is my call. Pick some way you can help our community. “

Brief Intermission

A shout out is absolutely necessary for Adam Machanic (twitter) for picking the right blog meme that has been able to survive so long in the SQLFamily. This party has helped many people figure out fresh topics as well as enabled them to continue to learn.

Reality Check

Very much related to my blog post about blogging, I have to echo the sentiment about how “Blogging helps you become a better technical person.” A lot of what I do for my blog is there to help the community, but it has a self-serving purpose. It helps me become a better technical person. It also helps me to improve my communications and writing skills.

There are some side effects of blogging as well. Each of us has a finite number of keystrokes in our lifetime. That said, it makes sense to write certain technical things down in a blog post rather than retyping the same information over and over for various different email or forum responses. Make sense? If nothing else, it just seems more efficient to write a long technical explanation once rather than 12 times.

So there we have a couple of self-serving reasons to blog. Those same self-serving reasons also frequently apply to being involved in the community. For example, the more you exert yourself to help answer forum questions, the more you learn. You become a more experienced technical person. In addition, you learn how to communicate better and write better (hopefully). You are practicing your craft in a public forum where people can easily shred you (and they often do), when you are wrong – even minutely wrong. This potential for being blasted in the forums typically makes one work harder at getting everything just about perfect.

If you opt to speak in front of technical people, guess what? You are doing the same things I just wrote about in regards to forum responses as well as with blogging. The big difference is that you are now doing it in person, live, on stage, and verbally! You have really put yourself out there in a big way to go speaking in front of people. You will likely double down even more with regards to ensuring your material is very near perfect and bullet proof. In addition, you will probably practice a few (hundred) times to make sure you don’t fumble with your words. What does this mean? You are becoming a more solid technical person and honing your communication skills. Again, very self serving!

Or is it? The one final aspect of being a community visible person is the drive behind what you do. I like to share what I learn. I also like to share my time. I believe in serving others with a charitable demeanor. Giving of yourself will always enhance your life more than you can imagine – when you do it with the attitude of putting others first. There is no selfish intent to those that really want to help the community.

It doesn’t matter if you are helping the sqlfamily, your local Scouting organization, boys and girls clubs, sports teams, or volunteering at the local schools etc; if you are doing it with the intent to serve and do good – you will enhance your life in some way. If you are doing it for some accolade or truly self-serving reason, you may get the accolade but you will find yourself stunted in the growth potential.

People that give of themselves freely is such an awesome characteristic. There are many in the SQL community that truly give of themselves freely – like SQLSoldier. When it is a part of your identity, it comes naturally and there isn’t a lot that needs to be done to plan for it. Sometimes, maybe it would be nice to be able to have more time to be able to do more – sure. And that is the beauty of this characteristic. If you are giving of yourself freely, you often find that you want to give more. That is great! Do what you can, when you can. Sometimes, it will be more. Sometimes, it will be less. It is all good as long as the heart is in the right place.

TSQL2sDay150x150The Wrap

This has been my diatribe about service and giving back to the community. When done properly, there is a natural born effect of enhancing one’s personal life equal in some way to the amount of effort given towards the community.

Oh, and if you are interested in some of my community contributions (which according to Jens Vestargaard is an awesome contribution), read this series I have published.

page 1 of 1

Calendar
May 2018
M T W T F S S
« Apr   Jun »
 123456
78910111213
14151617181920
21222324252627
28293031  

Welcome , today is Sunday, September 23, 2018