locked
How to audit SQL Server Instance level configurations RRS feed

  • Question

  • Hi!

    We have a supplier that insists upon having sysadmin rights on several of our boxes. My boss has agreed that they could have this on the basis that I can audit high level server configuration changes. Things like sp_configure, enabaling AWE, changing maximum server memory, enabling cross database ownership changeing, changeing FILESTREAM Access Level etc.

    (Basically the settings available in server properties in SSMS). I had a look at Audit specifications, but it doesnt seem to audit these settings.

    Is there a way to do this? 

    Thanks,

    Zoe

    Tuesday, October 7, 2014 7:30 AM

Answers

  • Hello Zoe,

    With the default trace you already have an audit for the most recently changes. In SSMS do a right mouse click on the server node => Reports => Standard Report => Configuration Changes History.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Zoe.Ohara Tuesday, October 7, 2014 10:39 AM
    Tuesday, October 7, 2014 8:12 AM

All replies

  • Well, even if you could your supplier would be able to turn it off. If you don't trust him or if you can't keep an eye on him in person, don't give him sysadmin rights.

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, October 7, 2014 7:42 AM
  • Hi,

    Please refer below MS article on SQL Server Audit.

    SQL Server Audit (Database Engine)

    Also refer

    An Introduction to SQL Server Audit

    http://solutioncenter.apexsql.com/sql-server-database-auditing-techniques/


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Tuesday, October 7, 2014 7:44 AM
  • Ha! that's what I said!

    unfortunately this ones over my head, im not allowed to say no.

    The idea of this is that if they do screw it all up I have evidence that its not our fault.

    I agree he can turn it off, but perhaps I can can evidence of that too.

    Tuesday, October 7, 2014 7:55 AM
  • How does he access your your box(es)? Via any recordable remote access software? My idea would be to record his session...

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, October 7, 2014 8:00 AM
  • Thanks Praveen,

    Ive had a look at those links, but unfortunately the things they audit are too granular a level for what I need. Im not trying to audit DDL/DML or Login activities. Im trying to audit Server level configuration changes.

    I suppose I could export the log on a regular basis and look for things like:

    Configuration option 'max server memory (MB)' changed from 5000 to 6000. Run the RECONFIGURE statement to install.
    Configuration option 'min server memory (MB)' changed from 0 to 1000. Run the RECONFIGURE statement to install.
    Configuration option 'index create memory (KB)' changed from 0 to 704. Run the RECONFIGURE statement to install.
    Configuration option 'min memory per query (KB)' changed from 1024 to 2048. Run the RECONFIGURE statement to install.
    Configuration option 'max worker threads' changed from 0 to 128. Run the RECONFIGURE statement to install.

    But I was hoping there would be a simpler way to achieve this?

    Thanks,

    Zoe

    Tuesday, October 7, 2014 8:05 AM
  • Hello Zoe,

    With the default trace you already have an audit for the most recently changes. In SSMS do a right mouse click on the server node => Reports => Standard Report => Configuration Changes History.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Zoe.Ohara Tuesday, October 7, 2014 10:39 AM
    Tuesday, October 7, 2014 8:12 AM
  • The only problem with this is that the default trace can be stopped and deleted an the evidence is gone. Still not waterproof.

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, October 7, 2014 8:20 AM
  • Another idea: Use remote monitoring software which your supplier can't access. If he would turn off any traces this event would be in a safe place. e.g. Profiler storing a file on your WS :-) But make sure you don't monitor too many events.

    Howzat?


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de


    Tuesday, October 7, 2014 8:41 AM
  • Hi Zoe,

    In your situation I would cajole my superior to give second thought about giving sysadmin privileges. Also even using audit you cannot completely trace his activities audit has its limitations and also SQL Server audit feature is available only for Enterprise edition.

    You would have to talk to your superior about repercussions of providing sysadmin privileges. Why does a supplier needs sysadmin privileges what are the things he need to do. With my career in SQL I have learned policy of least privilege to only give rights which is necessary. You need to have a meeting with your superior and Supplier and have to say him 'boss I cannot give sysadmin to you' .Ask him to list down all tasks he need to do and keeping that in mind prepare a list of privileges which needs to be given.

    Who owns the database ? If it is you why would he need to change max server memory, filestream setting, sp_configure settings WHY ? What possibly he could achieve. You need to be strict here to avoid any bad situation in long run it would save you from lot of hassle. 


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, October 7, 2014 8:49 AM
  • Yes, I agree with this in total and that's why I said "don't give him sysadmin".

    If he needs to have the configuration changed, for whatever reason, he could also ask you to make these changes which gives you the opportunity to ask him why.

    You don't play games in a live environment.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de


    Tuesday, October 7, 2014 9:07 AM
  • Perfect!

    Thanks Olaf!

    Tuesday, October 7, 2014 10:40 AM
  • Yes I completely agree we shouldn't be giving it to them!

    Unfortunately, the users have gone over our heads to the business and made threats. The suppliers have refused support without sysadmin rights - I know its unorthodox but that's the way its happened.

    I'm not senior nor influential enough that they would listen to me. I've told them im only supporting it on a 'best en-devours basis'

    Tuesday, October 7, 2014 10:44 AM
  • Unfortunately, the users have gone over our heads to the business and made threats. The suppliers have refused support without sysadmin rights - I know its unorthodox but that's the way its happened.

    I'm not senior nor influential enough that they would listen to me. I've told them im only supporting it on a 'best en-devours basis'

    So we are now in the area of politics, but you and your superior could take counteractions. Presuming that you are an IT department that makes internal invoicing to the department that wants this supplier, you can say that you no longer can take responsibility for this server, and it will fall out of an service agreement. You will help them in case of problems, but they will have to pay hour by hour.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 7, 2014 9:21 PM