March 14, 2008 at 10:48 am
I've created a trace to track failed login attempts, however I get the error: 'failed to save trace data to table', and then sql server profiler stops.
What is the issue?
March 14, 2008 at 11:04 am
Is the server you're writing to running? Are the permissions of the account running profiler sufficient?
It's generally recommended to run server-side traces to file, not to table, and import the files later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2008 at 11:04 am
we monitor this in the sql errorlog by setting the Auditlevel
print 'adjust Auditlevel - Failure';
if charindex('\',@@servername ,0) > 0
begin
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2
end
else
begin
EXEC xp_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 14, 2008 at 11:15 am
SSCrazy,
Thanks, but where exactly do I put this code snippet?
March 17, 2008 at 12:49 am
most scripts at SSC need to be executed in a query panel or using sqlcmd unless mentioned otherwise.
So in Sqlserver Management Studio, open a query panel to the desired sqlserver instance and execute the code.
(for this code you'll need sysadmin privileges)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2008 at 8:19 am
Thanks. So I run this query every time I want to see if there are failed logins? Can't I just go to the error log directly to see this? I guess I am not clear what this is supposed to accomplish, in particular in terms of automating this process.
March 17, 2008 at 9:08 am
No, these settings are active after stop/start of sqlserver.
they report failed logins in the sql errorlog file;
You can read it directly or import it using exec sys.sp_readerrorlog
Because that proc needs elevated authorities, we've provided a proc that imports it and have that scheduled in sqlagent.
The table is granted read to public.
/*
* ALZDBA dd 20070605
* Because sp_readerrorlog needs securityadmin authority to be run, we've created this alternative
*/
use Master
go
/*
drop table dbo.T_DBA_SQLServerErrorlog
*/
if object_id('dbo.T_DBA_SQLServerErrorlog') is null
begin
create table T_DBA_SQLServerErrorlog (
RowNumber int identity(1,1) primary key not null,
LogDate datetime not null,
ProcessInfo sysname not null,
[Text] varchar(max),
DtTableRefresh datetime not null default getdate()
)
-- grant select on dbo.T_DBA_SQLServerErrorlog to public
end
go
if object_id('spc_DBA_RefreshErrorlogData') is not null
begin
drop procedure spc_DBA_RefreshErrorlogData
end
go
Create proc spc_DBA_RefreshErrorlogData
as
begin
set nocount on
/*
* clear old data
*/
truncate table dbo.T_DBA_SQLServerErrorlog
-- delete from dbo.T_DBA_SQLServerErrorlog
-- DBCC CHECKIDENT ( 'dbo.T_DBA_SQLServerErrorlog', reseed , 0) WITH NO_INFOMSGS
/*
* load new data
*/
insert into dbo.T_DBA_SQLServerErrorlog (LogDate, ProcessInfo, [Text])
exec sys.sp_readerrorlog
end
GO
if object_id('sp_DBA_ErrorlogData') is not null
begin
drop procedure sp_DBA_ErrorlogData
end
go
Create proc sp_DBA_ErrorlogData
as
begin
select *
from dbo.T_DBA_SQLServerErrorlog
end
go
GRANT EXECUTE ON [dbo].[sp_DBA_ErrorlogData] TO [public]
go
/*
* Schedule job to refresh data hourly
*/
USE [msdb]
GO
/****** Object: Job [DBA_RefreshErrorlogData] Script Date: 06/05/2007 14:02:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 06/05/2007 14:02:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_RefreshErrorlogData',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [spc_DBA_RefreshErrorlogData] Script Date: 06/05/2007 14:02:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'spc_DBA_RefreshErrorlogData',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC dbo.spc_DBA_RefreshErrorlogData',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Dagelijks_Ieder_Kwartier',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070605,
@active_end_date=99991231,
@active_start_time=2,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply