InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:25:48 GMT20http://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxTake a look at this logon event generated by SQL 2008 Audit and then refined by LOGbinder (www.logbinder.com). Does that give you what you need? http://www.ultimatewindowssecurity.com/securitylog/encyclopedia/event.aspx?eventid=24001Tue, 25 Sep 2012 17:26:33 GMTRandall F Smithhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxThanks much for the guidance This is really helpfull I will explore more in this .ThanksWed, 12 Sep 2012 11:54:21 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxIf you need someone with more experience to actually set this up for you, consdier grabbing a consultant or a DBA you know and trust. Believe me, I know anything new can be scary, and it's easier to ignore than to try something new.Like i mentioned in my post quoted here, I have this running on my servers, with a rollover 24-7; I also have a DML trace, capturing every command executed as well; the procedure [b][url=http://msdn.microsoft.com/en-us/library/ms181720.aspx] sp_procoption (linky to Books Online) [/url][/b] is what you use to set a procedure to be executed every time the server restarts.You need to do that, because except for the C2 Audit and the default DDL trace, all traces stop when the server restarts.server side traces have minimal impact, unlike a profiler session.If you really need it, myself or any of the frequent posters here could be hired as a consultant to get you set up ; or you could do a little bit of investigation and get yourself up to speed.[quote][b]Lowell (9/10/2012)[/b][hr]here's a link to a Login trace I like to use for an example; you would need to make the procedure a startup procedure so it restarts when the server restarts. it also creates a view, so i can select from the trace anytime i want.[b][url=http://www.stormrage.com/SQLStuff/sp_AddMyLogonTrace.txt]sp_AddMyLogonTrace.txt[/url][/b][/quote]Wed, 12 Sep 2012 11:28:49 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxThanks .Could you please help me with server side trace,Can we run the trace 24x7? Any sample will be helpfullWed, 12 Sep 2012 09:42:34 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/12/2012)[/b][hr]Thanks for your reply.Trigger - yes it's working solution But the company doen't wanted to use the triggerSQL Profiler - unable to trace it in a table - Getting error "Failed to Save table <tablename> cursor operation conflict"SQL Audit - How to get the Hostname, program name etc...[/quote]No idea about the error in profiler; my suggestion was a server side trace.take a look at that and see if it you can get your arms around that instead.Wed, 12 Sep 2012 09:30:07 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxThanks for your reply.Trigger - yes it's working solution But the company doen't wanted to use the triggerSQL Profiler - unable to trace it in a table - Getting error "Failed to Save table <tablename> cursor operation conflict"SQL Audit - How to get the Hostname, program name etc...Wed, 12 Sep 2012 08:54:35 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/11/2012)[/b][hr]Could any one please help.thanks in advance.[/quote]help with what? you already determined SQL Audit doesn't do what you are asking, right? and I gave you two working examples, one of a trigger, and the other of a trace, which does capture what you are asking.what are you looking for if not those three items?Wed, 12 Sep 2012 06:32:38 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxCould any one please help.thanks in advance.Tue, 11 Sep 2012 21:00:03 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/10/2012)[/b][hr]we are fine to log into a flat filebut I am unable to capture the required informations like " Hostname" ,"local ip" and "Application Name" ect.this is for a enterprise application only I am testing this in development and Audit it running with the missing informations.Thanks[/quote]yeah, wrong tool for the right job, I'm thinking;I'm reading up a bit deeper on SQL Audit now, but it looks like some of that information is not currently available to the audit process, but is available form the connection information that you can use in a trigger..Mon, 10 Sep 2012 06:04:05 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxwe are fine to log into a flat filebut I am unable to capture the required informations like " Hostname" ,"local ip" and "Application Name" ect.this is for a enterprise application only I am testing this in development and Audit it running with the missing informations.ThanksMon, 10 Sep 2012 05:58:38 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/10/2012)[/b][hr]Thanks Very Much this is really usefull.Appriciate your help.is there is anyway we can get the same output using the SQL Auditplease help,[/quote]To the best of my knowledge, SQL Audit writes to a file or to a either the Security Log or the Application Log of the operating system, so writing to a table is not an option. Also, is SQL Audit available in Standard edition, or is that an enterprise only system?> Since I'm running the Developer edition, I'm not sure Audit is what you want to use.Mon, 10 Sep 2012 05:52:40 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxThanks Very Much this is really usefull.Appriciate your help.is there is anyway we can get the same output using the SQL Auditplease help,Mon, 10 Sep 2012 05:43:31 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxWhen i try it with the SQL profile using the table getting the below error not sure why?Failed to Save table <tablename> cursor operation conflictMon, 10 Sep 2012 05:41:09 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/10/2012)[/b][hr]Logon trigger - In case any issue with the trigger this will not allow any users to login into the DB.outpout in table is more preferable.[/quote]a well written trigger will not prevent users from logging in, just one that didn't take into consideration things like permissions on the target table.some of the info, like the databasename, might not be available, as there's often no database context at teh login time, only later...it depends ont eh conection properties, i believe.here's a link to a Login trace I like to use for an example; you would need to make the procedure a statrup procedure so it restarts when the server restarts. it also creates a view, so i can select fromt eh trace anytime i want.[b][url=http://www.stormrage.com/SQLStuff/sp_AddMyLogonTrace.txt]sp_AddMyLogonTrace.txt[/url][/b]alternatively, here's a nice login trigger i've slapped togther :note it takes into consdieration the ability to write to the table[code]CREATE TABLE [master].[dbo].[TRACETABLE] ( [EVENTDATE] DATETIME NOT NULL,[DBNAME] NVARCHAR(128) NULL,[CURRENTUSER] NVARCHAR(128) NULL,[HOSTNAME] NVARCHAR(128) NULL,[APPLICATIONNAME] NVARCHAR(128) NULL,[PROCEDURENAME] NVARCHAR(128) NULL,[USERID] SMALLINT NULL,[USERNAME] NVARCHAR(128) NULL,[SUSERID] INT NULL,[SUSERNAME] NVARCHAR(128) NULL,[IS_SERVERADMIN_SYSADMIN] INT NULL,[IS_DB_OWNER] INT NULL,[IS_DDL_ADMIN] INT NULL,[IS_DB_DATAREADER] INT NULL,[ORIGINAL_LOGIN] NVARCHAR(4000) NULL,[NET_TRANSPORT] SQL_VARIANT NULL,[PROTOCOL_TYPE] SQL_VARIANT NULL,[AUTH_SCHEME] SQL_VARIANT NULL,[LOCAL_NET_ADDRESS] SQL_VARIANT NULL,[LOCAL_TCP_PORT] SQL_VARIANT NULL,[CLIENT_NET_ADDRESS] SQL_VARIANT NULL,[PHYSICAL_NET_TRANSPORT] SQL_VARIANT NULL)GOGRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLICGRANT SELECT on [master].[sys].[dm_exec_connections]GOCREATE TRIGGER Logon_Trigger_Track_IPON ALL SERVER FOR LOGONASBEGIN INSERT INTO [master].[dbo].[TRACETABLE] --the auditing snippet below works fine in a --login trigger, --database trigger --or any stored procedure. SELECT getdate() AS EventDate, DB_NAME() AS DBName, CURRENT_USER AS CurrentUser, HOST_NAME() AS HostName, APP_NAME() AS ApplicationName, OBJECT_NAME(@@PROCID) AS ProcedureName, USER_ID() AS Userid, USER_NAME() AS UserName, SUSER_ID() AS sUserid, SUSER_SNAME() AS sUserName, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin], IS_MEMBER('db_owner') AS [Is_DB_owner], IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin], IS_MEMBER('db_datareader') AS [Is_DB_Datareader], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], ConnectionProperty('net_transport') AS 'net_transport', ConnectionProperty('protocol_type') AS 'protocol_type', ConnectionProperty('auth_scheme') AS 'auth_scheme', ConnectionProperty('local_net_address') AS 'local_net_address', ConnectionProperty('local_tcp_port') AS 'local_tcp_port', ConnectionProperty('client_net_address') AS 'client_net_address', ConnectionProperty('physical_net_transport') AS 'physical_net_transport' ENDGOENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER[/code]Mon, 10 Sep 2012 05:34:30 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxYes, i tried with SQL Profiler to record the event.but Unable to login into a tablegetting error " Failed to start the new trace" But I was able to trace it in a flat file without the client and server ip address.I am not sure Tracing using Sqlprofiler is a best solution for every day use, I am planning to run this all the day in the server and planning to pull a report out of it every week for audit review.please help.Mon, 10 Sep 2012 05:30:55 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxLogon trigger - In case any issue with the trigger this will not allow any users to login into the DB.outpout in table is more preferable.Mon, 10 Sep 2012 05:15:09 GMTraj_melvinhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxPretty sure you could use SQLTrace and audit the logon event. record it to a table and then use SSIS to output to whatever file format you like.Although like Jeff i would question why you wouldnt use the logon trigger?Edit: I skim read your post and SQLTrace probably doesnt capture everything your afterMon, 10 Sep 2012 05:01:26 GMTAnimal Magichttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspx[quote][b]raj_melvin (9/9/2012)[/b][hr]I am not willing to use the Logon trigger[/quote]Why not? I'm also curious about why you need it in a flat file.Sun, 09 Sep 2012 22:25:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1356521-1550-1.aspxI have a requirement 1. when a user logon I would need to log the below information in a table. I am not willing to use the Logon trigger Client IP Client Host Name Server IP Server Host Name DB User Name Service Name Database Name Source ProgramAny suggestions. I enabled the SQL Audit but unable to catch all the information.If i get all the the information in a flat file also work. please helpThanksSun, 09 Sep 2012 17:33:51 GMTraj_melvin