SOX and SQL server 2000 988



  • I am trying to get my SQL server 2000 SOX Compliant. I am looking for some ecxample of what other companies are currently doing.
    There are certain functions that require sys Admin rights. These right allow the user to anything they want. I need a way to separate SA and DBA functions. I have created a new account to create new users. I removed the SA from all other users in the system. the problem is without SA rights there are many daily functions that will need to be completed by a completely different departmaent.
    Any help would be GREATLY appreciated. This SOX stuff is killing me…
    Thanks,
    Joe



  • Can any of the daily fuctions be automated? (Trigger and Procedures)



  • I have recommended some of these controls to my clients. See if they can help you aswell.

    1. Access to the critical databases is limited to specific application users and DBA IDs. The likelihood of unauthorized access is also reduced since general users are forced to access data through the application layer alone.
    2. All other access to database (including for adding users) - is provided via the application.
    3. To improve the database security, Win NT authentication is implemented/ enforced.
    4. Use of SA/Admin passwords is eliminated and all access is granted by unique user ID and passwords. Where SA account is to be continued for intra -system functionality, the passwords are changed 9and periodically)from the generic ones that came with the setup.
    5. ODBC access to SQL Server is limited to key individuals in Accounting Group. Such access is authorized by the management and is controlled as ‘Read Only’. SA account is not used to establsih DNS connectiions.
    6. System Administrator ensures that the latest OS and SQL Server Service Packs/Hot-Fixes are applied on the Production Server and this practice flly complies with optimal ( testing) Change Mgmt practices.
    7. Access to the critical stored procedures and extended stored procedures are restricted to Systems Administrators only. User defined stored procedures/ triggers and Jobs are closely monitored and adhered to change management procedures.
    8. All user log-ins are captured in the designated table / log file. Failed log-in attempts are recorded in a text file and alerts are sent to the System Administrator.
    9. Various SQL Security reports are available to IT administration that includes any new exploits, successful attacks, backup storage protection, and object access failure statistics. IT management reviews them on a periodic basis and corrective measures are applied.
    10. Transaction logs are turned on for critical production database servers. Adequate log file capacities are created.
    11. DBA’s continuously monitor for any overflow issues and also ensure that transactions logs are included in the regular/dally backups.
    12. Management conducts periodic recovery testing ( from the SQL Backup tapes) to ensure that the financial data can be recovered successfully.
      Some of these controls could be in place already at your environment. Some of them may not apply to you. You need to extra vigilant with Auditors - if- your SQL 2000 Databases support/host your financial applications.
      Good Luck …


  • I work in a 15 person DBA shop that supports 425 SQL servers, one-third of which are production. All 15 DBAs are sysadmin on all of our SQL Servers. We do not separate duties into dev, QA, and prod, and we are not developers or business people.
    We don’t often use the sa account, but there are rare occasions where windows authentication was not available on a server and we had to use the sa account to get in.
    We have been looking at several solutions to the sa account problem in the context of a SOX audit. The problem as defined is that the sa account allows anonymous access using a priviledged, shared account. So we are trying to address the problem either by making the sa account inaccessible, or monitoring its usage. We have identified three solutions.

    1. Turn on All auditing and monitor for someone logging in as sa, and then try to match the login with someone’s windows account.
    2. Put a trace on every production server and monitor for someone logging in a as sa, and then try to match the login with someone’s windows account.
    3. Randomize the sa password daily, and modify the system stored procedure sp_password to record whose account attemted to change the sa password. Also monitor for the use of sp_configure to set Allow Updates to 1.
      However, these three solutions have serious drawbacks:
    4. auditing adds performance load to the system and it renders the errlog and the application eventlog useless because of all the noise. Also, you still don’t get any information about which windows account actually logged in as sa.
    5. tracing adds performance load to the system, especially on very busy systems where the load increases exponentially as the system becomes busier. Also, you still don’t get any information about which windows account actually logged in as sa.
    6. Modifying system stored procedures is not recommended by Microsoft PSS and could possibly render our support agreements with them void. The same goes for removing the sa account entirely.
      Microsoft recommends going to Windows Only authentication mode. Great. We’d love to, but we aren’t developers. We can’t just flip a switch and then let the application support people sort out the mess. Ultimately, this is a difficulty that Microsoft created by having an sa account that had no controls on it at all. When I’ve asked MS what they are doing about this, they are barely aware of something called SOX. They’ve been useless.
      With the number of servers we support, it would be impossible to operate effectively without DBAs being sysadmins. Impossible. We have good separation between developers, change management approvers, and DBAs. We have good processes and procedures, but we have been operating in the modern environment of competition with outsourcers and co-sourcers. If we can’t operate efficiently, we may not be a support group worth keeping internal.
      I would most appreciate somebody sharing a real solution to this problem of SOX and the SQL Server sa account. Remember, we support a large enterprise of servers. We have to automate everything to be effective and efficient. Half-baked solutions won’t work.
      Thanks


    1. ODBC access to SQL Server is limited to key individuals in Accounting Group. Such access is authorized by the management and is controlled as ‘Read Only’. SA account is not used to establsih DNS connectiions. May I ask how that is done?
      Perhaps it is not important to know how that is done. I have been researching security in the context of controlling access to accounting databases. Apparently it (the research requested by someone else) was preliminary analysis to determine what might be necessary for Sarbanes Oxley compliance. When it was suggested that ODBC connections to the SQL Server database not be allowed, I thought that is like locking the front door and leaving the side door unlocked.
      I am not a DBA such as JimTuck is, and since JimTuck did not comment on this, perhaps it is useful to do what you say, but there are many ways to ad-hoc access SQL Server. As far as I know, they all essentially require a SQL Server sign-on and it is my understanding that that is the most reliable way to control access. If the actual database files are only accessible to SQL Server (the default access) and necessary utilities then that essentially removes most possibilities for access to the data that bypasses SQL Server.
    2. System Administrator ensures that the latest OS and SQL Server Service Packs/Hot-Fixes are applied on the Production Server I think that the Microsoft Baseline Security Analyzer will do that for free. I am not sure but probably there is other software that can be purchased from Microsoft that does more, but the MBSA must be used for production Windows systems if there is nothing better in use. The MBSA can be downloaded from:
      microsoft.com/downloads/details.aspx?FamilyID=4b4aba06-b5f9-4dad-be9d-7b51ec2e5ac9
    3. Access to the critical stored procedures and extended stored procedures I read a lot about that too in my research. I don’t remember for sure but I think there is more information available on this subject so if someone is interested I will refer to my notes. For the moment, I want to make it through the remainder of the threads in this forum.


  • As the SOX 404 standards are written generically to incorporate best practices and optimum security, you won’t specific vendor recommendations published as part of the SOX standards themselves. However, vendor web sites often share best practices and guidelines related to helping their customers meet SOX compliancy.
    The following are also a few links that might help with SQL-Server 2000 security controls, as it may require some strengthening of security controls to ensure the appropriate ‘autonomy levels’ and ‘separation of duties’ within critical financial applications utilizing this technology.
    Please add ‘www’ and paste into your browser, as direct hyperlinking is discouraged in the forums.
    Microsoft SQL Server Home Page
    microsoft.com/technet/prodtechnol/sql/default.mspx
    SQL-Server 2000 Security Checklist
    microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx
    SQL Server 2000 SP3 Security Features and Best Practices
    microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx
    SQL Server 2005 (new version) Security Controls
    microsoft.com/technet/prodtechnol/sql/2005/library/security.mspx
    Please cut/paste this URL into browser ‘as is’ and without the www prefix … There are over 200 references, however many of these may not be applicable.
    Microsoft Site Search - SQL Server and SOX compliance
    search.microsoft.com/results.aspx?q=SQL-Server SOX compliance



  • The ’ Lockdown Script ’ at:
    sqlsecurity.com/Tools/LockdownScript/tabid/64/Default.aspx
    is a free tool that appears useful. The web site (sqlsecurity.com) has more useful tools (available for purchase), but I don’t know how they compare to equivalent tools. I found that script and the site when I researched security; I have no other connection to the site other than that.



  • A few companies are trying to address access control for SOX-related concerns. Some are more on the reporting side such as Idera and others are more on the proactive side such as PynLogic and AppRadar. %0AThere are ways to tie back an sa account with an actual user. I heard through the grapevine that one of these companies (forgot which one) is developing a solution that will require the use of two-factor authentication (CRYPTOCard/RSA tokens) with specific accounts, such as ‘sa’. So now you can pretty much have 100% accountability of who is using a shared/management account.%0AThis capability should solve the ‘who really logged with the sa account?’ question. In addition more vendors are now enforcing variable restrictions based on location/timeframe. So is becomes possible to implement variable rules based on the location of the user.


Log in to reply