How to log ODBC DSN changes


There is often a challenge to determine the last user who changed the ODBC DSN and when these changes were made. It is even more challenging in the environment where multiple users have remote access to the machine. In many cases unauthorized changes could cause a system malfunction or even a crash. In most cases these changes remain undetected for a long time until the problem strikes. Unfortunately there is no built-in history log functionality in ODBC and the chances are that you will never find out why and when the DSN was modified. However, there is a way to avoid costly mistakes when dealing with ODBC DSN in a shared environment. 

This approach will work with any registry key and is not limited to the ODBC Data Sources.

Starting from Windows 2003, Audit policy can be turned on for object access:

clip_image002

The only audit which needs to be used is a “success”. You can also turn on “failure” audit if you have reasons to believe that somebody has tried to tamper with your DSNs:

clip_image004

Start the registry editor and navigate to the ODBC Data Sources registry key:

clip_image006

Right click on it and select permissions:

clip_image008

Click on “Advanced” button and select “Auditing:”

clip_image010

Add “Authenticated Users” (or any other group you want to audit):

clip_image012

Click “OK” and select events you want to monitor:

clip_image014

Click “Apply” button, now all changes made to DSNs by authenticated users will be tracked in the Security Event log.

clip_image016

To verify, that this is actually being done and check which information is being tracked I will modify one of my DSNs:

clip_image018

I modified the DSN name and changed Client Configuration to use the static port. These changes are harmless if SQL Server is using a static port. However, if dynamic ports are used – my DSN might not work next time the SQL server is restarted.

clip_image020

clip_image022

All my DSN manipulations are now recorded in the Security event log. They might not give all details on the changes being made to the DSN, however I will have track on when and who did modifications and this perfectly suits my original goal.

Author : Vlad (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Malcom (MSFT) & Evan(MSFT) , SQL Escalation Services, Microsoft

Comments (0)

Skip to main content