Connecting to Exchange

Concept Information

Use the Exchange data connector to import data from Microsoft's Exchange email and calendar server. You can import data from a single Exchange account.

Note

You cannot use this connector independently of Analytics. You can configure a DSN for the connector driver using the Windows ODBC Data Source Administrator, however you must test the DSN connection from within Analytics and not from the connector's Windows DSN configuration dialog.

Before you start

To connect to Exchange, you must gather the following:

the domain name or IP address of the Exchange server (Exchange Web Services URL)
the version of the Exchange platform you are connecting to
the connecting user account, including the user name and password

For help gathering the connection prerequisites, contact the Exchange administrator in your organization. If your administrator cannot help you, you or your administrator should contact Exchange Support.

Create an Exchange connection

From the Analytics main menu, select Import > Database and application.
From the New Connections tab, in the ACL Connectors section, select Exchange.
Tip
You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.
In the Data Connection Settings panel, enter the connection settings and at the bottom of the panel, click Save and Connect.
You can accept the default Connection Name, or enter a new one.

The connection for Exchange is saved to the Existing Connections tab. In the future, you can reconnect to Exchange from the saved connection.

Once the connection is established, the Data Access window opens to the Staging Area and you can begin importing data. For help importing data from Exchange, see Working with the Data Access window.

Connection settings

Basic settings

Setting	Description	Example
User	The user of the Exchange account used to authenticate. Together with Password, this field is used to authenticate to the server.	recipient@example.com
Password	The password of the exchange account used to authenticate. Together with User, this field is used to authenticate to the server.
Server	The address of the Exchange server to which you are connecting. This should be set to the Exchange Web Services URL. For Exchange Online, you should set it to https://outlook.office365.com/EWS/Exchange.asmx.	https://outlook.office365.com/EWS/Exchange.asmx
Platform	The Platform associated with the Exchange server.	Exchange_Online

Advanced settings

Setting	Description	Example
Convert Datetime to GMT	Converts datetime fields to GMT time zone during import. If false, the datetime value is converted to the operating system time zone of the machine running Analytics.	true
AuthScheme	The scheme used for authentication. Together with Password and User, this field is used to authenticate against the server. BASIC is the default option. Use the following options to select your authentication scheme: NTLM uses your Windows credentials for authentication. BASIC uses HTTP Basic authentication. Note Microsoft has announced that is deprecating Basic authentication for Exchange Web Services in October 2020. Consider using an alternative authentication scheme. DIGEST uses HTTP Digest authentication. NEGOTIATE negotiates an authentication mechanism with the server. Set AuthScheme to NEGOTIATE to use Kerberos authentication. KERBEROSDELEGATION uses delegation through the Kerberos protocol. Set the User and Password of the account you want to impersonate.	BASIC
Impersonation Type	The type of identifier to use with impersonation while sending requests to the Exchange site: PrincipalName represents the user principal name (UPN) of the account to use for impersonation. This should be the UPN for the domain where the user account exists SID represents the security descriptor definition language (SDDL) form of the security identifier (SID) for the account to use for impersonation PrimarySmtpAddress represents the primary Simple Mail Transfer Protocol (SMTP) address of the account to use for Exchange impersonation. If the primary SMTP address is supplied, it will cost an extra Active Directory directory service lookup in order to obtain the SID of the user. It is recommended that you use the SID or UPN if they are available SmtpAddress represents the Simple Mail Transfer Protocol (SMTP) address of the account to use for Exchange Impersonation. If the SMTP address is supplied, it will cost an extra Active Directory lookup in order to obtain the SID of the user. It is recommended that you use the SID or UPN if they are availables	PrincipalName
Impersonation User	The user to impersonate while sending requests to the Exchange site.
Include Content	Whether to include message body content for all records returned. Caution This setting affects performance and may cause your query to timeout if you are working with many records.	false
Limit Key Size	The maximum length of a primary key column. Setting the size to 0 will make the key length revert to the original length. This property makes the connector override the reported length of all the primary key columns.	255
Map to Long Varchar	Controls whether or not a column is returned as SQL_LONGVARCHAR. Use this setting to map any column larger than the specified size so they are reported as SQL_LONGVARCHAR instead of SQL_VARCHAR.	-1
Map To WVarchar	Controls whether or not string types map to SQL_WVARCHAR instead of SQL_VARCHAR. It is set by default. String columns must be mapped to SQL_WVARCHAR to accommodate various international character sets, so MapToWVarchar is set to true by default. You may set it to false to use SQL_VARCHAR instead.	true
Pseudo Columns	Indicates whether or not to include pseudo columns as columns to the table. This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns.	MyTable=*
SSL Server Cert	The certificate to be accepted from the server when connecting using TLS/SSL. You can specify any of the following: full PEM certificate path to a local file containing the certificate the public key the MD5 Thumbprint (hex values can also be either space or colon separated) the SHA1 Thumbprint (hex values can also be either space or colon separated) If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine will be rejected.	C:\cert.cer
Support Enhanced SQL	Enhances SQL functionality beyond what can be supported through the API directly, by enabling in-memory client-side processing: true the connector offloads as much of the SELECT statement processing as possible to IMAP and then processes the rest of the query in memory. In this way the driver can execute unsupported predicates, joins, and aggregation false the connector limits SQL execution to what is supported by the IMAP API Execution of predicates The connector determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets. Execution of Joins The connector uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once. Execution of Aggregates The connector retrieves all rows necessary to process the aggregation in memory.
Upper Case Identifiers	Report all identifiers in uppercase, including table and column names.	false
Proxy Authentication Scheme	The authentication type to use to authenticate to the ProxyServer proxy. This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort. Note The connector will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed. The authentication type can be one of the following: BASIC The driver performs HTTP BASIC authentication DIGEST The driver performs HTTP DIGEST authentication. NEGOTIATE The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication PROPRIETARY The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request	BASIC
Proxy Auto Detect	Indicates whether to use the system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.	true
Proxy User	A user name to be used to authenticate to the ProxyServer proxy. The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer. You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the username of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a username in one of the following formats: user@domain domain\user	john_doe@example.com
Proxy Password	A password to be used to authenticate to the ProxyServer proxy. This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme. If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy. If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication
Proxy Server	The hostname or IP address of a proxy to route HTTP traffic through. The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy. By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.	206.174.193.115
Proxy Port	The TCP port the ProxyServer proxy is running on.	80
Proxy SSL Type	The SSL type to use when connecting to the ProxyServer proxy: AUTO If the URL is an HTTPS URL, the connector will use the TUNNEL option. If the URL is an HTTP URL, the connector will use the NEVER option (default) ALWAYS the connection is always SSL enabled NEVER the connection is not SSL enabled TUNNEL the connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy	AUTO

Impersonating user accounts

Users with the required permissions and the ApplicationImpersonation role in Exchange can read mailbox data for other users two ways:

using the Impersonation Type and Impersonation User fields to configure the connection

using the ImpersonationUser filter in a WHERE clause in SQL mode:

SELECT * FROM "Exchange"."DRAFTS" "DRAFTS"  WHERE ImpersonationUser='user@example.com'

Retrieving the message body

By default, the message body is only returned when you select one record from a table. If more than one record is returned, this field is left blank.

If you want to retrieve the message body for more than one record with a single query, you must set the Include Content option in the Advanced Settings.

Note

Using this connector, you can list attachment filenames, however you cannot access the contents of attachment files. You can only access the contents of the message body.

Returning the message body is resource-intensive and doing so for a number of records affects performance. If you need to examine the message body, try using other fields to identify the messages you want to analyze in detail. Then query this subset of messages individually to examine the message body for each one.

Filtering limitations

The following filter condition and field combinations are not supported:

Fields	Unsupported operators
All DateTime fields	On (=)
SenderName	Is (=) Starts with (LIKE "%value") Contains (LIKE "%value%")
SenderEmailAddress
FromName

Data connector updates

When you upgrade Analytics, the Robots Agent, or AX Server, you should test any of your scripts that import data using one of the Analytics data connectors (ACCESSDATA command).

The possibility exists that changes made by third-party data sources or ODBC driver vendors required updates to one or more of the data connectors. Scripted data connections may need to be updated in order to continue working correctly.

Re-run the import The easiest way to update a connection is to manually perform an import using the Data Access window in the upgraded version of Analytics. Copy the ACCESSDATA command from the log and use it to update your script.
Note
Before connecting to a data source and re-running the import, clear the connector cache to flush the existing set of table names.
In the Existing Connections tab in the Data Access window, beside the connector name, select > Clear cache.
Update field specifications You may also need to update field specifications in the script body to align with table schema changes in the data source or ODBC driver. Possible changes include field names, field data types, and field and record lengths.
Check the results of any filtering You should also check the results of any filtering that you apply as part of the data import. Confirm that the import filtering is including and excluding records correctly.

Exchange data connector changes

Specific changes made to the Exchange data connector are listed below.

Analytics version	Exchange table	Change
14.2	n/a	Exchange schema renamed in connector from Exchange to EWS. Example of required update in ACCESSDATA command: Old SELECT * FROM "Exchange"."Calendar" "Calendar" New SELECT * FROM "EWS"."Calendar" "Calendar"
	Calendar	Field renamed: Recurrence_DayOfWeek renamed to Recurrence_DaysOfWeek Field added: ModifiedOccurrences_Aggregate DeletedOccurrences_Aggregate Data type changed: Recurrence_StartDate changed from date to datetime Recurrence_EndDate changed from date to datetime
	Inbox and SentItems	Field renamed: ToRecipients_Name renamed to ToRecipients_Names ToRecipients_EmailAddress renamed to ToRecipients_EmailAddresses ToRecipients_ItemId renamed to ToRecipients_ItemIds CcRecipients_Name renamed to CcRecipients_Names CcRecipients_EmailAddress renamed to CcRecipients_EmailAddresses CcRecipients_ItemId renamed to CcRecipients_ItemIds BccRecipients_Name renamed to BccRecipients_Names BccRecipients_EmailAddress renamed to BccRecipients_EmailAddresses BccRecipients_ItemId renamed to BccRecipients_ItemIds
	Tasks	Field renamed: Recurrence_NumberOfOccurences renamed to Recurrence_NumberOfOccurrences