Enable Common Criteria Compliance?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Sarbanes-Oxley » Enable Common Criteria Compliance?

Enable Common Criteria Compliance?

Rate Topic

Display Mode

Topic Options

Author

Message

Vivien Xing

Posted Monday, July 14, 2008 8:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:19 AM
Points: 1,279, Visits: 2,191

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.

Post #533557

alen teplitsky

Posted Tuesday, March 24, 2009 7:13 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 1,408, Visits: 4,505

it enables some columns on the sessions or connections dmv, forget which one. MS had a nice script for SQL 2005 SP1 but haven't seen anything for SP2 yet

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #683001

ganci.mark

Posted Wednesday, October 14, 2009 9:06 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:55 AM
Points: 581, Visits: 422

It looks like SP2 32 bit is out for this but I cannot find sp3 x64.

Did you ever find what you were looking for and how did it work out for you?

Thanks
Mark

Post #802828

ganci.mark

Posted Wednesday, October 14, 2009 9:07 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:55 AM
Points: 581, Visits: 422

It looks like SP2 32 bit is out for this but I cannot find sp3 x64.

Did you ever find what you were looking for and how did it work out for you?

Thanks
Mark

Post #802831

alen teplitsky

Posted Wednesday, October 14, 2009 9:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 1,408, Visits: 4,505

it's a PITA but you have to check each login failure with event ID 18456 in the app log on the server. what sucks is that the view may return 10 failures when in reality it's just one login failure

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #802843

ganci.mark

Posted Wednesday, October 14, 2009 9:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:55 AM
Points: 581, Visits: 422

My problem is I need to find successful logins and more specifically last login date time.
Last login date time is available in sys.dm_exec_sessions but is not recorded unless
Common Criteria is enabled.

I am thinking of using logon triggers instead but that either way I think its way to complicated for something that should be recorde by default.(last login date/time)

Thanks
Mark

Post #802861

alen teplitsky

Posted Wednesday, October 14, 2009 9:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 1,408, Visits: 4,505

i run this on a few servers and then there is a job to transfer it to a central server and truncate the data on the source.

insert admindb..tblsupersysprocesses ( session_id, login_time, connect_time, host_name, client_net_address, program_name, login_name,
protocol_version, auth_scheme, last_read, last_write, cpu_time, memory_usage,
last_request_start_time, last_request_end_time, reads, writes)
select
a.session_id, a.login_time, b.connect_time, a.host_name, b.client_net_address, a.program_name, a.login_name,
b.protocol_version, b.auth_scheme, b.last_read, b.last_write, a.cpu_time, a.memory_usage,
a.last_request_start_time, a.last_request_end_time, a.reads, a.writes
--into admindb..tblsupersysprocesses
from sys.dm_exec_sessions as a, sys.dm_exec_connections as b
--cross apply sys.dm_exec_sql_text(b.most_recent_sql_handle) as SQL_Query
where a.session_id = b.session_id

update admindb.. tblsupersysprocesses
set date = getdate() where date is null

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #802885

ganci.mark

Posted Wednesday, October 14, 2009 10:17 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:55 AM
Points: 581, Visits: 422

Good Idea. Keep tract of realtime dates/times from Sysprocesses.
Less obtrusive and less complicated then other options.
Probably safer too.

Thank you for the idea.

Thanks
Mark G.

Post #802913

Vivien Xing

Posted Wednesday, October 14, 2009 2:09 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:19 AM
Points: 1,279, Visits: 2,191

For tracking login, SQL Serer 2008 new feature “SQL Audit” does better job than trigger/profiler/3rd party or home-grown tools as SQL Audit is native to SQL Server, while the others are add-ons to SQL Server.

It works on SQL2008 version only.

Post #803080

Halcyon

Posted Friday, August 06, 2010 9:47 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, July 19, 2012 8:47 AM
Points: 260, Visits: 366

it is only with the Enterprise edition which cost 15k per processor if I am not mistaken?

Post #965279

« Prev Topic | Next Topic »

Permissions