Create a connection to Azure Cosmos DB by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Azure Cosmos DB icon is not available, click the Add More icon to download and install the Azure Cosmos DB connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Before You Connect

Role Assignment

Configure role-based access control for your Azure Cosmos DB account with Azure AD

You can either assign one of the built-in role definitions:

• CosmosDB Built-in Data Reader
• CosmosDB Built-in Data Contributor

or create your own custom role definitions. You must also set the scope of the role assignment, where "/" means that the identity has access to all the databases.

Connecting to Azure Cosmos DB

Account Key

Log in to the Azure Portal, select Azure Cosmos DB, and select your account.

Set the following to authenticate:

• AccountEndpoint: The Cosmos DB account URL. Set this to the URI value found in the Settings > Keys blade of the Cosmos DB account.
• AccountKey: A master key token or a resource token for connecting to Azure Cosmos DB. Set this to the PRIMARY KEY value found in the Settings > Keys blade of the Cosmos DB account.
• TokenType: (optional). Set this to "master" (the default value) if you are using a Master Token, which is a full permissions token generated during account creation. Otherwise, set this property to "resource" if you are using a Resource Token, which is a custom permissions token generated when a database user is set up.

Azure AD

Azure AD is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with Azure Cosmos DB using an internet browser. The Sync App facilitates this in several ways as described below. Set your AuthScheme to AzureAD. All AzureAD flows assume that you have done so.

Desktop Applications

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• OAuthClientId: (custom applications only) Set this to the client Id in your application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in your application settings.
• CallbackURL: Set this to the Redirect URL in your application settings.

When you connect the Sync App opens the OAuth endpoint in your default browser. Log in and grant permissions to the application.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Azure Cosmos DB. See Creating a Custom AzureAD App. You can then use the Sync App to get and manage the OAuth token values. Get an OAuth Access Token

Set one of the following connection properties groups depending on the authentication type to obtain the OAuthAccessToken:

1. Authenticating using a Client Secret
   • OAuthClientId: Set this to the client Id in your app settings.
   • OAuthClientSecret: Set this to the client secret in your app settings.
2. Authenticating using a Certificate
   • OAuthClientId: Set this to the client Id in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings. If necessary, set the Permissions parameter to request custom permissions.

   The stored procedure returns the URL to the OAuth endpoint.

2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Permissions parameter to request custom permissions.

To connect to data, set the OAuthAccessToken connection property to the access token returned by the stored procedure. When the access token expires after ExpiresIn seconds, call GetOAuthAccessToken again to obtain a new access token.

Client Credentials

Client OAuth Flow

All permissions related to the client oauth flow require admin consent. This means the app embedded with the CData Sync App cannot be used in the client oauth flow. You must create your own OAuth app in order to use client credentials. See Creating a Custom AzureAD App for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions - Delegated and Application permissions. The permissions used during client credential authentication are under Application Permissions. Select the permissions you require for your integration.

You are ready to connect after setting one of the connection properties groups depending on the authentication type.

1. Authenticating using a Client Secret
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthGrantType: Set this to CLIENT.
   • OAuthClientId: Set this to the client Id in your app settings.
   • OAuthClientSecret: Set this to the client secret in your app settings.
2. Authenticating using a Certificate
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • AzureTenant: Set this to the tenant you wish to connect to.
   • OAuthGrantType: Set this to CLIENT.
   • OAuthClientId: Set this to the client Id in your app settings.
   • OAuthJWTCert: Set this to the JWT Certificate store.
   • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections will take place and be handled internally.

Azure Service Principal

Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal. The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow, and it does not involve direct user authentication. Instead, credentials are created for just the app itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Note: You must create a custom application prior to assigning a role. See Creating a Custom AzureAD App for more information.

When authenticating using an Azure Service Principal, you must register an application with an Azure AD tenant. Follow the steps below to create a new service principal that can be used with the role-based access control.

Assign a role to the application

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the particular subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Complete the Authentication

In both methods

Before choosing client secret or certicate authentication, follow these steps then continue to the relevant section below:

1. AuthScheme: Set this to the AzureServicePrincipal in your app settings.
2. InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
3. AzureTenant: Set this to the tenant you wish to connect to.
4. OAuthClientId: Set this to the client Id in your app settings.

Continue with the following:

1. OAuthClientId: Set this to the client Id in your app settings.
2. OAuthClientSecret: Set this to the client secret in your app settings.

Authenticating using a Certificate

Continue with the following:

