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:

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:

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

Right click on it and select permissions:

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

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

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

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

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

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.

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