SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need advice on SOX compliant policy for access to generic admin accounts.


Need advice on SOX compliant policy for access to generic admin accounts.

Author
Message
Tom Brehony
Tom Brehony
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 2
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.
AndrewMurphy
AndrewMurphy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 304

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.





Martin D. Cymerman
Martin D. Cymerman
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 17
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
Joseph Mulhall
Joseph Mulhall
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 42

"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.


chisholmd
chisholmd
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 86
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)
vinny_1973
vinny_1973
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 86

"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!?





James Tucker
James Tucker
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3

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


martygold
martygold
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 6
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
Herve Roggero
Herve Roggero
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 52
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
Ross McMicken
Ross McMicken
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 2250
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search