1. OAuthJWTCert: Set this to the JWT Certificate store.
2. OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Fine Tuning Data Access

You can use the following properties to gain greater control over Azure Cosmos DB API features and the strategies the Sync App uses to surface them:

• RowScanDepth: This property determines the number of rows that will be scanned to detect column data types when generating table metadata.
• TypeDetectionScheme: This property allows more control over the strategy implemented by the RowScanDepth property.

• GenerateSchemaFiles: This property enables you to persist table metadata in static schema files that are easy to customize, to persist your changes to column data types, for example.
  You can set this property to "OnStart" to generate schema files for all tables in your database at connection. Or, you can generate schemas as you execute SELECT queries to tables.
  The resulting schemas are based on the connection properties you use to configure Automatic Schema Discovery
  To use the resulting schema files, set the Location property to the folder containing the schemas.

Just as described in the SQL Compliance the Sync App supports batch CUD (Create, Update, Delete) operations. Batch processing is achieved by issuing multiple requests simultaneously. Even though this method greatly improves the performance for write operations, the cost of these operations is relatively high, thus the Request Units (RU) budget per second for a certain container or database may be exceeded. Depending on your Azure Cosmos DB Service Quotas, exceeding the RU budgets may incur in extra costs, or it may even temporary throttle or interrupt the Azure Cosmos DB usage for other workloads.

In order to avoid exceeding the RU budget per second, the Sync App dynamically adjusts the number of concurrent requests per second depending on the set WriteThroughputBudget and the constantly adjusted average RU cost per statement. The user can utilize the WriteThroughputBudget connection property to define the RU budged per second, that batch write operations should not exceed. Another important factor in batch write operations is the MaxThreads connection property, which specifies the maximum number of concurrent requests. If using a low MaxThreads value, the Sync App might not be able to efficiently use the available budget.

Since the requests throttling logic is applied client-side, in a few cases the RU/s budged may be exceeded by a relatively small amount. These cases include Inserting, Updating and Deleting records with highly variable column count and input value length per column.

Note: By default, the WriteThroughputBudget property is set 1000 RU/s and the MaxThreads property is set to 200 threads.

Azure Cosmos DB is a schemaless, document database that provides high performance, availability, and scalability. These features are not necessarily incompatible with a standards-compliant query language like SQL-92. In this section we will show various schemes that the Sync App offers to bridge the gap with relational SQL and a document database.

Working with Azure Cosmos DB Objects as Tables

The Sync App models the schemaless Azure Cosmos DB objects into relational tables and translates SQL queries into Azure Cosmos DB queries to get the requested data. See Query Mapping (Sql API) for more details on how various Azure Cosmos DB operations are represented as SQL.

Discovering Schemas Automatically

The Automatic Schema Discovery scheme automatically finds the data types in a Azure Cosmos DB object by scanning a configured number of rows of the object. You can use RowScanDepth, FlattenArrays, and FlattenObjects to control the relational representation of the collections in Azure Cosmos DB. You can also write Free-Form Queries not tied to the schema.

Customizing Schemas

Optionally, you can use Custom Schema Definitions to project your chosen relational structure on top of a Azure Cosmos DB object. This allows you to define your chosen names of columns, their data types, and the location of their values in the collection.

Set GenerateSchemaFiles to save the detected schemas as simple configuration files that are easy to extend. You can persist schemas for all collections in the database or for the results of SELECT queries.

Limitations of the RawValue TypeDetectionScheme

If the TypeDetectionScheme is set to RawValue, the Sync App will push each document as single aggregate value on a column named JsonData, along with its resource identifier on the separate Primary Key column. The JSON documents are not processed, and as a result, the below functionalities are NOT supported with this configuration.

• Automatic Schema Discovery
• Free-Form Queries
• Vertical Flattening
• SQL API Built-In Functions
• SQL API GROUP BY
• Almost all server side supported filters apart from WHERE clause conditions built with the resource identifier.

The Sync App automatically infers a relational schema by inspecting a series of Azure Cosmos DB documents in a collection. You can use the RowScanDepth property to define the number of documents the Sync App will scan to do so. The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties.

Flattening Objects

If FlattenObjects is set, all nested objects will be flattened into a series of columns. For example, consider the following document:

{
  id: 12,
  name: "Lohia Manufacturers Inc.",
  address: {street: "Main Street", city: "Chapel Hill", state: "NC"},
  offices: ["Chapel Hill", "London", "New York"],
  annual_revenue: 35,600,000
}

If FlattenObjects is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be {street: "Main Street", city: "Chapel Hill", state: "NC"}. See JSON Functions for more details on working with JSON aggregates.

You can change the separator character in the column name from a dot by setting SeparatorCharacter.

Flattening Arrays

The FlattenArrays property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short, for example the coordinates below:

"coord": [ -73.856077, 40.848447 ]

It is best to leave other unbounded arrays as they are and piece out the data for them as needed using JSON Functions.

As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured Azure Cosmos DB data. JSON Functions enable you to use standard JSON functions to summarize Azure Cosmos DB data and extract values from any nested structures. Custom Schema Definitions enable you to define static tables and give you more granular control over the relational view of your data; for example, you can write schemas defining parent/child tables or fact/dimension tables. However, you are not limited to these schemes.

After connecting you can query any nested structure without flattening the data. Any relations that you can access with FlattenArrays and FlattenObjects can also be accessed with an ad hoc SQL query.

Let's consider an example document from the following Restaurant data set:

 
{
  "address": {
    "building": "1007",
    "coord": [
      -73.856077,
      40.848447
    ],
    "street": "Morris Park Ave",
    "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
    {
      "grade": "A",
      "score": 2,
      "date": {
        "$date": "1393804800000"
      }
    },
    {
      "date": {
        "$date": "1378857600000"
      },
      "grade": "B",
      "score": 6
    },
    {
      "score": 10,
      "date": {
        "$date": "1358985600000"
      },
      "grade": "C"
    }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
} 

SELECT [address.building], [grades.1.grade] FROM restaurants WHERE restaurant_id = '30075445'

It is possible to retrieve an array of documents as if it were a separate table. Take the following JSON structure from the restaurants collection for example:

{
  "_id" : ObjectId("568c37b748ddf53c5ed98932"),
  "address" : {
    "building" : "1007",
    "coord" : [-73.856077, 40.848447],
    "street" : "Morris Park Ave",
    "zipcode" : "10462"
  },
  "borough" : "Bronx",
  "cuisine" : "Bakery",
  "grades" : [{
      "date" : ISODate("2014-03-03T00:00:00Z"),
      "grade" : "A",
      "score" : 2
    }, {
      "date" : ISODate("2013-09-11T00:00:00Z"),
      "grade" : "A",
      "score" : 6
    }, {
      "date" : ISODate("2013-01-24T00:00:00Z"),
      "grade" : "A",
      "score" : 10
    }, {
      "date" : ISODate("2011-11-23T00:00:00Z"),
      "grade" : "A",
      "score" : 9
    }, {
      "date" : ISODate("2011-03-10T00:00:00Z"),
      "grade" : "B",
      "score" : 14
    }],
  "name" : "Morris Park Bake Shop",
  "restaurant_id" : "30075445"
}

SELECT * FROM [restaurants.grades]

You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The Sync App expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Azure Cosmos DB documents -- this type of query is supported through the Azure Cosmos DB API.

SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'

The Sync App can return JSON structures as column values. The Sync App enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

JSON_EXTRACT

SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;

JSON_COUNT

SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

JSON_SUM

SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;

JSON_MIN

SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;

JSON_MAX

SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;

DOCUMENT

The DOCUMENT function can be used to retrieve the entire document as a JSON string. See the following query and its result as an example:

SELECT DOCUMENT(*) FROM Customers;

{ "id": 12, "name": "Lohia Manufacturers Inc.", "address": { "street": "Main Street", "city": "Chapel Hill", "state": "NC"}, "offices": [ "Chapel Hill", "London", "New York" ], "annual_revenue": 35,600,000 }

Cosmos DB also supports a number of built-in functions for common operations, that can be used inside queries. Here are some example of how can be used as part of select columns or the WHERE clause:

Use Built-in functions as part of SELECT columns

SELECT IS_NUMBER(user_id) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM [users]
SELECT POWER(user_id, 2) AS POWERSSS, LENGTH(id) AS LENGTH_ID, PI() AS JustThePI FROM [users]

Use Built-in functions as part of WHERE clause

SELECT * FROM [users] WHERE STARTSWITH(middle_name, 'G')
SELECT * FROM [users] WHERE REPLACE(middle_name, 'Chr', '___') = '___istopher'

Mathematical functions

The mathematical functions each perform a calculation, based on input values that are provided as arguments, and return a numeric value. Here's a table of supported built-in mathematical functions.

Type checking functions

The type checking functions allow you to check the type of an expression within SQL queries. Type checking functions can be used to determine the type of properties within documents dynamically when it is variable or unknown. Here's a table of supported built-in type checking functions.

String functions

The following scalar functions perform an operation on a string input value and return a string, numeric or Boolean value. Here's a table of built-in string functions:

Array functions

The following scalar functions perform an operation on an array input value and return numeric, Boolean or array value. Here's a table of built-in array functions:

Nested functions

You can also perform nested built-in functions, wich will be processed server side as well:

i.e. SELECT TOP 10 CONCAT(SUBSTRING(UPPER(cuisine), 0, 3), '-cuisine') FROM [restaurants]

The GROUP BY clause divides the query's results according to the values of one or more specified properties. This operation is partially done server-side because of some API limitations. We still need to operate a client-side grouping.

GROUP BY Examples

SELECT COUNT(*) AS CNT, gender FROM [users] GROUP BY gender
SELECT COUNT(*) AS CNT, gender, doc_type FROM [users] GROUP BY gender, doc_type

The Sync App maps SQL queries into the corresponding Azure Cosmos DB SQL API queries. A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used. The Sync App takes advantage of SQL API features such as the aggregation framework to compute the desired results.

SELECT Queries

SELECT id, name FROM Users

SELECT C.id, C.name FROM C

SELECT * FROM Users WHERE name = 'A'

SELECT * FROM C WHERE C.name = 'A'

SELECT * FROM Users WHERE name = 'A' OR email = '[email protected]'

SELECT * FROM C WHERE C.name = 'A' OR C.email = '[email protected]'

SELECT id, grantamt FROM WorldBank WHERE grantamt IN (4500000, 85400000) OR grantamt = 16200000

SELECT C.id, C.grantamt FROM C WHERE C.grantamt IN (4500000, 85400000) OR C.grantamt = 16200000

SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt ASC

SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt ASC

SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt DESC

SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt DESC

Aggregate Queries

SELECT COUNT(grantamt) AS COUNT_GRAMT FROM WorldBank

SELECT COUNT(C.grantamt) AS COUNT_GRAMT FROM C

SELECT SUM(grantamt) AS SUM_GRAMT FROM WorldBank

SELECT SUM(C.grantamt) AS SUM_GRAMT FROM C

Built-In functions

SELECT IS_NUMBER(grantamt) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM WorldBank

SELECT IS_NUMBER(C.grantamt) AS ISN_ATTR, IS_NUMBER(C.id) AS ISN_ID FROM C

SELECT POWER(totalamt, 2) AS POWERS_A, LENGTH(id) AS LENGTH_ID, PI() AS ThePI FROM WorldBank

SELECT POWER(C.totalamt, 2) AS POWERS_A, LENGTH(C.id) AS LENGTH_ID, PI() AS ThePI FROM C

You can extend the table schemas created with Automatic Schema Discovery by saving them into schema files. The schema files have a simple format that makes the schemas to edit.

Generating Schema Files

Set GenerateSchemaFiles to "OnStart" to persist schemas for all tables when you connect. You can also generate table schemas as needed: Set GenerateSchemaFiles to "OnUse" and execute a SELECT query to the table.

For example, consider a schema for the restaurants data set. This is a sample data set provided by Azure Cosmos DB.

Below is an example document from the collection:

{
  "address":{
    "building":"461",
      "coord":[
        -74.138492,
        40.631136
      ],
      "street":"Port Richmond Ave",
      "zipcode":"10302"
   },
   "borough":"Staten Island",
   "cuisine":"Other",
   "name":"Indian Oven",
   "restaurant_id":"50018994"
}

Customizing a Schema

When GenerateSchemaFiles is set, the Sync App saves schemas into the folder specified by the Location property. You can then change column behavior in the resulting schema.

The following schema uses the other:bsonpath property to define where in the collection to retrieve the data for a particular column. Using this model you can flatten arbitrary levels of hierarchy.

Below are the corresponding column definitions for the restaurants data set. In Custom Schema Example, you will find the complete schema.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">  

  <rsb:info title="StaticRestaurants" description="Custom Schema for the restaurants data set.">  
    <!-- Column definitions -->
    <attr   name="_rid"               xs:type="string"   key="true"   other:collrid="hWdRAKRi3Pg=" other:dbrid="hWdRAA==" other:partitionpath="/name" />
	<attr   name="borough"            xs:type="string"   />
    <attr   name="cuisine"            xs:type="string"   />
    <attr   name="address.building"   xs:type="string"   />
    <attr   name="address.street"     xs:type="string"   />
    <attr   name="address.coord.0"    xs:type="double"   />
    <attr   name="address.coord.1"    xs:type="double"   />
    <input name="rows@next" desc="Internal attribute used for paging through data."  />
  </rsb:info>  

  <rsb:set attr="collection" value="restaurants"/>

</rsb:script>

This section contains a complete schema. The info section enables a relational view of a Azure Cosmos DB object. For more details, see Custom Schema Definitions. The table below allows the SELECT, INSERT, UPDATE, and DELETE commands as implemented in the GET, POST, MERGE, and DELETE sections of the schema below.

Copy the rows@next input as-is into your schema. The operations, such as cosmosdbadoSysData, are internal implementations and can also be copied as is.

Set the Location property to the file directory that will contain the schema file.

When, creating custom schemas, the attr for _rid, shown below, is required.

Also required are three properties for the _rid column definition:

• other:dbrid is found in the _self property of an item in the collection, after "dbs/".
• other:collrid is found in the _self property of an item in the collection, after "/colls/".
• other:partitionpath refers to the name of the partition specified when the collection was created.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">  

  <rsb:info title="StaticRestaurants" description="Custom Schema for the restaurants data set.">  
    <!-- Column definitions -->
	<attr   name="_rid"               xs:type="string"   key="true"   other:collrid="hWdRAKRi3Pg=" other:dbrid="hWdRAA==" other:partitionpath="/name" />
    <attr   name="borough"            xs:type="string"   />
    <attr   name="cuisine"            xs:type="string"   />
    <attr   name="address.building"   xs:type="string"   />
    <attr   name="address.street"     xs:type="string"   />
    <attr   name="address.coord.0"    xs:type="double"   />
    <attr   name="address.coord.1"    xs:type="double"   />
    <input name="rows@next" desc="Internal attribute used for paging through data."  />
  </rsb:info>  

  <rsb:script method="GET">
    <rsb:call op="cosmosdbadoSysData">
      <rsb:push />
    </rsb:call>
  </rsb:script>

  <rsb:script method="POST">
    <rsb:call op="cosmosdbadoSysData">
      <rsb:push />
    </rsb:call>
  </rsb:script>

  <rsb:script method="MERGE">
    <rsb:call op="cosmosdbadoSysData">
      <rsb:push />
    </rsb:call>
  </rsb:script>

  <rsb:script method="DELETE">
    <rsb:call op="cosmosdbadoSysData">
      <rsb:push />
    </rsb:call>
  </rsb:script>

</rsb:script>

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Azure Authentication

OAuth

SSL

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The type of authentication to use when connecting to Azure Cosmos DB.

Remarks

• AccountKey: Set this to perform authentication with AccountKey and AccountEndpoint.
• AzureAD: Set this to perform Azure Active Directory OAuth authentication.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal.

The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account.

Remarks

The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account.

A master key token or a resource token for connecting to the Azure Cosmos DB REST API.

Remarks

In the Azure portal, navigate to the Cosmos DB service and select your Azure Cosmos DB account. From the resource menu, go to the Keys page. Find the PRIMARY KEY value and set Token to this value.

Denotes the type of token: master or resource.

Remarks

The master key is created during the creation of an account. There are two sets of master keys, the primary key and the secondary key. The administrator of the account can then exercise key rotation using the secondary key. In addition, the account administrator can also regenerate the keys as needed.

Resource tokens are created when users in a database are set up with access permissions for precise access control on a resource, also known as a permission resource. A permission resource contains a hash resource token constructed with the information regarding the resource path and access type a user has access to. The permission resource token is time bound and the validity period can be overridden. When a permission resource is acted upon on (POST, GET, PUT), a new resource token is generated.

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

The Microsoft Online tenant being used to access data. If not specified, your default tentant will be used.

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

The Azure Environment to use when establishing a connection.

Remarks

In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

The grant type for the OAuth flow.

Remarks

The following options are available: CODE,CLIENT,PASSWORD

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).

