IIS7 – post #57 – How to setup ODBC Logging in IIS 7.0/7.5

This example shows how-to setup ODBC logging on IIS 7.0. A big thanks goes to Thomas Deml and Anil Ruia for pointing me in the right direction.  There wasn’t an article on how-to setup this feature I could find, so I wanted to share my experience. I couldn’t get IIS Manager to enable the ‘Custom’ the Logfile format, I receive this error.




One of the things Microsoft recommends is not use ODBCLogging on a busy web server. What is a busy web server? That can vary, I’ll leave that up to you to determine. When in doubt, load-test your site. That might be another blog how to load-test your ODBCLogging. When using ODBCLogging, keep in mind the not all the values that the W3C extending logging are captured with ODBC Logging.  Here is a list of values that ODBC Logging captures.  This has not changed since IIS6. Here is an article that contains more information.  http://tinyurl.com/3bd2np


Assumptions.



  • You are running Windows Server 2008 Web, Standard, Enterprise.  I’ve not tested Server Core.

  • SQL Server 2005 is installed either locally or have access to a remote system.

  • A SQL database called IISLogs and Table called InternetLog + an SQL user account that has appropriate permissions.

  • IIS is installed and one website is configured.

Here are the steps.
1) Installed SQL Server 2005 on the web server. (I used express and put SQL Server Management Studio Express).  If you have a remote database, you can use that also
http://www.microsoft.com/sql/editions/express/default.mspx

2) Created a database called “IISLogs”. You can use SQL Server Management Studio or the ‘create database’ code

USE [master]
GO
/****** Object: Database [IISLogs] Script Date: 12/20/2007 19:05:13 ******/
CREATE DATABASE [IISLogs] ON PRIMARY
( NAME = N’IISLogs’, FILENAME = N’c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAIISLogs.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’IISLogs_log’, FILENAME = N’c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAIISLogs_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’IISLogs’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [IISLogs].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [IISLogs] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [IISLogs] SET ANSI_NULLS OFF
GO
ALTER DATABASE [IISLogs] SET ANSI_PADDING OFF
GO
ALTER DATABASE [IISLogs] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [IISLogs] SET ARITHABORT OFF
GO
ALTER DATABASE [IISLogs] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [IISLogs] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [IISLogs] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [IISLogs] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [IISLogs] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [IISLogs] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [IISLogs] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [IISLogs] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [IISLogs] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [IISLogs] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [IISLogs] SET ENABLE_BROKER
GO
ALTER DATABASE [IISLogs] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [IISLogs] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [IISLogs] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [IISLogs] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [IISLogs] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [IISLogs] SET READ_WRITE
GO
ALTER DATABASE [IISLogs] SET RECOVERY SIMPLE
GO
ALTER DATABASE [IISLogs] SET MULTI_USER
GO
ALTER DATABASE [IISLogs] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [IISLogs] SET DB_CHAINING OFF

3) Create a table called InternetLog in your IISLogs database.  This is the default table name outlined in the schema. You can also use the ‘logtemp.sql’ located in %SystemRoot%system32inetsrv.

