One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Monday, May 13, 2019

When Your Options Are Limited

In my role I am still on-call several times each month, and many of the escalations that come are simply things that newer DBA's and Help Desk staff have yet to run into - things that many employees at their level *can* handle, once they have experienced it.

This is one of those stories.

Our monitoring alerted the Help Desk because a client SQL Server was down (in this case, SQL services were stopped but Windows was not down).  In most cases this is handled between our Help Desk and if needed, the client, but in this case it was paged out to me with this note from the help desk technician:
"Upon checking on server Generic SQL Server, SQL Server Agent and SQL Server are failed - these services have automatic startup type but I can't start the services because the start option is disabled  "
And they added a screen shot:

There are multiple reasons why you can't start a service, most of them around permissions (role memberships, explicit permissions, etc.)  With what we do as a managed services provider (MSP) we usually have extensive administrative privileges on the servers on which we work, so true permissions aren't usually a problem.

But UAC is.

User Account Control (UAC) was introduced in Windows Vista and has since been expanded across multiple Windows desktop and server platforms.  At a basic level, it is an extra layer of security to make sure that changes to the operating system and it's components are only performed by administrators, and only when actually intended - think of it as an "Are You Sure You Want To Do That?"

UAC is an important security feature but can also be pretty annoying when you perform administrative tasks all of the time.  When I was looking for a good descriptive link to reference in the paragraph above, five of the top ten items were some variant of "here's an easy way to turn off UAC" (and three of the other five were "why you should never turn off UAC!")

The functional gotcha is the screen shown above.  The technician had not run the services console "as administrator" and as such did not have the token authority to start/stop Windows services.

This is an easy fix, however...instead of double-clicking on the services icon, simply right-click and select "Run As Administrator"

If your login is a Windows administrator. this then runs the services console with the administrator token, and you can then start/stop/manage services (as well as break/delete/befoul services - be careful!) to your heart's content.

If your login is *not* a Windows Admin, you will receive a prompt which will allow you to enter administrative credentials which will be used solely to run the application in question - it isn't a full context switch to that login for your whole session.

Note - you will often also run into this on some systems with SQL Server Management Studio and other SQL Server tools if you are running them locally on a server - if you run the tool and cannot connect to your local SQL Server instance, it may be that you haven't run the tool "as administrator."  Simply right-click the icon for the tool and, as above, select "Run as Administrator."


Hope this helps!