How to configure IIS 7.0 for ODBC logging?
If you select Log File format as “Custom” in the IIS manager, it doesn’t give you options to configure ODBC logging in the UI. Instead, it just gives you an alert saying it cannot be configured through IIS manager which you already know.
But, in the previous versions of IIS, you would see the below:
So, in this post I will explain how to configure IIS7.0 site for ODBC logging. You still want to check out this KB for the database, table related information which needs to be created prior to this IIS configuration change.
Changing LogFile Format and the customLogPluginClsid
To configure ODBC, you might need to know the log plugin ID for the ODBC logging. In IIS 6.0, it was LogModuleId, and if you do a search for “ODBC logging” in your Metabase.XML file, you might find this property with value “{FF16065B-DE82-11CF-BC0A-00AA006111E0}”. We are going to use the same in IIS 7.0, but in the ApplicationHost.config file as customLogPluginClsid.
You need to find the <logFile> node in ApplicationHost.config, and that should look like below:
<logFile customLogPluginClsid="{FF16065B-DE82-11CF-BC0A-00AA006111E0}" logFormat="Custom" />
Below are the AppCmds to do this:
appcmd set site /site.name:"Default Web Site" /logFile.customLogPluginClsid:"{FF16065B-DE82-11CF-BC0A-00AA006111E0}"
appcmd site set /site.name:"Default Web Site" /logFile.logFormat:"Custom"
Now, we have just configured IIS to use ODBC logging for our default website. We still need to configure the required DSN name, table-name, username and password to do the ODBC logging.
Configuring ODBC logging parameters in ApplicationHost.config
After you’ve followed this KB article to create database, table, and DSN, you need to make sure you configure ApplicationHost.config to contain the information. You need to configure those settings in <odbcLogging> node under <system.webServer>. Below is my sample configuration:
<location path="Default Web Site">
<system.webServer>
<odbcLogging dataSource="ODBCLogging" tableName="HTTPLog" userName="Username" password="mypassword” />"
</system.webServer>
</location>
Below are the AppCmds to configure the above attributes for the site:
appcmd set config "Default Web Site" /section:odbcLogging /dataSource:"ODBCLogging" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /tableName:"ODBCLogTable" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /userName:"Username" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /password: "mypassword" /commit:appHost
Also, we do not support configuring ODBC logging feature in IIS using the SQL Native Client ODBC driver. You must use the SQL Server ODBC driver. You might want to take a look at this KB article on this.
Hope this helps! Do post a comment if you have any questions on this.