asked
How to audit who connects to Server
I need to audit and record who connects to the actual SERVER (not the database instance) and record it in a table in SQL Server . With a LOGON trigger you only gets who connect to the instance but i need to know who connects to the actual server. Is there a way I can import Windows security event viewer log to SQL Server? or are there any other way I can record who connects to the server in SQL Server?
This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
HI Not used [this][1] option before, but it looks like it can do the trick. In the past I have used manageengine as it was a enterprise requirement to audit the all servers. [1]:
http://www.mssqltips.com/sqlservertip/1915/how-to-automatically-monitor-windows-event-log-from-sql-server/
Thank you sp_lock for your answer. This is actually a very useful article but it only works for windows server 2003.
Ok. There is a PoSH option. You can use the [PoSH guru's][1] method to perform this. Create a table to host the values. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[EventViewer]( [Index] [int] NULL, [Time] [datetime] NULL, [EntryType] [varchar](MAX) NULL, [Source] [varchar](MAX) NULL, [InstanceID] [varchar](MAX) NULL, [Message] [varchar](MAX) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO You should be able to then execute the PoSH script (assuming you have access to the server, you have AUDITING turned on and the relevant modules loaded) $ServerEvents = 'SERVER1' $DBServer = 'DBAServer' $DB = 'DBA' $variable = ( Get-EventLog -ComputerName $ServerEvents -LogName Security -After “11-04-2014“ | where eventid -EQ "528" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance $DBServer -Database $DB -TableName EventViewer -Data $valuedatatable I have changed the code that Laerte uses to include only the Successful logon event. It would also be a good idea to read the blog as he also describes now to enumerate multiple servers NOTE: Code is "as is" and no warranty is provided. Please test in a non-production evironment first. [1]:
https://www.simple-talk.com/blogs/2011/08/31/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell/
Thank you for pointing me to the right direction... Posh guru's method and your script helped
Refer to this for auditing for Windows 2008 and later
http://social.technet.microsoft.com/Forums/windowsserver/en-US/216c7ee3-2cf2-4a1a-a1d9-23a79853ef7c/audit-logon-events-for-windows-2008?forum=winservergen
17 People are following this question.