Remarks

The name of the certificate store for the client certificate.

The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.

SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).

The type of key store containing the TLS/SSL client certificate.

Remarks

This property can take one of the following values:

The password for the TLS/SSL client certificate.

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

The subject of the TLS/SSL client certificate.

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, [email protected]". The common fields and their meanings are shown below.

If a field value contains a comma, it must be quoted.

The certificate to be accepted from the server when connecting using TLS/SSL.

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 is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App 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 Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

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 user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

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.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\CosmosDB Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

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

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Specify the Azure Cosmos DB database you want to work with.

Remarks

Specify the Azure Cosmos DB database you want to work with.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Specifies whether will return the calculated value of the aggregates or grouped by partiton range.

Remarks

Specifies whether will return the calculated value of the aggregates or grouped by partiton range.

Denotes the type of token: master or resource.

Remarks

The consistency level override for read options against documents and attachments. The valid values are: Strong, Bounded, Session, or Eventual (in order of strongest to weakest). The override must be the same or weaker than the account's configured consistency level.

The consistency level override for read options against documents and attachments. The valid values are: Strong, Bounded, Session, or Eventual (in order of strongest to weakest). The override must be the same or weaker than the account's configured consistency level.

By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays.

Remarks

By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. This is only recommended for arrays that are expected to be short.

