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


SQL Server Audit


SQL Server Audit

Author
Message
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223704 Visits: 42006
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Animal Magic
Animal Magic
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3786 Visits: 13752
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
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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.
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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.
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75725 Visits: 40993
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.
sp_AddMyLogonTrace.txt

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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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,
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75725 Visits: 40993
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
raj_melvin
raj_melvin
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 21
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
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