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.