Set FlattenArrays to the number of elements you want to return from nested arrays. The specified elements are returned as columns. The zero-based index is concatenated to the column name. Other elements are ignored.

For example, you can return an arbitrary number of elements from an array of strings:

["FLOW-MATIC","LISP","COBOL"]

Setting FlattenArrays to -1 will flatten all the elements of nested arrays.

Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON.

Remarks

Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. The property name is concatenated onto the object name with a dot to generate the column name.

For example, you can flatten the nested objects below at connection time:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

Indicates the user preference as to when schemas should be generated and saved.

Remarks

GenerateSchemaFiles enables you to save the table definitions identified by Automatic Schema Discovery. This property outputs schemas to .rsd files in the path specified by Location.

Available settings are the following:

• Never: A schema file will never be generated.
• OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
• OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
• OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.

Generate Schemas with SQL

When you set GenerateSchemaFiles to OnUse, the Sync App generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.

When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.

Generate Schemas on Connection

Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.

Alternatives to Static Schemas

If your data structures are volatile, consider setting GenerateSchemaFiles to Never and using dynamic schemas. See Automatic Schema Discovery for more information about dynamic schemas.

Editing Schemas

Schema files have a simple format that makes them easy to modify. See Custom Schema Definitions for more information.

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Specifies the maximum number of concurrent requests for Batch CUD (Create, Update, Delete) operations.

