Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Audit Expand / Collapse
Author
Message
Posted Sunday, September 9, 2012 5:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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
Post #1356521
Posted Sunday, September 9, 2012 10:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356550
Posted Monday, September 10, 2012 5:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:31 AM
Points: 999, Visits: 13,473
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
Post #1356653
Posted Monday, September 10, 2012 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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.
Post #1356658
Posted Monday, September 10, 2012 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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.
Post #1356665
Posted Monday, September 10, 2012 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356666
Posted Monday, September 10, 2012 5:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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
Post #1356669
Posted Monday, September 10, 2012 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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,
Post #1356672
Posted Monday, September 10, 2012 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356675
Posted Monday, September 10, 2012 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 2:07 PM
Points: 11, 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
Post #1356677
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse