Who’s been logging into my SQL Server?

Published On: 2009-06-11By:

Knowing who has been logging into your SQL Server is one of the key things to know about your SQL Server.  It lets you know quite a bit about who’s using your server, and about who’s been trying to break into your SQL Server, and most importantly if they have succeeded.

Obviously you can setup a SQL Profiler trace to capture this information but that requires the overhead of running SQL Profiler, and who wants that.

All version of SQL Server (from 2000 and up at least) provide some level of logging about who has tried to log into the SQL Server.  Within Enterprise Manager or SQL Server Management Studio’s Object Explorer right click on the Server and select properties (if using Enterprise Manger select properties not connection properties).

Select the Security Tab and find the Login Auditing section.  By default SQL Server only logs the failed logons which is good as it tells you who hasn’t been able to log into the server.  However it doesn’t tell you it they have been successful which is why you may want to change this to both failed and successful logins.

Now changing this setting has an upside and a downside.  The upside is that you know who has been successfully broken into your database using a brute force attack and when.  The downside is that every client that successfully connects to the SQL Server will also log an entry, making it very hard to find the correct entry you are looking for.

Where do these entries get logged to you ask?  That’s the other downside.  They get logged to the SQL Server ERRORLOG file and the Windows Security log file.  Which means that these files will fill up fast.  And if you have a large enough client base logging into the database VERY FAST.

In a perfect world, I’d set this screen to both failed and successful logins.  In reality failed is probably all I can do.

SQL Server 2000 didn’t provide a whole lot of information about what is happening as it only says that Login n has tried to connect and failed.  Not exactly helpful as you don’t know who was trying to login to the SQL Server using the sa account over and over again.  SQL Server 2005 and up include a little piece of helpful information, the IP Address of the person who tried to connect to the SQL Server.  This will help tell you who is connecting to the SQL Server so that you can smack them around.

Denny


Contact the Author | Contact DCAC

3 responses to “Who’s been logging into my SQL Server?”

  1. Cosmictrickster says:

    Or you could do what I have done: create a stored procedure that runs on SQL startup which creates a server-side trace. In my case, it logs to a file, but you could log to a table as well. In that trace, I filter out noise like service accounts (ever seen how many logins MOSS makes?). It’s not a complete record of logins, but it’s easier to track things if you filter out the noise. Besides, if somebody was using a service account to access the server, their accesses would get lost in the noise and I would say you have a little more to worry about – someone got the password to one of the service accounts! You either have poor security or somebody has some elevated rights they probably shouldn’t have.

  2. DavidHay says:

    I just set up a job and trace for all logins and failed logins, the way cosmictrickster said with a few exceptions. It dumps hourly to disk, then loads summary into a table. I don’t filter anything, I can do that on the way out. I can then catch developers who are using admin accounts based on the host name in the trace. I then set SSRS to email a daily login report for my main production servers each morning.

  3. j2ndes says:

    Hello @davidHay . Do you have the tutorial on how to do this including the script? Thank you.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via