FAQ: How do I set up SQL Server logging in Exchange Connector?
Exchange Connector is able to log all POP3, SMTP and folder delivery operations to a SQL Server database. This is primarily designed for troubleshooting and diagnostic purposes. Some customers have also found it to be useful for developing custom reports about mail delivery.
In order for this feature to work, you must have Microsoft SQL Server 2000 or later already set up and running. SQL Server can be on a separate machine.
The database schema needs to be created manually on the SQL Server prior to enabling the SQL logging feature. A script is provided with Exchange Connector to create the database.
You can use the freely available SQL Server Express 2005+ or Microsoft Desktop Engine (MSDE) versions for this purpose. See the Microsoft SQL Server Express website to obtain SQL Server Express. Please note that the SQL Server Compact Edition is significantly different to the Express edition and is not supported.
Creating the SQL Server Database
To create the SQL server database:
- Locate the database creation script, ExchangeConnectorLog.sql, in the installation directory of the software. By default this is %ProgramFiles%\Quantum Software Solutions\Exchange Connector.
- SQL Server 2005+: Open the SQL Server Management Studio tool from the Microsoft SQL Server group on the Start Menu.SQL Server 2000: Open the SQL Server Query Analyzer tool.Enter the name of the machine on the network running the SQL Server. Select the authentication method (e.g. Windows Authentication) and enter the SQL-based credentials if necessary. Connect to the server.If you are using MSDE, the Query Analyzer tool will be unavailable. It is recommended that you download the free SQL Server 2005+ Express tools instead, or use the client tools available with the full version of SQL Server, if you have access to a license for the full version of SQL 2000. You can achieve the same functionality using the osql command-line utility. Refer to the MSDE documentation for further information.
- Paste the text of the script into the SQL Server Management Studio or Query Analyzer text editor window.
- Press the F5 key to run the script, which will create the database.
- Ensure that the script runs without generating any errors at the bottom of the query window. email the complete text of any errors generated to Technical Support for assistance.
Configuring Database Permissions
The account which Exchange Connector uses to connect to the SQL Server must have full access to the ExchangeConnectorLog database. If you configure Exchange Connector to connect using Windows Authentication, the account used will be the account which the service is running under. For more information, see Setting the Service Account for Exchange Connector.
If the service is running under one of the Windows built-in accounts (LocalSystem, LocalService, NetworkService), configuring the SQL Server permissions varies depending on the domain configuration and the versions of Windows involved. For simplicity, we recommend either changing the service account or using SQL Server authentication to connect to the database.
Setting Permissions in SQL Server 2005+
- Open SQL Server Management Studio (for SQL Server 2000, use Enterprise Manager) and connect to the SQL Server.
- Expand the SQL Server node in the Object Explorer and locate the Security node.
- Right-click the Security node and select New, Login.
- Enter the login name, and if creating a SQL Server login (Recommended), enter a password. Click OK to create the user.
- To give the user permissions to the ExchangeConnectorLog database, locate the Databases node in the Object Explorer.
- Expand the Security node under the ExchangeConnectorLog database, then right-click the Users node and select New User.
- Enter the name of the user exactly as it was entered when creating the user in step 4.
- In the Database role membership list, select the db_owner role. Click OK.
- Open the Exchange Connector Service Manager tool.
- Click the Options button, then select the Error Reporting / Logging tab.
- Select the Enable SQL Server logging option, and enter the correct connection details for your SQL server.
- Click Connect button, and select the database (ExchangeConnectorLog). If the connect operation fails, then either the SQL Server is not accessible, or the account does not have permission.
- Click OK. The changes will take effect immediately and all operations will be logged to the database when the next download begins.
Ensure that the SQL login settings are valid. If SQL logging is configured with incorrect permissions or the database is not correctly set up, a large number of error reports can be generated and in some cases, mail delivery may stop completely.