ERRORLOG

Who’s been logging into my SQL Server?

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

My ERRORLOG shows some spids have an ’s’ after them. What’s going on?

That “s” is new in SQL Server 2005.  It means that the SPID is a system process.  For example my database is having a problem with tempdb running out of space for no reason.  When I look in the log I see that SPID 118s is the offending SPID.  When I watch SPID 118 using sp_who3 I see that the output looks a little strange.  I see a lot of CPU and disk load and that the thread and been logged in for ever using the sa account but with no hostname, host process, or mac address listed.  This is because this is the service broker process which is what runs the activated procedures.  Starting in SQL Server 2005 system processes no longer need to run with SPIDs less than 50.  Because of this, Microsoft has decided to make live easier when trying to identify the system processes by putting the s after the SPID number in the logs.

This same information appears to hold true for SQL Sever 2008 (so far at least).

Denny