Skip to content

How to audit login to my SQL Server (both failed and Successful)?

February 25, 2013

Solution: One day my manager called me and asked me to get him details of the people who logged into one of instances a week back between 4-5 PM. I knew this day was coming and thank fully I already setup login auditing on all our SQL Server instances. It always a good practice as getting login details real time is easy but what if you want to know if you are investigating an issue happened earlier and need to have list of all login attempts during a particular time. In this article I will go over some of the quick setup that you can do to enable login auditing.

Method 1: Using SQL Server management Studio: Connect instance in Management Studio → Right Click and select  server Properties → Security → Under Login Auditing select both failed and Successful logins and click OK.  This will audit all the login attempts in Error log.

Note: An important point to be considered is, for this change to come into effect you need to restart your SQL Server Service.  So plan your downtime accordingly.

If you want to search for a particular login attempts, you can always search it in error log. If you can closely look at below screenshot I am only searching for login attempts for User ‘AuditLoginTest1’ so are the results. You can even define timeline or a specific computer name from where login request should be coming. SQL Server is awesome 🙂

Screenshot:

AuditingLogin1

You can also read the error log file in Management Studio by using below command.

xp_readerrorlog 0,1,”AuditLoginTest1″ – Replace AuditLoginTest1 with your login name you want to search.

0 – Current Error log.

1 – Error log (Use 2,3… )

<AuditLoginTest1> – text you want to search.

Screenshot:

AuditingLogin2

Method 2: Using SQL Server Trace: You can setup a trace from same server or a different server to audit all the login attempts to SQL Server and save it safely in a file.  You can open Profiler from SQL Server management Studio → Tools → SQL Server Profiler or you can login to server  All programs → SQL Server → Performance Tools → SQL Server Profiler. Connect to correct SQL Server instance → Change trace file name → Select save to file (You can save it as SQL table by selecting save to table option, provide appropriate database name and Table name) → Select location where you want to put your trace file → Go to tab Event Selection at the top → Check box Show all events → Choose “Audit Login” and “Audit Login Failed” (Refer screenshot below) → Once done click on run.

  • Note: To setup this monitoring you don’t have to restart SQL Server Services.
  • If you have logged into server to start the trace make sure you do not logoff or the trace will be stopped. Keep your login as an active login to server.

Screenshot:

AuditingLogin3

AuditingLogin4

Method 3: Using SQL Server Auditing: With SQL Server 2008 and subsequent versions you can enable SQL Server auditing to audit login attempts. It’s easy to setup and gives accurate results.

Step 1: Connect instance in management studio and under → Security Dropdown → Select Audits → Right Click and select new Audit… → provide Audit name and select location where Audit files will be saved → Click Ok to create Audit.

AuditingLogin5

Step 2: Now right click newly created Audit and Select enable → After that Right Click on Server Audit Specification → Select new Server Audit Specification → Provide appropriate name and → from Audit drop down select LoginAudit (remember we just created it in previous step) and Audit Auction types as “Failed Login Group” and “Successful_Login_Group” and click ok → Right Click on new created group and select enable. DONE !

AuditingLogin6

Now right click Login Audit enable in Step 1 and select view Audit logs. Bingo! All the login attempts along with server name and user Id is there.

Screenshot:

AuditingLogin7

Important Points:

  • Using above steps you can easily keep track of all the users logging into your SQL Server.
  • One more important is you can check if a user is complaining that he cannot log into the database, you can easily check the logs to see what is the issue.
  • If the users is coming from an application and intern application is connecting to the database using a different login then it would not be possible to know which user connected to database.
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: