Error: Writing IIS logs to SQL Server database via ODBC

2
votes

I am trying to get the IIS logs to the database. Followed the steps as given in this website http://blog.datacenterfromhell.net/2013/08/how-to-write-iis-logs-to-database-using.html

It doesn't seem to work, got an error in Windows event log.. EventID: 1016 Error: The World Wide Web Publishing Service (WWW Service) did not configure logging for site 1. The data field contains the error number.

Environment: OS - Windows 7 DB - SQL Server 2008 R2 Express edition

Please help if you have encountered same error previously.

Thanks, Naveen

Steps added:

Step 1: Create DB

Create an empty database on any Microsoft SQL server. Make sure that the identity that used for the Application Pool which is serving the Web Site has write access to this database.

Step 2: Create a table using logtemp.sql script

In C:\Windows\System32\inetsrv\ you will find a script called logtemp.sql, use it to create a table in the database you just created.

Step 3: Create System DSN on the web server that points to the

Create a System DSN on the web server that points to the DB created in step 1. I would recommend to use the identity of the IIS Application Pool to configure the ODBC connection, since this account will later also write the log data to the database. Let's call our DSN IISLoggingDSN. In this post I explained how to create a System DSN.

Step 4: List ODBCLogging settings

Open elevated cmd and change directory to C:\Windows\System32\inetsrv> To get a list of all ODBCLogging settings configured so far, run this command.

C:\Windows\System32\inetsrv> appcmd list config -section:ODBCLogging

This should be empty.

Step 5: Set the ODBCLogging settings

To setup a ODBCLogging connection, issue the following command:

appcmd set config -section:ODBCLogging -datasource:IISLoggingDSN -tableName:InternetLog -username:DOMAIN\IISLogsUser -password:P@SSwoRD$

Let me explain the switches: -section: SectionName we want to configure

-datasource: the name of the DSN we configured in step 3.

-tableName: name of the table in the database

-username: Active Directory domain account that has permissions to write to the database

-password: password of this user, it will be stored encrypted in the config file

Step 6: Enable ODBCLogging for one web site

To enable the logging for a web site, run this command.

C:\Windows\System32\inetsrv> appcmd set sites "Default Web Site" -logFile.logFormat:Custom -logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}

IMPORTANT: The customLogPluginClsid attribute must be set to "{FF16065B-DE82-11CF-BC0A-00AA006111E0}" This ID I took from the Microsoft documentation. It defines that the custom format is ODBCLogging. Let me declutter these switches as well:

-sites: the site that will write to the logs, I tested with "Default Web Site"

-logFile.logFormat: by configuring Custom we configure that custom format will be used

-logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}: see above

1
iisloggingodbciis-7.5dsn
I cant view the link... so if you can edit your post to list the gist of steps you are carrying out, it would be great. – Muqeet Khan
I'm having the same problem. It seams that class id FF16065B-DE82-11CF-BC0A-00AA006111E0 is not registered in the system. – Oscar

1 Answers

1
votes

Naveen, did you meet all prerequisites?

  • The IIS Web Site which should write its log to a database must be served by an Application Pool that is using an AD Domain account identity to run.
  • The same AD Domain account must have RW access to the DB that will be target for the IIS logs.
  • ODBC AND Custom Logging must be installed on the IIS server

I wrote this blog post some months ago. You may also reach out to me via the comments of the blog.