Remarks

This property should be used in conjunction with the WriteThroughputBudget connection property. The Sync App may execute less parallel requests than the configured MaxThreads value, since it always aims to not exceed the WriteThroughputBudget limit. The number of concurrent requests will also depend on the running machine's resources.

Note: This property is applicable only when executing batch CUD operations.

Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time.

Remarks

Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time.

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

The maximum number of results to return per page from Azure Cosmos DB.

Remarks

The Pagesize property affects the maximum number of results to return per page from Azure Cosmos DB. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

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; for example, "*=*".

The maximum number of rows to scan to look for the columns available in a table.

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

The character or characters used to denote hierarchy.

Remarks

In order to flatten out hierarchical structures, the Sync App needs some specifier that states the path to a column through the hierarchy. If this value is "." and a column comes back with the name address.city, this indicates that there is a mapped attribute with a child called city. If your data has columns that already use a single period within the attribute name, set the SeparatorCharacter to a different character or characters.

Whether or not to use the collection's Partition Key field as part of composite Primary Key for the corresponding exposed table.

Remarks

By default, this is set to TRUE, and the collection's Partition Key is used as part of the table's composite Primary Key along with the _rid column. If this is set to FALSE, only the _rid column will serve as the Primary Key for the exposed table.

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

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

Comma-separated options for how the provider will scan the data to determine the fields and datatypes in each document collection.

Remarks

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM [CData].[Entities].Customers WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

Set this property to false to switch using the id column as primary key instead the default _rid.

Remarks

Since CosmosDB allows you to use both _rid and id fields as unique values for retrieving resource data, you can set this property to false to switch using the id column as primary key instead the default _rid.

Defines the Requests Units (RU) budget per Second that the Batch CUD (Create, Update, Delete) operations should not exceed.

Remarks

The Sync App will dynamically adjust the maximum number of requests per second depending on the configured RU budget. Although the Sync App always aims to not exceed the RU budget, since the requests throttling logic is applied client-side, it may be exceeded by a relatively small amount in a few cases. These cases include Inserting, Updating and Deleting records with highly variable column count and input value length per column.

Note: This property is applicable only when executing batch CUD operations.