Dynamics CRM Data Model

Connection String Options

  1. Auth Scheme
  2. Auto Cache
  3. Cache Connection
  4. Cache Driver
  5. Cache Location
  6. Cache Metadata
  7. Caller Id
  8. Connect On Open
  9. CRM Version
  10. Default Precision
  11. Device Credential Location
  12. Device Credential Password
  13. Device Name
  14. Device Password
  15. Firewall Password
  16. Firewall Port
  17. Firewall Server
  18. Firewall Type
  19. Firewall User
  20. Internet Facing Deployment
  21. Location
  22. Logfile
  23. Max Log File Size
  24. Offline
  25. Organization Name
  26. Other
  27. Pagesize
  28. Password
  29. Proxy Auth Scheme
  30. Proxy Auto Detect
  31. Proxy Password
  32. Proxy Port
  33. Proxy Server
  34. Proxy SSL Type
  35. Proxy User
  36. Pseudo Columns
  37. Query Cache
  38. Query Method
  39. Query Passthrough
  40. Readonly
  41. RTK
  42. SSL Cert
  43. SSL Server Cert
  44. STSURL
  45. Tables
  46. Timeout
  47. Url
  48. Use Display Names
  49. Use Name For Picklist Value
  50. User
  51. Use Simple Names
  52. Verbosity

Auth Scheme

Data Type

string

Default Value

"NTLM"

Remarks

Together with Password and User, this field is used to authenticate against an on-premises Dynamics CRM 4.0 server. This property will not be used for other versions of CRM. NTLM is the default option. Use the following options to select your authentication scheme:

  • NTLM: Set this to use your Windows credentials for authentication.
  • BASIC: Set this to use HTTP Basic authentication.
  • DIGEST: Set this to use HTTP Digest authentication.
  • KERBEROSDELEGATION: Set this to use delegation through the Kerberos protocol. Set the User and Password of the account you want to impersonate.

 

 

Auto Cache

Data Type

bool

Default Value

false

Remarks

When AutoCache is set, the driver automatically caches the results of SELECT queries to a cache specified by the CacheLocation option. CacheLocation defines the path to a simple, file-based cache.

AutoCache is the simplest caching configuration available, however, like any caching scheme, using a cache is not without pitfalls such as reporting on stale data. The driver is designed to be fully functional without relying on caching.

The following sections outline how and when to use AutoCache. Understanding how AutoCache works and its limitations will help you choose an effective caching strategy. For more information on deploying other caching strategies, see Caching Data.

How AutoCaching Works

 

When you execute a SELECT statement with AutoCache set, the driver executes the query to the remote data and persists the results; rows and columns that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it.

Non-queries (such as UPDATE/INSERT/DELETE statements) are executed to the remote data as well; these statements will not modify the data in the cache at all, regardless of the value set for AutoCache.

To work with the local data, append #CACHE to the table name. For example:

SELECT * FROM [Lead#CACHE]
Limitations of AutoCache

 

In the following scenarios, consider the alternatives listed below:

  • When you need to work with the cache transparently: Because AutoCache requires a special syntax to utilize the cache, it is not suitable for use with BI, analytics, and reporting. Many of these tools generate SQL statements for you; these generated statements will still be executed to Dynamics CRM instead of the cache.

    In these situations, one solution is to use the Offline property. When this is set, all queries are executed locally. See Caching: Best Practices for examples.

    One downside of this approach is that it requires a separate connection. As an alternative, consider using the CData Sync tool to maintain a local database that is kept fresh with scheduled updates.

     

  • When you need more control over cached data: The AutoCache feature does not have the ability to remove rows from the cache that were deleted from the remote data. It also does not support dropping a table from the cache or more advanced cache maintenance such as changing the cached table schemas.

    In this scenario, consider REPLICATE Statements and CACHE Statements. REPLICATE statements leverage incremental update support in the Dynamics CRM API. With incremental updates, only the changeset needs to be retrieved from Dynamics CRM, so the driver is able to merge any changes into the cache much more quickly. For more granular control, use CACHE statements.

    CACHE statements can remove cached rows that no longer exist in Dynamics CRM.

    See Caching Explicitly for more information on how to use CACHE statements.

  • When you need to work with an RDBMS: AutoCache can only be used with the default database, JavaDB or SQLite. Many enterprises will need to use an RDBMS to support more concurrent writes or integrate with existing infrastructure. You can specify a database driver with CacheConnection and CacheDriver.

 

 

Cache Connection

Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheConnection defines the connection properties necessary to connect to the cache database.

Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost:7437;user=sa;password=123456;databaseName=Cache'

 

 

 

Cache Driver

Data Type

string

Default Value

""

Remarks

You can cache to any database that you have a JDBC driver for. The driver has been tested with SQL Server, Derby and Java DB, MySQL, Oracle, and SQLite.

The cache database is determined based on CacheDriver and the CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you would like to use to cache data. The example below caches to SQL Server:

Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost:7437;user=sa;password=123456;databaseName=Cache'
Note that the driver JAR must be specified on the classpath.

 

 

Cache Location

Data Type

string

Default Value

""

Remarks

If AutoCache is set but the cache location is not specified, CacheLocation defaults to the cache folder on the directory specified by the Location setting.

The CacheLocation is a simple, file-based cache. See the CacheConnection and CacheDriver properties to cache to other databases.

 

Cache Metadata

Data Type

bool

Default Value

false

Remarks

The cache.db file will be created in the location specified by the CacheConnection or if that is not set, the CacheLocation.

 

Caller Id

Data Type

string

Default Value

""

Remarks

The Id of a user to impersonate when inserting or updating new records.

 

 

Connect On Open

Data Type

bool

Default Value

true

Remarks

When set to 'true', a connection will be made to Dynamics CRM when the connection is opened. This property enables the 'Test Connection' feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to Dynamics CRM and nothing from this initial connection is maintained.

Setting this property to 'false' may provide performance improvements (depending upon the number of times a connection is opened).

 

CRM Version

Data Type

string

Default Value

"CRM2011+"

Remarks

The type of Dynamics CRM server to which you are connecting. Accepted entries are CRM2011+ or CRMOnline. A value of CRMOnline is required to connect using the Office 365 STS. For CRM Online with local AD FS or an IFD instance of CRM, you must also specify the STSURL property to the value used by your organization. You can retrieve this value with the GetSTSUrl stored procedure.

Set InternetFacingDeployment to connect to an IFD instance of CRM.

 

Default Precision

Data Type

string

Default Value

"2"

Remarks

The currency precision that is used for pricing throughout the system. Valid values are 0-4 and Auto. If the value is Auto, the default value will be retrieved from the Dynamics CRM server.

 

 

Device Credential Location

Data Type

string

Default Value

""

Remarks

Dynamics CRM uses claims-based authentication when authenticating using a Windows Live Id. This requires device credentials, which consist of a DeviceName and a DevicePassword. The device credentials need to be generated only once and can be reused. It is customary for each application to have its own set of device credentials.

The credentials in the DeviceCredentialLocation may be protected using the DeviceCredentialPassword. If the DeviceCredentialPassword is specified, then it will be used to encrypt and decrypt the device credentials. If it is not specified, then the credentials are stored in plaintext.

If the DeviceCredentialLocation is not specified and neither are DeviceName and DevicePassword, then the driver will generate the credentials for onetime use. This can be inefficient for repeated use.

 

Device Credential Password

Data Type

string

Default Value

""

Remarks

Dynamics CRM uses claims-based authentication when authenticating using a Windows Live Id. This requires device credentials, which consist of a DeviceName and a DevicePassword. The device credentials need to be generated only once and can be reused. It is customary for each application to have its own set of device credentials.

The credentials in the DeviceCredentialLocation may be protected using the DeviceCredentialPassword. If the DeviceCredentialPassword is specified, then it will be used to encrypt and decrypt the device credentials. If it is not specified, then the credentials are stored in plaintext.

 

Device Name

Data Type

string

Default Value

""

Remarks

Dynamics CRM uses claims-based authentication when authenticating using a Windows Live Id. This requires device credentials, which consist of a DeviceName and a DevicePassword. The device credentials need to be generated only once and can be reused. It is customary for each application to have its own set of device credentials.

You may specify device credentials using the DeviceName and DevicePassword properties or using DeviceCredentialLocation. If DeviceName and DevicePassword are specified, the contents of the DeviceCredentialLocation are ignored.

 

Device Password

Data Type

string

Default Value

""

Remarks

Dynamics CRM uses claims-based authentication when authenticating using a Windows Live Id. This requires device credentials, which consist of a DeviceName and a DevicePassword. The device credentials need to be generated only once and can be reused. It is customary for each application to have its own set of device credentials.

You may specify device credentials using the DeviceName and DevicePassword properties or using DeviceCredentialLocation. If DeviceName and DevicePassword are specified, the contents of the DeviceCredentialLocation are ignored.

 

Firewall Password

Data Type

string

Default Value

""

Remarks

If FirewallServer is specified, the FirewallUser and FirewallPassword properties are used to connect and authenticate to the given firewall.

 

Firewall Port

Data Type

string

Default Value

""

Remarks

Note that the driver sets the FirewallPort to the default port associated with the specified FirewallType. See the description of the FirewallType option for details.

 

Firewall Server

Data Type

string

Default Value

""

Remarks

If this property is set to a domain name, a DNS request is initiated and the name is translated to the corresponding IP address.

 

Firewall Type

Data Type

string

Default Value

"NONE"

Remarks

The applicable values are:

 

   
Firewall Type Default FirewallPort
TUNNEL 80
SOCKS4 1080
SOCKS5 1080

 

 

Firewall User

Data Type

string

Default Value

""

Remarks

If the FirewallServer is specified, the FirewallUser and FirewallPassword properties are used to connect and authenticate against the firewall.

 

Internet Facing Deployment

Data Type

string

Default Value

"false"

Remarks

Set this to true if you are connecting to an Internet Facing Deployment (IFD) for CRM.

 

 

Location

Data Type

string

Default Value

""

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

The schema files used in your application must be deployed with other assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.

 

Logfile

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, take a look at Verbosity.

 

Max Log File Size

Data Type

string

Default Value

""

Remarks

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. There is no limit by default. Values lower than 100kB will use 100kB as the value instead.

 

Offline

Data Type

bool

Default Value

false

Remarks

When Offline is set to TRUE, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

 

Organization Name

Data Type

string

Default Value

""

Remarks

The following table is a description for setting this property in each Dynamics CRM version.

 

 

   
Dynamics CRM 4.0 without IFD Optional. If this property is not set, the organization name can be retrieved from the URL.
CRM 4.0 with IFD Required. This property must be set.
Dynamics CRM 2011 on-premises Optional. If this property is not set, the organization name can be retrieved from the URL.
Dynamics CRM 2011 with IFD Optional.
Dynamics CRM 2011 without IFD Optional.

 

 

Other

Data Type

string

Default Value

""

Remarks

The Other property is a semicolon-separated list of name-value pairs used in connection parameters specific to a data source.

Caching Configuration

 

   
CachePartial=True Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.

 

Integration and Formatting

 

   
SupportAccessLinkedMode In Access' linked mode, it is generally a good idea to always use a cache as most data sources do not support multiple Id queries. However if you want to use the driver in Access but not in linked mode, this property must be set to False to avoid using a cache of a SELECT * query for the given table.
ConvertDateTimesToGMT Whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.
ClientCulture This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'.
Culture This setting can be used to specify culture settings that determine how the driver interprets certain data types that are passed into the driver. For example, setting Culture='de-DE' will output German formats even on an American machine.

 

 

Pagesize

Data Type

string

Default Value

"500"

Remarks

The number of rows to return per page from the data source.

 

 

Password

Data Type

string

Default Value

""

Remarks

The password used to authenticate to the Dynamics CRM site.

 

 

Proxy Auth Scheme

Data Type

string

Default Value

"BASIC"

Remarks

This value may be BASIC, DIGEST, NONE, NTLM, NEGOTIATE or PROPRIETARY.

 

Proxy Auto Detect

Data Type

bool

Default Value

true

Remarks

This indicates whether to use the default system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.

 

Proxy Password

Data Type

string

Default Value

""

Remarks

If the ProxyServer is specified, the ProxyUser and ProxyPassword properties are used to connect and authenticate against the firewall.

 

Proxy Port

Data Type

string

Default Value

"80"

Remarks

See the description of the ProxyServer field for details.

 

Proxy Server

Data Type

string

Default Value

""

Remarks

If this property is set to a domain name, a DNS request is initiated and the name is translated to the corresponding address.

 

Proxy SSL Type

Data Type

string

Default Value

"AUTO"

Remarks

This value may be AUTO, ALWAYS, NEVER, or TUNNEL.

 

Proxy User

Data Type

string

Default Value

""

Remarks

If a ProxyServer is specified, the ProxyUser and ProxyPassword options are used to connect and authenticate against the firewall.

 

Pseudo Columns

Data Type

string

Default Value

""

Remarks

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; i.e., "*=*".

 

Query Cache

Data Type

string

Default Value

""

Remarks

The QueryCache allows you to cache the results of a query in-memory and use them until the cache expires. Setting the QueryCache can improve performance if the same or similar (see below) queries are executed often. The in-memory query cache is shared across connections, so it can help with performance even if more than one connection is being used.

The cache manager for QueryCache will not only use the results in the cache for exactly the same query, but also for queries that represent a subset of data in the cached query. For example, in the following queries, the cache created while executing Query A will be used to obtain the results for both Query B and Query C.

SELECT * from Account; // Query A
SELECT * from Account WHERE Name LIKE '%John'; // Query B
SELECT Id, Name from from Account LIMIT 10; // Query C

 

Setting the QueryCache to zero disables in-memory caching.

 

Query Method

Data Type

string

Default Value

"FetchXML"

Remarks

The method to use when querying data from Dynamics CRM. In most cases FetchXML will work with all tables. However, QueryExpression may be specified as an alternative.

 

Query Passthrough

Data Type

bool

Default Value

false

Remarks

The Protocol Version used to authenticate with Dynamics CRM.

 

 

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

 

RTK

Data Type

string

Default Value

""

Remarks

The RTK property may be used to license a build. Please see the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

 

SSL Cert

Data Type

string

Default Value

""

Remarks

SSLCert is used for specifying a private key certificate for HTTP client authentication.

 

SSL Server Cert

Data Type

string

Default Value

""

Remarks

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. This can take the form of a full PEM certificate, the path to a file containing the certificate, the public key, the MD5 thumbprint, or the SHA1 thumbprint. If not specified, any trusted certificate will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).

 

STSURL

Data Type

string

Default Value

""

Remarks

The driver will automatically select the correct STSURL if CRMVersion is set to 'CRMOnline'. For IFD instances of CRM or CRM Online with local AD FS authentication, this value must be set and can be retrieved with the GetSTSUrl stored procedure.

 

Tables

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.

 

Timeout

Data Type

string

Default Value

"60"

Remarks

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.

If Timeout expires and the operation is not yet complete, the driver throws an exception.

 

Url

Data Type

string

Default Value

""

Remarks

The root URL of the organization. For example, a CRM 4.0 or CRM 2011 URL will resemble http://MySite/MyOrganization. For CRM Online, the URL will resemble https://myOrg.crm.dynamics.com/.

 

 

Use Display Names

Data Type

bool

Default Value

false

Remarks

Boolean determining if the display names for the columns should be used instead of the API names.

 

Use Name For Picklist Value

Data Type

bool

Default Value

true

Remarks

Boolean determining if the string value should be used for picklist field values instead of integers.

 

User

Data Type

string

Default Value

""

Remarks

The user who is authenticating to the Dynamics CRM site.

 

 

Use Simple Names

Data Type

bool

Default Value

false

Remarks

If true, tables and columns returned will be renamed to make them easier to read and to specify in a query. Requirements for using [] or '' are removed. If false, the tables and columns will appear as they do in Dynamics CRM.

 

Verbosity

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:

 

   
1 Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the HTTP requests.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

Views

  1. OptionSetInfo

OptionSetInfo

Gets basic information about the OptionSet values available for a given table and displays the mapping of OptionSet string values to OptionSet int values.


 

Columns

 

 

 

   
Name Type Description
TableName [KEY] String The table to retrieve information about. This must be specified as an input.
ColumnName [KEY] String The column name.
OptionSetString [KEY] String A string value for the OptionSet column.
OptionSetInt [KEY] Integer A int value for the OptionSet column.
APIColumnName String The internal name of the column.
DataType String The system data type for the column.
DataSourceDataType String The Dynamics CRM data type for the column before conversion to the system data type.
Description String A description for the column if available.
IsReadOnly Boolean Boolean determining if the column is read-only or not.

 

Tables

Regretfully, the Tables Data Model is too large to be included in this single documentation. However, the information can still be accessed at the CDATA website here.