June 8, 2004 at 2:58 am
Hi!
One weekend a guy from development placed some sp_configure instructions inside a procedure that is used by one of the jobs. So I got this on the server:
Date Source Message
2004-06-06 16:17:27.16 spid91 Configuration option 'set working set size' changed from 1 to 0. Run the RECONF
2004-06-06 16:17:27.25 spid91 Configuration option 'min memory per query (KB)' changed from 4096 to 0. Run th
2004-06-06 16:17:27.36 spid91 Configuration option 'max worker threads' changed from 255 to 0. Run the RECONF
2004-06-06 16:17:27.47 spid91 Configuration option 'max degree of parallelism' changed from 2 to 0. Run the R
2004-06-06 16:17:27.58 spid91 Configuration option 'cost threshold for parallelism' changed from 10 to 0. Run
2004-06-06 16:17:27.80 spid91 Configuration option 'remote access' changed from 1 to 0. Run the RECONFIGURE s
2004-06-06 16:17:27.91 spid91 Configuration option 'remote proc trans' changed from 1 to 0. Run the RECONFIGU
2004-06-06 16:17:28.02 spid91 Configuration option 'nested triggers' changed from 1 to 0. Run the RECONFIGURE
This means that anyone who has permission to edit SPs can administer MS SQL server! Such people can crush server, grant any rights to themselves, etc. etc. Is there a way to block such activity?
Thanks.
June 8, 2004 at 3:17 am
Only sysadmin and serveradmin can execute sp_configure for updating. The check is carried out at runtime.
June 8, 2004 at 5:13 am
Each TSQL Step in Every Job has got a property called as "Run as user" which by default is "(Self)". It is the same account as that used by SQL Agent. This again is defaulted to "sa" / windows Authentication in most configurations.
Hence the job and the developer's SP actually would have executed in a sysadmin context.
check if this conditions were true in your case.
Change the "Run as user" and see if you get the same configuration messages again.
June 8, 2004 at 10:36 am
Also, I would suggest that developers should not have the ability to create/modify jobs, or to implement stored procedures on a production server. They should be coming to you for that, and you would then see the configuration changes in the procedure before it is implemented.
In our shop, we have a job that checks and archives the configuration, and notifies the dbas when the configuration changes, so that we can take the server back to its previous configuration if someone does happen to make an unauthorized change.
Steve
June 8, 2004 at 10:41 am
Steve (hoo-t),
Have you considered submitting that script? I think you should, it would be a great addition to the library.
-SQLBill
June 8, 2004 at 12:51 pm
SQL Bill:
How bout this one.
It creates a table.
Uses sp_configure to move settings into that table then add in the settings that have changed and what date they were found to change. So you track the date of when configuration changes were made.
First it inserts all configurations then it only puts in changes from the last time it was tracked.
Tracking who did it could probably only be done through a trigger. Then you could just add user name to the stored proc.
Create Table ConfigureLog(
EntryOrder int IDENTITY(1, 1),
[name] nvarchar(70),
minimum int,
maximum int,
config_value int,
run_value int,
changed datetime
)
Create Procedure Usp_TrackConfigChanges
as
Create Table #ConfigureLogtemp(
[name] nvarchar(70),
minimum int,
maximum int,
config_value int,
run_value int
)
Insert #ConfigureLogtemp
Exec sp_configure
If Exists(select [name] from ConfigureLog)
Begin
Insert Into ConfigureLog([name],
minimum,
maximum,
config_value,
run_value,
changed)
select tem.[name],
tem.minimum,
tem.maximum,
tem.config_value,
tem.run_value,
GetDate()
from #ConfigureLogtemp tem, (select [name],
Max(EntryOrder) as EntryOrder
from ConfigureLog
group by [name]) main, ConfigureLog
where tem.[name]= main.[name] and
main.EntryOrder = ConfigureLog.EntryOrder
and
(tem.minimum ConfigureLog.minimum
or tem.maximum ConfigureLog.maximum
or tem.config_value ConfigureLog.config_value)
end
else
begin
Insert Into ConfigureLog([name],
minimum,
maximum,
config_value,
run_value)
select tem.[name],
tem.minimum,
tem.maximum,
tem.config_value,
tem.run_value,
GetDate()
from #ConfigureLogtemp tem
end
Drop Table #ConfigureLogtemp
GO
June 9, 2004 at 10:49 am
SQLBill,
Thank you for suggesting that I submit my stored proc. I took a look at it, and it makes a LOT of assumptions about our 'DBA' database being present, with tables, and other homegrown procedures. I've considered submitting some scripts for quite a while, just haven't taken the time to "proof-read" them for these types of issues. I've also considered setting up a small website that would be a "repository" of my scripts/procedures. I'm going to try to do one or both in the near future. In the meantime, I'd be happy to e-mail the configuration script to you or anyone else, with the understanding that you'll need to modify it to fit your environment.
Steve
June 9, 2004 at 1:36 pm
You could try to find which is the sp that stores the instruction, and then take a look to the permissions. Script all the sps through EM, and find the 'sp_configure' string.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply