How to setup ODBC Logging in IIS 7.0 and later on windows

This post shows how-to setup ODBC logging on IIS 7.0.

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://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/8ea48fac-28c0-46ae-9b7a-e4682dae3264.mspx?mfr=true

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\IISLogs.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'IISLogs_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\IISLogs_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%\system32\inetsrv.

'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%\System32\inetsrv\config\schema\IIS_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 resultApplied 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 resultSITE 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:\inetpub\wwwroot" />
</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