SQLServerCentral / SQL Server 7,2000 / Sarbanes-Oxley

In SQL 2000, security login inquiry

escobal_lor — Fri, 15 Apr 2011 03:19:08 GMT

We used the trace file and saw an "SA" ID login in SQL2K was failed and the hostname is blank. Is it possible that the "SA" ID in SQL 2000 was able to login and you cannot capture the HostName?

Looking for Security Auditing solution

Jack Henry — Fri, 30 Sep 2005 14:02:00 GMT

I am looking for a security auditing solution. Any feedback on 3rd party vendors that supply these solutions?

Thanks,

Jack

Auditor Knowledge of Database Environment

JunkMail Victim — Fri, 01 Sep 2006 05:13:00 GMT

Now that most companies have gone through at least one round of SOX, I'm wondering what everyone's assessment of the auditor's understanding of the database environment is.

I've found them to concentrate on the compiled executables of the client applications, but not think much about the unencrypted business logic that resides in stored procedures and triggers in the database environment. In our case, they seem to think of databases as only data storage, and don't consider how powerful and immediate the environment really is.

There's probably a mosaic of response depending on what auditor companies have had, but I'm curious what everyone's experience has been.

Thanks.

Enable Common Criteria Compliance?

Vivien Xing — Mon, 14 Jul 2008 08:31:15 GMT

I need to do some research for auditing SOX related applications for SQL2005. Anyone has worked on this option yet? Any input or related links are much appreciated.

sox guidelines for writing software specs

mm-1009269 — Tue, 06 Oct 2009 11:07:08 GMT

Hello!I'm a developer and I write my own requirements and I have read access to production servers.Am I violating SOX guidelines by writing my own requirements and then doing the development? I'm really unsure how to interpret SOX since I'm an IT person but technically, I'm not in the IT department.Can somebody please help me understand?Thank you very much!-Michelle :-)

Audti Trial for a single user

WAGalaxy — Mon, 24 Aug 2009 21:44:47 GMT

HelloFor business requirements I want to create another sysadmin account called "sa2" and give it to the Software Developers/Business Analysts to use so they can perform their duties. What I want to do is implement a audit trail for that user only to capture any update/delete as well as creating databases, indexes, fields etc...pretty much everything for that user. We use SQL 2005 and 2008.Have anyone done this before? Your help/advice is muchly appreciated.ThanksBrandon

Log Reader Software

shew — Tue, 01 Jun 2004 09:33:00 GMT

I’ve been asked to find software that will read the SQL Server log to:

Identify anyone making DDL changes
Identify anyone making data changes (i.e., UPDATE, INSERT, DELETE)

Does such a product exist for Microsoft SQL Server 2000? SQL Profiler "kinda" fits the bill, but it takes a lot of disk space, and you can easily lose data if it crashes or fills up a disk. And it could simply be turned off if someone wanted to "mess with" the data.

TIA,

Jon

Enabling Audit for SQL Server 2005 / PCI Compliance

venkatesh.selvaraju — Mon, 03 Aug 2009 15:05:08 GMT

Hello there,We've got to enable auditing for critical DB servers which stores Card holder data. I've read Brian Kelley's article "SQL Server Auditing - Part 1" and " Auditing with SQL Profiler". What I don't understand is how to use the profiler to enable auditing for a particular table/column which houses the card holder data. Also, the end goal is to move these logs to a centralized log management server (RSA enVision). Can we export the profiler logs to enVision?If someone performs a select/alter/drop query on this table we wish to log the event for analysis. In addition, we wish to log account management events. Any help is greatly be appreciated.Thank you,V

Need to audit changes in permissions

Sharif-217569 — Wed, 15 Mar 2006 09:55:00 GMT

My SOX requirements are that I need to monitor when any changes are made to user privileges - if someone is granted new access, etc. Ideally also when a new user is created.

I have a trace running from SQL Profiler now but that is a pain because everytime the server is rebooted I have to stop the trace, save thefile and start a new trace.

I have to monitor this on 6 different servers.

Does anyone know of a better way to monitor this? Procedures or third part software?

Thanks.

SOX Archiving

ian.bailey — Thu, 02 Oct 2008 10:45:09 GMT

Hi Guys, I've looking for a solution that helps with SOX compliance on SQL in an archiving manner, what do you guys suggest. I've looked at commvault and symantec but there too expensive. CheersIan

SQL DB Server Access Levels for Programmers

jgaitu — Sun, 19 Oct 2008 20:19:40 GMT

My Organisation have a SQL Development Server, Test SQL Server to test Databases and a Production SQL server.I'd like to have programmers to have all the access levels on Development server but certain access level on both Test and Production server. My question, is there a [b]guideline or policy [/b]for Programmers to have certain access to Test/Production servers?

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

Tom Brehony — Thu, 26 May 2005 09:22:00 GMT

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.

Looking for help due to SOX - Removal of local admin from DBAs

SAIC Dba — Tue, 20 Dec 2005 12:46:00 GMT

Thanks to SOX regulations their is a movenement to remove the dba's from the local administrators group on all the servers. Is there a paper or anything to get me started that list out what premissions a DBA would need such as regiestry keys file access and anything else in general?

No DBAs allowed access to Production DB Servers...

Mike Dominick — Wed, 19 May 2004 07:35:00 GMT

Our IT Director has come down and said that in order to meet compliance with Sarbox we will need to implement a new policy the removes DBA access from the production database servers. If we need access to any of the servers we will have to submit a request for a “key” and then a user id and password will be sent to us so that we can access the server.

I do not believe this is true and have been unable to convince the director that this could be an unwise course of action. How are we to monitor and respond to issues if we need to request a “key” for everything from adding/modifying database users, re-running a backup job that failed, monitoring performance, etc. This is going to make doing my job, and the “keepers of key” a real burden. The other thing that scares me is that the only system administrator accounts will be SA, SQL service account and the key account. All which 2 key keepers are responsible for managing the passwords and do not understand SQL Server. The fun part is that we are still responsible for the servers so when the gate keepers to say a service account password change and do it wrong, it will me who has to answer to the exec’s.

Has anyone else experienced this situation? How has it worked out?

I believe the director does not realize that the DBA’s role is more a specialized system administrator job. In our company it’s I am move of a hybrid DBA since I do both the Admin side and work as one of the developers. Any thoughts of how to explain this to the director?

Does anyone have any compelling arguments why this new approach is a bad/good idea.

Thanks

Unable to launch startup trace with regread ....

ALZDBA — Tue, 10 Jul 2007 07:36:00 GMT

Because of SOX we need to launch a modified C2-trace to monitor activities.

To be able to determine where to put the trace file, I've produced this sproc (because of reuse in the sox-backup-system)

Create Procedure sp_DBA_GetSOXTraceFileName@TraceFileName nvarchar(245) OUTPUTas beginset nocount on/*-- test executionDeclare @TraceFileName nvarchar(245) exec sp_DBA_GetSOXTraceFileName @TraceFileName OUTPUTprint @TraceFileName*/SET @TraceFileName = ''/** Get SQLServer Errorlog path*/Create table #tmpRegValues ([Value] varchar(50), [Data] varchar(1000))insert into #tmpRegValues exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'

Select @TraceFileName = substring(Data, 3,datalength(Data) - charindex('\',reverse(Data)) - 2) + '\DBASOX' + '_' + replace(@@servername,'\','_') + '_' + replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') from #tmpRegValues where Data like '-e%' if @@rowcount = 0 raiserror ('DBASOX Unable to read SOX tracefilename !!', 16, 16) with log

DROP TABLE #tmpRegValuesend

Here's the problem :

If this proc is executed within a startup storedprocedure, it always raises the error 'DBASOX Unable to read SOX tracefilename'

Aparently it cannot read the registrykey at startup time

I've also tried to add a waitfor delay '00:00:05' in the actual startup proc (residing both in master !) but that didn't help.

If I execute the proc in QA everything works fine

removing the Xp_regread Allowed Paths also didn't help (KB 887165)

The server is SQL2000 SP4 + kb904659 + kb904660

For the moment I'll reside to storing this filename in an extra table in master, so the startup sproc for the sox-trace can be kept standard for all servers.

Specific Database Compliance Questions - please advise

sql_jr — Mon, 07 May 2007 07:58:00 GMT

OK, so we're going public. Got to put together the plan on managing access to our production servers.

1. First, what do auditors look at when auditing a db environment?

2. More specifically, as a team of infrastructure folks, most of us here have local admin rights on the box which authenticates via BuiltIn\Administrators. Also, our service account is a sysadm. What is the acceptable #of 'sysadmins' on a prod box (or is there any?) Do we remove the BuiltIn\Admin account?

3. With respect to logons, is turning on Logon Failures sufficient to log?

4. public role - what is expected here?

I have more q's, but I'll wait for some reply to the above. TIA!

Where Can a DBA learn More about SOX

Barkingdog — Fri, 19 Aug 2005 11:43:00 GMT

I'm totally new to this stuff. I'm told that the actual SOX "defining document" is thousands of pages and not practical for me to read through. I'm also told that SOX, in practice, is what our auditors tell us what we need to do. (My company is already talking to auditing companies that profess SOX expertise.)

Any idea where a DBA can start to learn about SOX?

TIA,

Bill

P.S. Around our company, our frontend applications control authentication. Often the application talks to the database using a single account, for all users, using the app. We can't begin to trace at the database level who is doing what. I'd bet SOX will have something to say about this approach.

DBGhost

Robert Wilson-203725 — Fri, 14 Oct 2005 15:28:00 GMT

My company is currently in the process of becomgin SOX compliant. We are setting up a development,test,and production server environment. I have downloaded the evaluation version of DBGhost but am not sure exactly where to start. Do you have to be using a version control program to use DBGhost. Any help would be appreciated.

Thank You

Contractor Access to Company Databases

Mark Shepherd — Thu, 21 Dec 2006 00:28:00 GMT

Hi there

I need to get some feedback about DBA Contractor access to our company databases. I am a DBA and from time to time we get DBA contractors in to perform development work on specifc systems. The group that they current get added to gives them access to all systems in the company (we have a lot of systems from payroll to customers etc). I might add that our company is a well known financial institution in our country.

How are other DBA's treating contractors in their company. I have no problem giving them the access they need to perform the task that they are contracted to do, but should they get full access?

How does this fit in with Sarbanes Oxley?

Your feedback would be much appreciated.

Thank you

Database Documentation

Bob Bridges-173967 — Wed, 06 Apr 2005 08:10:00 GMT

Greetings to the group!

I've been told that our Sarbanes-Oxley requirements include documentation of all of our databases.

My thought is to generate the SQL scripts (through Enterprise Manager) for all objects in each database.

Does this sound appropriate? How are others handling this? Some db management tool?

Thanks,

Bob

"Column encryption" software recommendations?

Barkingdog — Fri, 11 Mar 2005 10:48:00 GMT

I am testing software to encrypt columns in sql tables. It's an interesting experience. Typically such software renames the base table containing encrypted columns and creates a VIEW having the name of the original table. The idea is to make the transformation have as little impact as possible at the source code level. I applied one company's package to a test databases (pretyy complex ones) and found out... after much trouble .... that I couldn't de-crypt the very columns I had encryped! Yes, I can encrypt\decrypt Northwind with their product, I am working with the company on this one.

I also found that DTS no longer recognized the table name and that QA, expecting to display text, would sometimes freak out when it hit an encrypted (binary) field.

That's my sob story. Now I'm chechking out the products of Protegrity hoping to have greatr success.

Can you recommed database encryption software for me to test?

TIA,

Bill

Database Activity Monitoring/Auditing

JunkMail Victim — Fri, 01 Sep 2006 05:30:00 GMT

I'm wondering if anyone has tried to set up auditing on databases that are particularly active, and generate a lot of extraneous information in the normal operation of the application. For example, the 'sa' account may be used by the application to create and drop tables, move data around, generating tens of thousands of records in the process. It may also act on behalf of the user, effectively masking who was doing what.

I'm curious what products may have been used, and what kind of filtering you apply. I'm even curious who may have segregated duties to lessen the requirement for monitoring.

Thanks.

How to identify GROUPS in database roles

Prasad Pavirala — Wed, 05 Apr 2006 12:46:00 GMT

I am writing these scripts to prepare report that shows all the users that have dbo rights on a given database.

By using sp_helprolemember 'db_owner' I can get the required list. But some of these members are Local or Domain groups. Under such situations, I have to show all the logins within these groups (iteratively if required). I can do that also by writing a VB script to read from Active directory.

But the main problem I am having here is how to identify if a dbo role member is a group or just a login?

Is there some table/view/stored proc that can give us this info?

Thanks

Prasad

SAS 70 Certification

Eric Peterson — Fri, 17 Mar 2006 10:02:00 GMT

All

Has anyone gone thru a SAS 70 audit and certification.

I am assuming that it is very similar to a SOX audit, but the devil is in the details.

Thanks in advance

Eric

Securing DTS connections for Sarbanes Oxley

cooljdude — Mon, 13 Mar 2006 12:35:00 GMT

Hi,

We have jobs scheduled on several of our Database Servers, which execute DTS packages. The DTS packages have Connections (steps) to more than one Sql Servers (different applications). Because of the Sarbanes Oxley requirements, we are trying to come up with a way where only the primary developer for any given application would know the password for that application/Sqlserver. And because we have integrations between different systems/applications via DTS packages, we want to come up with a scenario where the developer of the DTS package would not need to know the password for the connection step to the other application involved in the DTS. This is what we have come up with so far:In our Development environment, the developers will have access to all the databases on all the sql servers via their NT domain accounts. Thus, they can create the DTS packages with connections to different sql server /databases (Development only) using windows authentication on all connection steps and then test it. When the DTS needs to be deployed, only the primary developer for the production database where the DTS/Job is to be housed, will have access to do so. He/She will copy (save as) the DTS to the production server, and then schedule it as a job. The DTS owner will thus be the primary developer for the application who has sa privileges. If my understanding is correct, when the DTS is executed by the job, if the DTS uses 'Windows authentication' on the connection steps, the credentials associated with the sql server agent log on (from the machine where the job is running), will be used for authentication. Is this correct? If it is, then if we have sql server agents on all our instances to start using a windows domain account that has administrative rights, would this approach work? It seems like the NT domain admin account falls under the BuiltIn/Administrator login. WE have that login under the Sa role.Would this approach work? Is there any downside to using such a mechanism.Moreover, when the credentials from the sql server agent log on are passed to authenticate against any sql database, how does sql server figure out that this log on is part of the builtin/administrator group? Does Active Directory come into play at all?Jaladhi

Resources?

Clive Strong — Fri, 20 Jan 2006 04:29:00 GMT

Hi All,Just changed jobs and the new company are SOX complient. This is all very new to me, so if you have any good resources I can read up on, I'd appreciate it. Also, are there any SOX resources which target DB Servers/Applications/Installs? We run SQL Server & Oracle, so I'd be interested in reading up on both.ThanksClive

Editting DTS packages

John R. Yori — Fri, 24 Jun 2005 12:45:00 GMT

I am in the process of planning for moving a database to a new sql2000 instance. The problem is I found out there is several hundred DTS packages saved as structured storage files (.DTS) that will need to be editted to reflect the new instance name. The only way I know how to do this is to edit each one individually. Does anyone know of any tools or tricks to do this quickly?

Help wanted for an interview...

Jay Bee-249994 — Tue, 07 Feb 2006 19:02:00 GMT

Hi all,

I've got a spec that I'm trying to get a good global picture of, so I can knock their socks off. A lot of it makes sense, some things don't, I'm not too savvy in management-speak (which I've marked '???'), any thoughts?

It's for a sports company interested in the following:

1) Putting in compliant practices across their European region (they have several continental Soccer teams);

2) Capacity Planning (I'm presuming they want future projections of DB and Log growth in terms of both transactions and server resources)

3) Change Reconciliation ???

4) Wintel Segregation of Duties (I presume they're trying to separate NT Admins from SQL Admins, but again, ???)

5) Log Review Process Implementation ????

6) Threat to a particular database arising from ODBC - always been more Admin than Dev, what might be going on here??

Thanks in advance!

A blocking problem

lopeter — Wed, 14 Dec 2005 21:38:00 GMT

In my production environment, I have a stored procedure which was called by an application several time. This stored procedure was used to return a new sequence number for the 'key' passed as an argument. For example, if appliication needs to add a new customer record, this stored procedure was called and returned a new cust_num for the new record. The 'last_sequence_oid' table has the column keyname and the column storing the last sequence number used for the 'keyname'.

Recently, we experienced a blocking problem with the 'last_sequence_oid' table. By examing the sysprocesses table, I found that the blocker was the apps calling the stored procedure. The blocker sysprocesses record has shown that the blocked column was 0, waittime was 0, waittype was 0x000, and the status was 'AWATING FOR COMMAND'.

If I killed the application process, all the login sessions blocked were able to continue.

We could not reproduce the blocking problem in test environment. And blocking problem occasionally occurred in production environment. SQL Profiler trace did not show errors or exceptions.

Can anyone share his experience with me to investigate this problem.

Regards,

-- Peter Lo

Online Resources

K. Brian Kelley — Wed, 18 Feb 2004 15:11:00 GMT

Text of Sarbanes-Oxley Act of 2002:

http://news.findlaw.com/hdocs/docs/gwbush/sarbanesoxley072302.pdf

US Securities and Exchange Commission Resources:

http://www.sec.gov/spotlight/sarbanes-oxley.htm

American Institute of Certified Public Accountants:

http://www.aicpa.org/sarbanes/index-07-09-03.asp

Secure Transaction Logs

Curtis Smith — Wed, 22 Jun 2005 10:44:00 GMT

Due to SOX, i am being asked to do the following:

We purchased LogExplorer to view the transaction logs for auditing. I am being asked to copy the transaction logs to a secure location, so they can be reviewed.

My Problem: I backup the log every 11 minutes and name the file based on the date and hour. During the same hour I append the transaction log backup to the file. What is the best approach for the back, and copy to the secure location. Keeping in mind that the file being copied may be in use. Also, if successfully copied I need to remove the file from by original location.

Montioring using Guardium SQL Guard or other similar products

timingskey — Fri, 05 Nov 2004 14:03:00 GMT

I'm a system DBA for a large company. We have approx 20 SQL Servers at our central computer facility. There are many other SQL Servers throughout the enterprise.

With the advent of SOX, and just overall security awareness, it's been presented to us that we should evaluate Guardium Inc's "SQL Guard" to monitor all SQL Servers in the Enterprise. Has anyone used the product, or something similar? If so, what did you find?

Thanks,

Ben Reeder - Deere and Company SQL Server DBA

Looking for SOX-Security Details

YSLGuru — Thu, 28 Apr 2005 10:25:00 GMT

Does anyone know where I can find details specific to SOX & SQL Server security? I'm trying to determine what SOX says about security of SQL Server if it says anything at all.

Thanks

Looking for SQL open source accounting

Gery D. Dorazio — Sun, 17 Apr 2005 21:53:00 GMT

I am trying to find some open source accounting that can be used in conjunction with the dot NET environment. From what I have researched so far much of the actual controls for accounting systems resides in the database structure. Therefore I am asking this question here in hopes someone may point me in the right direction.

Thanks,Gery

Monitor Server

Martin D. Cymerman — Wed, 13 Apr 2005 13:15:00 GMT

With SOX in place, can a developer atleast "monitor" a questionable server? I have been trying to fix a deadlock issue on a production server for two days with this "Hands off" approach....and may have fixed it. But I need to monitor the server in question.

Auditing DB backend to webserver"

Barkingdog — Fri, 11 Mar 2005 10:37:00 GMT

Users Browse to our site and enter sensitive data in our database backend (of course behind the firewall, etc.) The communication between the webserver and database backend is done via a single, anonymous, account as we don't know the identiy of the user using the Browser. So, from the perspective of SOX what type of auditing is required? (We certainly can't identify who has changed what -- only what has been changed.)

TIA,

Bill

Who Did What?

sqlintern — Wed, 24 Nov 2004 22:41:00 GMT

With the SOX lock downs on our SQL databases, it appears someone had taken "rights" away from a particular login id that imports data via batch. This login was changed and limited in it's access, however, for the last 2.5 years, it did had the correct rights. Once the problem was identified and fixed (restore rights to the login id), now one is "owning" up to making the change. Where do I go to look at the log files to see who made this change originally?

Retrieving next to last row in a recordset

sqlintern — Tue, 04 Jan 2005 14:47:00 GMT

I have a table that contains many records for one topic.

For instance:

Employee PTO_Accr Employ status Effective Date

123456 .91 T 01/31/2005

123456 .91 A 08/01/2004

123456 .91 A 06/15/2004

123456 .80 A 01/15/2002

My stored procedure needs to look at the last effective dated record, however, if the employee is terminated I need to capture the next to last record (in this case, 08/01/2004). I can not use a prior in the same script as a fetch next. Any suggestions would be greatly appreciated. Thank you.

Database Schema Changes & SOX

Darren Fuller — Sun, 23 May 2004 04:02:00 GMT

Having an audit trail & controls over changes to data within a database is a requirement of the SOX Act. But does this also include providing an audit trail of changes to the database schema, reference tables & stored procedure code?

Thanks,

Darren

Audit SQL account usage

Gary Andrade — Fri, 10 Dec 2004 14:56:00 GMT

To comply with SOX requirements we have established Maintenance accounts that are checked out (given a key/password). The account is good for a peroid of time before the password gets changed. I have the requirement to audit the activities of the accounts. What is the easiest way to do this? Profiler does'nt seem like an option because of the ongoing nature and amount of accounts to monitor. Do I need a log reader software and if so which one can track changes by account name?