September 9, 2012 at 5:33 pm
I 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 Program
Any 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 help
Thanks
September 9, 2012 at 10:25 pm
raj_melvin (9/9/2012)
I am not willing to use the Logon trigger
Why not? I'm also curious about why you need it in a flat file.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2012 at 5:01 am
Pretty 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 after
September 10, 2012 at 5:15 am
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.
September 10, 2012 at 5:30 am
Yes, i tried with SQL Profiler to record the event.but Unable to login into a table
getting 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.
September 10, 2012 at 5:34 am
raj_melvin (9/10/2012)
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.
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.
alternatively, here's a nice login trigger i've slapped togther :note it takes into consdieration the ability to write to the table
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)
GO
GRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections]
GO
CREATE TRIGGER Logon_Trigger_Track_IP
ON ALL SERVER FOR LOGON
AS
BEGIN
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'
END
GO
ENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER
Lowell
September 10, 2012 at 5:41 am
When i try it with the SQL profile using the table
getting the below error not sure why?
Failed to Save table <tablename>
cursor operation conflict
September 10, 2012 at 5:43 am
Thanks Very Much this is really usefull.
Appriciate your help.
is there is anyway we can get the same output using the SQL Audit
please help,
September 10, 2012 at 5:52 am
raj_melvin (9/10/2012)
Thanks Very Much this is really usefull.Appriciate your help.
is there is anyway we can get the same output using the SQL Audit
please help,
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.
Lowell
September 10, 2012 at 5:58 am
we are fine to log into a flat file
but 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
September 10, 2012 at 6:04 am
raj_melvin (9/10/2012)
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
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..
Lowell
September 11, 2012 at 9:00 pm
Could any one please help.thanks in advance.
September 12, 2012 at 6:32 am
raj_melvin (9/11/2012)
Could any one please help.thanks in advance.
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?
Lowell
September 12, 2012 at 8:54 am
Thanks for your reply.
Trigger - yes it's working solution But the company doen't wanted to use the trigger
SQL 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...
September 12, 2012 at 9:30 am
raj_melvin (9/12/2012)
Thanks for your reply.Trigger - yes it's working solution But the company doen't wanted to use the trigger
SQL 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...
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.
Lowell
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply