Tracking SQL connections for one month

Microsoft SQL 2014:
Best way to Track what users are connecting to SQL server for one month period of time.
I know you can use profiler to do this but need it for a month and once I log off the server it disconnects it.

Not I am not an expert with SQL.
jyoung127Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You could use extended events (I think) or a logon trigger.

The logon trigger starts like this:

USE master
GO
CREATE TRIGGER trigger_name
ON ALL SERVER
AFTER LOGON  
AS
SET NOCOUNT ON;
--your logic goes here--
GO --end of trigger

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I also recommend the logon trigger where you can capture the login id and the current date time and store it in a table for later reports. Something like:
CREATE TRIGGER RecordLogon
ON ALL SERVER 
FOR LOGON
AS
BEGIN
     INSERT Login_log  (LogInDateTime, LoginName) 
      VALUES  (GETDATE(), ORIGINAL_LOGIN())
END

Open in new window

Jason clarkDBA FreelancerCommented:
Tracking SQL connections
I would like to suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.
I found a helpful post after a long search on google you should go through this it may help yours. http://dba.stackexchange.com/questions/54494/sql-server-keep-track-of-all-connections-disconnections
jyoung127Author Commented:
Sorry for the delay I will be looking into all of the suggestions earlier next week and report what work for me.
Scott PletcherSenior DBACommented:
Extended events are great to use, they just take a little getting used to.  But they are very powerful and not as much overhead on the system as other methods.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.