Need advice on SOX compliant policy for access to generic admin accounts.
-
I work in an IT Dept. which has a number of Windows and SQL Server admins who need access to various generic admin accounts which cannot be replaced by named admin accounts.
e.g MS Clustering requires the pre-defined clustering admin account, the SQL Server sys. admin. account "sa" is required to run some functions and replacing this with a named admin account which should have the same access rights as "sa" just does not work sometimes, etc.
We have an upcoming SOX audit and there is talk of limiting access to the passwords for these accounts to one individual per account. e.g only one person would know the SQL Server "sa" password.
IMO this is operationally dangerous, as there will always be situations where access is needed to a specific account there and then to deal with an immediate emergency. And as anyone who has worked in IT for more than 10 minutes realises, these emergency situations occur all the time.
I cannot believe that there is not a SOX compliant procedure to allow multiple individuals controlled access to generic admin account passwords.
How have others dealt with this very obvious requirement?
I come from a DBA background and I'm just getting up to speed on SOX, so excuse me if there is an obvious solution to this.
If there is, I'm all ears!
Cheers.
Tom.
-
SOX is about seperation of duties and the creation of procedures to monitor and control same. Once you have a procedure in place to cover the 'viewing/usage' of an 'SA' type password, and that procedure is documented and repeatable, then you are covered. One way to get around your immediate issue....is to put the password in an sealed envelope and to maintain a register of when it was created/opened. If the 'SA' holder is not available, then the register is updated + countersigned, the envelope opened, password used, problem solved, password changed, new envelope created, register updated + countersigned. Being SOX compliant will be a matter of showing your SOX auditors the register, the current sealed envelope and the process of opening/using/creating a new version.
An IT solution is not always available within SOX. SOX in fact is not an IT specific process. Once a clear seperation of duties process exists like above, then you should be SOX compliant.
-
Our IT department is broken up into three groups, Development, Network and SQL Admin.
The Development Group has no “sa” access to the production servers at all. If a change needs to be made, change control goes into effect. The proper paperwork must be filled out explaining what you are trying to accomplish and why. The change goes to the SQL DBA who uses an assigned login/password for the group to accomplish this.
We use 5 logins/passwords to do various SQL tasks, one for each level of administration, with only one sa account. We also adopted a tool that will alert us to any changes on the DB’s/Network. We opted against building an in house product for fear of whoever designed it would be gone some day and no one would support it.
In essence SOX gives you guidelines to follow, how you work with in those guidelines is up to you.
HTH
Marty
-
"SQL Server sys. admin. account "sa" is required to run some functions and replacing this with a named admin account which should have the same access rights as "sa" just does not work sometimes, etc."
Auditors, let alone SOX auditors, will not be happy that a non-auditable superuser account is in use at all.
Can you really justify it's use in the cases you alude to? If recoding is required, then that's what must be done.
"I cannot believe that there is not a SOX compliant procedure to allow multiple individuals controlled access to generic admin account passwords."
IMO it's not necessarily individuals, but control of access to high level accounts. I.e. Support staff apply for it, Security staff authorise it, this gives dual-control.
-
I really like the envelope method.
Assuming that the envelop is not kept under lock and key. If I want to make an unauthorized change to the production db I just rip open the envelop, make my db change, change the password, put it in a new envelop and viola! Since the password is only written inside the envelop there should be no audit trail that it has been changed. In fact I wouldn't even need to change it I would just place it back in a new envelope. Perhaps a wax seal with the directors signet ring...
Of course if the envelope was locked in a safe, well then I would need a collaborator.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
"SQL Server sys. admin. account "sa" is required to run some functions and replacing this with a named admin account which should have the same access rights as "sa" just does not work sometimes, etc."
Auditors, let alone SOX auditors, will not be happy that a non-auditable superuser account is in use at all.
Someone should probably mention this to the "Great Plains" developers then!?
-
I work in a 15 person DBA shop that supports 425+ SQL servers, one-third of which are production. All 15 DBAs are in a global group which is a member of the sysadmin fixed server role 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 an imminent 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:
1) 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.
2) 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.
3) 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
-
To get around the issue of shared accounts we have begun using a product called "Unlock Administrator" http://www.e-motional.com/ULAdmin.htm
Once the system is logged into using a generic username and password it is locked
in the standard Windows fashion and the system is set to lock when the screensaver is activated as well.
This program allows you to select which users are able to unlock the system using their own Windows domain credentials. A log of when the system is locked and when and by whom it is unlocked is kept in a protected file as well as a Windows
Event. Users don't have read or write access to this file. This way we have complete knowledge of who used the account and when. Everyone uses their own password and no password needs to be shared.
Hope someone else finds this useful as well.
mArtY
-
James - auditing logins for the sa account only should not have any visible impact to your SQL Server performance. If you have a couple of SQL Servers to audit then SQL Trace would do just fine. If not an audit software would work just as well. But depending on how secure you can make the SQL Server machine, it may not even be needed (see below).
Regarding access to the 'sa' account I would make sure no one would be able to use it outside of the SQL Server box itself. Instead you could have 15 sa proxy accounts (saJohn, saSally...) each with its own password - one for each of your DBAs that map to the 'sa' account. Now you would know which DBA is using the 'sa' credential without giving the DBAs the actual 'sa' password. You can do this really easily with a SQL Server proxy - there is one free on the pynlogic website.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
I would add that giving sysadmin access on a production server to anyone's everyday ID is just plain stupid. Set up ID's that are specifically for privileged access. That makes it easy to track what each ID did, and prevents the issue of accidentally running a truncate script meant for a test box against a production server.
-
SOX compliance....
Here's what you need to do. Be warned, it is TEDIOUS and work intensive.
First, list out every account you have in SQL Server with that account's permissions and db access. (this includes roles attached to each account and both Windows & SQL Only accounts).
Secondly, list out every person who has the password to the SQL Only accounts.
Thirdly, get a solid business reason why all these people have the pwds to the SQL Only accounts.
Fourthly, get together with the people who make the security decisions and write down a solid policy of how these passwords are passed out, by whom, the acceptible reasons for giving people access to these accounts, the reasons for account access "rejection", how you are tracking who has account access and how you deal with the account access when someone changes jobs / teams or leaves the company.
Lastly, make sure the document is accessible (in both electronic and paper format) to everyone who makes the security decisions and that they have all read it and are aware of the policies.
CONGRATULATIONS! You are now SOX compliant!
Yes, it really is that easy. @=) You don't have to change a thing unless you have holes in your security and cannot prove these people have a solid business reason for having access.
Now, as a DBA, your situation makes me majorly paranoid, waiting to change the SA password and revoke access to everyone's accounts. But from a SOX Compliant POV, really all you have to do is plug the holes and document the process.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply