Security: Is there a way to lock down SQL Server Agent ??
-
Hello,
MSSQL 7/2000; using Enterprise Manager tool; Mixed Mode Authentication
I've noticed that a given user (NT or MSSQL) is able to START and STOP the SQL Server Agent service having been given only database access using his/her Enterprise Manager tool! <The option is NOT ghosted out when right clicking SQL Agent under Management>.
Is there a way to prevent this (for both SQL & NT autenticated accounts)? We've had users "accidently" stop the service.
Many thanks. Jeff
-
I thought you had to have admin rights on the box. Are you sure these people don't have rights to stop/start services?
-
It still shows up in the right-click menu, however if one of those non-sysadmin users tried to stop/start the service. They would receive an error message stating "Access Denied"! This is the same response I gave to your earlier post, which you obviously neglected to read.
A.J.
DBA with an attitude -
Hi Steve,
Do a simple test ... create a MSSQL user and grant access to database.
Hi awilbur77 ... not neglected ... but not really the answer I was looking for ... my test user is a non-sys user (sql authentication) !! You suggested at the Windows OS level ... good for NT accounts ... how do you prevent a MSSQL user account from stopping/starting services ???!!!
IS THERE A WAY TO CONTROL THIS WITHING THE DATABASE SERVER SOFTWARE (ie: SQL Server)?????
Many thanks. Jeff
-
I got a suggestion, quit your job as wanna-be DBA and go dig ditches for a living.
A.J.
DBA with an attitude -
Please be carefull not to cross post or duplicate. Addiitonal comments http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=123292#bm123397
-
In testing did you test on a server machine that is seperate from any domain controller, is not the machine you are testing from, and does not have a user/password account the same as the one you are logged into for testing from. If any of those conditions exist it would use the Windows account of the machine you are logged into with all the permission valid for it, not that of the SQL Account. That is one thing you have to be carefull of.
-
SQL Sever and SQL Agent run as Windows Services. Starting and stopping windows services is done according to windows permissions and has nothing to do with sql users. Even though you are using SQL EM / Sql Service Manager to start/stop the services, it's actually making windows calls similar to 'NET STOP <service>'. There is no SQL command to start or stop SQL Agent. There is SQL no permission for the services.
If the user is part of the Administrators group on the machine SQL Server/SQL Agent is running on, they have permission to start /stop it. Power Users may also have permission. You need to run Group Policy editior to change the access permissions to the MSSQLServer + MSSQLAgent Services if you want to prevent access. Or, make sure no one is in the administrators group on the server box.
See MS KB article 283904 and related
http://support.microsoft.com/default.aspx?scid=kb;en-us;283904
Julian Kuiters
juliankuiters.id.au -
is the xp_cmdshell still open ?
exec xp_cmdshell 'net stop sqlagent' might be your weak spot.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
awilbur77 ... thank you for your insight into this potentially serious security loop ... but I suspect you've forgotten the professionalism part of a DBA code of ethics ...
Many thanks. Jeff
-
Hello to all, thanks you for your suggestions. I appologize for cross posting, but thought this was a better venue to seek guidance.
The user is not a super user, or a sys admin user. The account does not belong to a windows group. The account is MSSQL ... using SQL Server 2000 with SP3 on Win 2000 Server ... here are the steps:
1. As sysadmin, using Enterprise Manager (EM) ... (connected remotely to db server) I create an server login account (zz_test) using SQL Server Authentication and password.
2. As sysadmin, using EM, I grant this account (zz_test) access to a given database (no permissions or membership to roles .. albeit the public databse role ... which has not been altered ... same defaults as initial install).
3. As sysadmin, using EM, I "Edit SQL Server Registration Properties" ... change my connection properties from syadmin to "Use SQL Server Authentication" and use my account with no rights (zz_test) .
4. An MS info message box pops up .. "Connection information has been changed for the active connection. Do you wish to disconect?" ... answer "Yes".
5. As zz_test, using EM, I can perform the following steps: "Management" > "SQL Server Agent" > right click ... and select "STOP" (or START). The "Display Error Log" option is the only one ghosted !!!
Hopefully someone can shed some insight into this? This poses potential admin risks and challanges to DBAs ... Steve Jones ... are you still out there???
Many thanks. Jeff
-
Jeff,
The key to this has already been posted. The ability to stop/start SQL Agent has nothing whatsoever to do with internal SQL Server authorities. It is purely a NT thing.
You can have sysadmin in SQL Server, but if you do not have the NT authority to stop/start services you cannot stop SQL or SQL Agent.
You can be a user who just has rights to log in to SQL Server but has no rights to query anything, or even someone who cannot even log in to SQL Server, but if you have NT authority to stop/start services you can stop SQL Server and SQL Agent.
You need to look at the NT rights of the user who is able to stop SQL Agent, not the SQL Server rights.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed ... just performed several tests from the NT side ... NT supercedes ... many many thanks to your posting ... the clouds have lifted ...
Many thanks. Jeff
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply