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


Enable Common Criteria Compliance?


Enable Common Criteria Compliance?

Author
Message
Vivien Xing
Vivien Xing
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 Visits: 2204
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.
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 4672
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
ganci.mark
ganci.mark
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 546
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
ganci.mark
ganci.mark
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 546
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
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 4672
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
ganci.mark
ganci.mark
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 546
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
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 4672
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
ganci.mark
ganci.mark
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 546
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.
Vivien Xing
Vivien Xing
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 Visits: 2204
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.
Halcyon
Halcyon
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 368
it is only with the Enterprise edition which cost 15k per processor if I am not mistaken?
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