‘Code to Create the IISLogs database that will house the log entries.
USE [IISLogs]
GO
/****** Object: Table [dbo].[InternetLog] Script Date: 12/20/2007 19:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InternetLog](
[ClientHost] [varchar](255) NULL,
[username] [varchar](255) NULL,
[LogTime] [datetime] NULL,
[service] [varchar](255) NULL,
[machine] [varchar](255) NULL,
[serverip] [varchar](50) NULL,
[processingtime] [int] NULL,
[bytesrecvd] [int] NULL,
[bytessent] [int] NULL,
[servicestatus] [int] NULL,
[win32status] [int] NULL,
[operation] [varchar](255) NULL,
[target] [varchar](255) NULL,
[parameters] [varchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

3) Create a user called IISLogsUser with a password of P@SSwoRD$.  This can be anything, but this is what used for the example. Grant the user appropriate SQL permissions.

Setup the DSN called InternetDB

Create a System DSN (data source name)
a. On your web server computer, open Control Panel, go to Administrative Tools, double-click the Data Sources (ODBC) data source, click the System DSN tab, and then click Add.
b. When the Create New Data Source window appears, click to select SQL Server, and then click Finish.
c. In the Name box, type InternetDB, type a description, click to select the SQL server that you want to connect to, and then click Next. If the SQL server is on the same computer, select (local).
d. In the creation wizard, make sure that you click to select With Windows NT authentication using the network login ID for the computer that is running SQL Server. Examine the client configuration, and use the default Named Pipe TCP/IP setting. Make sure that the SQL server name is correct, and then click OK.
e. Click Next.
f. Map the default database to the database where InternetLog table resides, and then click Next.
g. If you want to, you can click to select Save long running queries to the log file and Log ODBC driver statistics to the log file in the wizard.
h. Click Finish.
i. At the end of the wizard, click Test Data Source. Make sure that you have successfully connected to the computer that is running SQL Server, and then click OK to exit.

‘The schema file is in %SystemRoot%System32inetsrvconfigschemaIIS_schema.xml
‘Note the password is encrypted.  When you display using appcmd, the password is decrypted.

<sectionSchema name=”system.webServer/odbcLogging”>
    <attribute name=”dataSource” type=”string” caseSensitive=”true” defaultValue=”InternetDb” />
    <attribute name=”tableName” type=”string” caseSensitive=”true” defaultValue=”InternetLog” />
    <attribute name=”userName” type=”string” defaultValue=”InternetAdmin” />
    <attribute name=”password” type=”string” caseSensitive=”true” encrypted=”true” defaultValue=”[enc:AesProvider::enc]” />
</sectionSchema>

5) List the ODBCLogging config using AppCMD, the values should be blank

‘Here is the syntax

appcmd list config -section:ODBCLogging

‘Here is the initial result
<system.webServer>
    <odbcLogging />
</system.webServer>

6) Here are the properties you can set on the ODBCLogging section.

‘Here is the syntax
appcmd set config -section:ODBCLogging /?

‘Here is the result
ERROR ( message:-dataSource
-tableName
-userName
-password
)

7) Set your website to use ODBCLogging.  You’ll need the DSN, table name, username and password.

‘Here is the syntax

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

‘Here is the result
Applied configuration changes to section “system.webServer/odbcLogging” for “MACHINE/WEBROOT/APPHOST” at configuration commit path “MACHINE/WEBROOT/APPHOST”

8) List the config to ensure the values were set. You can also open IIS 7.0 manager and see ‘Custom’ logFile format has been selected.

‘Here is the syntax
appcmd list config -section:ODBCLogging

‘Here is the result.   If you look in applicationHost.config, the password is encrypted, AppCMD decrypts it.
<system.webServer>
    <odbcLogging dataSource=”InternetDB” tableName=”InternetLog” userName=”IISLogsUser” password=”P@SSwoRD$” />
</system.webServer>

9) Enable your particular website to use ODBCLogging.

‘Syntax I used, note you have to use the customLogPluginClsid listed in the example with the brackets around it {}
appcmd set sites “Default Web Site” -logFile.logFormat:Custom -logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}

‘Here is the result
SITE object “Default Web Site” changed

‘Here is what should be listed in the applicationHost.config
‘Sites
<sites>
    <site name=”Default Web Site” id=”1″>
          <application path=”/” applicationPool=”Default Web Site”>
   
            <virtualDirectory path=”/” physicalPath=”c:inetpubwwwroot” />
           </application>
   
    <bindings>
            <binding protocol=”http” bindingInformation=”*:80:” />
            <binding protocol=”ftp” bindingInformation=”*:21:ftptest.aspdot.net” />
        </bindings>
        <logFile customLogPluginClsid=”{FF16065B-DE82-11CF-BC0A-00AA006111E0}” logFormat=”Custom” />
   
</site>


9.2) Set the Application Pool account as Network Service or a Domain Account.  In IIS 7.5, the default identity is ApplicationPoolIdentity


9.3) Inside SQL Server, go to the Security / Logins and add either Network Service or custom User (aka a domain account)


9.4) Make sure the Custom Logging and ODBC Logging modules are installed.


10) Test your website.  Browse http://localhost and look in the table, you should have results. 


In conclusion, hopefully this will help those who want to use ODBC Logging with IIS 7.0.  Here is a KB article that discusses other versions.  How to configure ODBC logging in IIS    Here is more information on the ODBCLogging class on MSDN.  IIS 7.0- OdbcLoggingSection Class


Cheers,


Steve Schofield
Windows Server MVP – IIS

10 thoughts on “IIS7 – post #57 – How to setup ODBC Logging in IIS 7.0/7.5

  1. Lukman

    Try every single steps, but nothing capture in the database table.

    I wonder of step 9.4) Make sure the Custom Logging and ODBC Logging modules are installed.

    How do I check whether the modules have been installed?

    For your assistance please.

  2. to lukman

    click right mouse button and select web server. “add role services”. and select again. and u gonna see odbc logging.

  3. Pano Kappos

    Hi Steve,

    Thank you so much for this article.
    You have saved me a huge amount of time, i have been wrestling with this one on and off for a while now.

    The only thing i had to do extra was go to server manager and under web server, and add the two ODBC and custom logging roles as suggested in the comment by ADMIN.

    I wish there were more blow by blow articles of such clarity online.

  4. Geographer22

    I have implemented logging and it seems to work fine on some of my pages but it seems to be causing 500 errors on my asp.net pages running on other app pools. Has anyone elese ran into this.

  5. GISMan

    I had to add the Network Service account as a datawriter to the InternetLog table to get this working which I assume is because my app pools are running under that account? Awesome article though, I’ve been wanting to do this for a long time!

  6. Praveen

    I have sucessfully done upto step 8.

    When I do step 9) Enable your particular website to use ODBCLogging.
    with the following command
    appcmd set sites “IISLogging” -logFile.logFormat:Custom -logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}

    The “IISLogging” web application fails with this error. “HTTP Error 500.0 – Internal Server Error”

    PLEASE HELP, I need to get this working.

  7. Jay Ohman

    Great information, I have logging going to SQL Server. But a nasty problem lingers, so I’m wondering:

    – In step 4 above (not actually numbered, referencing the IIS_schema.xml file) are we supposed to manually edit this file? Will the appcmd edit this file? The blurb indicates we can “.. display using appcmd ..”, but I can’t figure out how to do that. (appcmd seemd to only edit the applicationHost.config file).

    – Nasty problem: Hits are being logged to SQL Server, but the values for fields “ProcessingTime”, “BytesRecvd”, and “BytesSent” are all 0 (zero)!! I have a bunch of traffic analysis that rely on these fields, I really need to get valid data!

    I have scrubbed through this write-up dozens of times trying to get this working correctly, thus the question about the IIS_schema.xml file.

    Help PLEASE!!

Comments are closed.