|
Matillion ETL Data Model for LDAP
|
Connection String OptionsBack To Top
- Server
- Port
- User
- Password
- BaseDN
- UseSSL
- LDAPVersion
- AuthMechanism
- Scope
- SSLServerCert
- FirewallType
- FirewallServer
- FirewallPort
- FirewallUser
- FirewallPassword
- Logfile
- Verbosity
- LogModules
- MaxLogFileSize
- MaxLogFileCount
- Location
- BrowsableSchemas
- Tables
- Views
- AutoCache
- CacheDriver
- CacheConnection
- CacheLocation
- CacheTolerance
- Offline
- CacheMetadata
- BatchSize
- ConnectionLifeTime
- ConnectOnOpen
- FollowReferrals
- FriendlyGUID
- FriendlySID
- MaxRows
- Other
- PoolIdleTimeout
- PoolMaxSize
- PoolMinSize
- PoolWaitTime
- PseudoColumns
- Readonly
- RTK
- SupportEnhancedSQL
- Timeout
- UseConnectionPooling
Server
Data Type
string
Default Value
""
Remarks
Specify the server's domain name or IP address in this property. This value does not need to include the LDAP:\\ portion, only the server domain name or IP.
Port
Data Type
string
Default Value
"389"
Remarks
The port the LDAP server is running on. Together with Server, this property is used to specify the LDAP server.
User
Data Type
string
Default Value
""
Remarks
Together with Password, this field is used to authenticate against the LDAP server.
Password
Data Type
string
Default Value
""
Remarks
Together with User, this field is used to authenticate against the LDAP server.
BaseDN
Data Type
string
Default Value
""
Remarks
Specifying a base DN may greatly improve performance when returning entries for large servers by limiting the number of entries that need to be examined.
UseSSL
Data Type
bool
Default Value
false
Remarks
Whether or not to use SSL to connect to the server. Note that a port of 636 will always use SSL.
LDAPVersion
Data Type
string
Default Value
"2"
Remarks
Set this property to 2 or 3. The driver is a standard LDAP client as specified in RFC 1777, 2251, and other LDAP RFCs.
AuthMechanism
Data Type
string
Default Value
"SIMPLE"
Remarks
By default, AuthMechanism is set to SIMPLE, and default plaintext authentication is used to log in to the server. If AuthMechanism is set to DIGESTMD5, the more secure DIGEST-MD5 authentication is used. If AuthMechanism is set to NEGOTIATE, NTLM/NEGOTIATE authentication will be used.
- SIMPLE
- DIGESTMD5
- NEGOTIATE
Scope
Data Type
string
Default Value
"WHOLESUBTREE"
Remarks
Limiting scope can greatly improve search performance. Set Scope to one of the following values:
- WholeSubtree: Limit the scope of the search to the BaseDN and all of its descendants.
- SingleLevel: Limit the scope of the search to the BaseDN and its direct descendants.
- BaseObject: Limit the scope of the search to the base object only (BaseDN).
SSLServerCert
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 is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
FirewallType
Data Type
string
Default Value
"NONE"
Remarks
This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the driver opens a connection to LDAP and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
FirewallServer
Data Type
string
Default Value
""
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.
FirewallPort
Data Type
int
Default Value
0
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.
FirewallUser
Data Type
string
Default Value
""
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.
FirewallPassword
Data Type
string
Default Value
""
Remarks
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
Logfile
Data Type
string
Default Value
""
Remarks
Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.
If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.
For more control over what is written to the log file, you can adjust the Verbosity property.
Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.
To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.
If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.
Java Logging
Java logging is also supported. To enable Java logging, set Logfile to:
Logfile=JAVALOG://myloggername
As in the above sample, JAVALOG:// is a required prefix to use Java logging, and you will substitute your own Logger.
The supplied Logger's getLogger method is then called, using the supplied value to create the Logger instance. If a logging instance already exists, it will reference the existing instance.
When Java logging is enabled, the Verbosity will now correspond to specific logging levels.
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 detailed in the Logging page.
LogModules
Data Type
string
Default Value
""
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.
MaxLogFileSize
Data Type
string
Default Value
"100MB"
Remarks
When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
Adjust the maximum number of logfiles generated with MaxLogFileCount.
MaxLogFileCount
Data Type
int
Default Value
-1
Remarks
When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.
The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.
Adjust the maximum size of the logfiles generated with MaxLogFileSize.
Location
Data Type
string
Default Value
"%APPDATA%\\CData\\LDAP Data Provider\\Schema"
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 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\\LDAP Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Mac | ~/Library/Application Support |
| Linux | ~/.config |
BrowsableSchemas
Data Type
string
Default Value
""
Remarks
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
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.
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.
Views
Data Type
string
Default Value
""
Remarks
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the driver.
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.
AutoCache
Data Type
bool
Default Value
false
Remarks
When AutoCache = true, the driver automatically maintains a cache of your table's data in the database of your choice.
Setting the Caching Database
When AutoCache = true, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:
- CacheLocation: Specifies the path to the file store.
- CacheDriver and CacheConnection: Specifies a driver to a database and the connection string.
See Also
- CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the LDAP metadata. Metadata then needs to be retrieved only once instead of every connection.
- Explicitly Caching Data: This section provides more examples of using AutoCache in Offline mode.
- CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.
CacheDriver
Data Type
string
Default Value
""
Remarks
You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.
The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.
Note that you must also add the CacheDriver JAR file to the classpath.
The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.
Derby and Java DB
The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR file is shipped in the JDK. You can find the JAR file, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath:
jdbc:ldap:CacheLocation='c:/Temp/cachedir';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:ldap:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
SQLite
The following is a JDBC URL for the SQLite JDBC driver:
jdbc:ldap:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
MySQL
The following is a JDBC URL for the included CData JDBC Driver for MySQL:
jdbc:ldap:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
SQL Server
The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:
jdbc:ldap:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
Oracle
The following is a JDBC URL for the Oracle Thin Client:
jdbc:ldap:Cache Driver=oracle.jdbc.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .
PostgreSQL
The following JDBC URL uses the official PostgreSQL JDBC driver:
jdbc:ldap:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';user=MyUserName;password=MyPassword;Server=MyServer;Port=MyPort;
CacheConnection
Data Type
string
Default Value
""
Remarks
The cache database is determined based on the CacheDriver and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.
The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.
Derby and Java DB
The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.
Alternatively, you can configure the connection to Derby manually using CacheDriver and CacheConnection. The following is the Derby JDBC URL syntax:
jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]
For example, to cache to an in-memory database, use the following:
jdbc:derby:memory
SQLite
To cache to SQLite, you can use the SQLite JDBC driver. The following is the syntax of the JDBC URL:
jdbc:sqlite:dataSource
- Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.
MySQL
The installation includes the CData JDBC Driver for MySQL. The following is an example JDBC URL:
jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache
The following are typical connection properties:
- Server: The IP address or domain name of the server you want to connect to.
- Port: The port that the server is running on.
- User: The user name provided for authentication to the database.
- Password: The password provided for authentication to the database.
- Database: The name of the database.
SQL Server
The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:
jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]
For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true
The following are typical SQL Server connection properties:
- Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
- Port: The port SQL Server is running on.
- Database: The name of the SQL Server database.
- Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
- User Id: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
- Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
Oracle
The following is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:
jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]
For example:
jdbc:oracle:thin:scott/tiger@myhost:1521:orcl
The following are typical connection properties:
-
Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
- Password: The password provided for authentication with the Oracle database.
- User Id: The user Id provided for authentication with the Oracle database.
PostgreSQL
The following is the JDBC URL syntax for the official PostgreSQL JDBC driver:
jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]
For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgres
The following are typical connection properties:
- Host: The address of the server hosting the PostgreSQL database.
- Port: The port used to connect to the server hosting the PostgreSQL database.
- Database: The name of the database.
- User name: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
- Password: The password provided for authentication with the PostgreSQL database.
CacheLocation
Data Type
string
Default Value
"%APPDATA%\\CData\\LDAP Data Provider"
Remarks
The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.
If left unspecified, the default location is "%APPDATA%\\CData\\LDAP Data Provider" with %APPDATA% being set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Mac | ~/Library/Application Support |
| Linux | ~/.config |
See Also
- AutoCache: Set to implicitly create and maintain a cache for later offline use.
- CacheMetadata: Set to persist the LDAP catalog in CacheLocation.
CacheTolerance
Data Type
int
Default Value
600
Remarks
The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.
Offline
Data Type
bool
Default Value
false
Remarks
When Offline = 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.
CacheMetadata
Data Type
bool
Default Value
false
Remarks
As you execute queries with this property set, table metadata in the LDAP catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.
When to Use CacheMetadata
The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.
When Not to Use CacheMetadata
- When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
- When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.
BatchSize
Data Type
int
Default Value
0
Remarks
When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.
Setting BatchSize to 0 will submit the entire batch as specified.
ConnectionLifeTime
Data Type
int
Default Value
0
Remarks
The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.
ConnectOnOpen
Data Type
bool
Default Value
false
Remarks
When set to true, a connection will be made to LDAP 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 LDAP 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).
FollowReferrals
Data Type
bool
Default Value
false
Remarks
When following referrals, you will only be able to return data from the referral servers. INSERT/UPDATE/DELETE will not be available without updating the connection string to connect directly to that server.
FriendlyGUID
Data Type
bool
Default Value
false
Remarks
When inspecting object attributes this setting determines whether GUID attributes such as "objectGUID" are returned as binary objects or converted into a human readable string such as "708d9374-d64a-49b2-97ea-489ddc717703". When set to True a friendly string value is returned. When set to False (default) a base 64 encoded string of the binary object is returned.
FriendlySID
Data Type
bool
Default Value
false
Remarks
When inspecting object attributes this setting determines whether SID attributes such as "objectSid" are returned as binary objects or converted into a human readable string such as "S-1-5-21-4272240814-246508344-1325542772-12464". When set to True a friendly string value is returned. When set to False (default) a base 64 encoded string of the binary object is returned.
MaxRows
Data Type
int
Default Value
-1
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.
Other
Data Type
string
Default Value
""
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.
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
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines 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. |
PoolIdleTimeout
Data Type
int
Default Value
60
Remarks
The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.
PoolMaxSize
Data Type
int
Default Value
100
Remarks
The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.
PoolMinSize
Data Type
int
Default Value
1
Remarks
The minimum number of connections in the pool. The default is 1.
PoolWaitTime
Data Type
int
Default Value
60
Remarks
The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.
PseudoColumns
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; for example, "*=*".
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. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.
SupportEnhancedSQL
Data Type
bool
Default Value
true
Remarks
When SupportEnhancedSQL = true, the driver offloads as much of the SELECT statement processing as possible to LDAP and then processes the rest of the query in memory. In this way, the driver can execute unsupported predicates, joins, and aggregation.
When SupportEnhancedSQL = false, the driver limits SQL execution to what is supported by the LDAP API.
Execution of Predicates
The driver 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 driver 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 driver retrieves all rows necessary to process the aggregation in memory.
Timeout
Data Type
int
Default Value
60
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 driver throws an exception.
UseConnectionPooling
Data Type
bool
Default Value
false
Remarks
This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.
TablesBack To Top
Group
Stores a list of user names. Used to apply security principals on resources.
Table Specific Information
Select
All columns support server-side processing for the operators =, >= , <=, !=, LIKE, AND, and OR. Other filters are executed client side within the driver. For example, the following query is processed by LDAP:
SELECT * FROM Group WHERE GroupType != '-2147483644' AND ObjectClass = 'top;group' LIMIT 5You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refers to any other operators will cause an error.
Columns
| Name | Type | ReadOnly | References | DataFormat | Description |
| Id [KEY] | String | True |
Combined index and DN. Multiple indices are only possible when a column is set to SplitDataByRow. | ||
| DN | String | True |
The full distinguished name. | ||
| RDN | String | False |
The relative distinguished name. | ||
| BaseDN | String | True |
The base distinguished name. | ||
| GroupType | String | False | DelimitedData |
Contains a set of flags that define the type and scope of a group object. For the possible values for this attribute, see Remarks. | |
| ObjectClass | String | False | SplitDataByRow |
The list of classes from which this class is derived. | |
| Member | String | False | DelimitedData |
The list of users that belong to the group. | |
| NTGroupMembers | String | False | DelimitedData |
This attribute is not used. | |
| OperatorCount | String | False | DelimitedData |
Operator count. | |
| AdminCount | String | False | DelimitedData |
Indicates that a given object has had its ACLs changed to a more secure value by the system because it was a member of one of the administrative groups (directly or transitively). | |
| GroupAttributes | String | False | DelimitedData |
The Group-Attributes attribute is not currently used. | |
| GroupMembershipSAM | String | False | DelimitedData |
Windows NT Security. Down level Windows NT support. | |
| ControlAccessRights | String | False | DelimitedData |
Used by DS Security to determine which users can perform specific operations on the host object. | |
| DesktopProfile | String | False | DelimitedData |
The location of the desktop profile for a user or group of users. Not used. | |
| NonSecurityMember | String | False | DelimitedData |
Nonsecurity members of a group. Used for Exchange distribution lists. | |
| ManagedBy | String | False | DelimitedData |
The distinguished name of the user that is assigned to manage this object. | |
| PrimaryGroupToken | String | False | DelimitedData |
A computed attribute that is used in retrieving the membership list of a group, such as Domain Users. The complete membership of such groups is not stored explicitly for scaling reasons. | |
| String | False | DelimitedData |
The list of email addresses for a contact. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Filter | String |
Defines the LDAP filter explicitly, overriding any other values set in the WHERE clause. |
OrganizationalPerson
This class is used for objects that contain organizational information about a user, such as the employee number, department, manager, title, office address, and so on.
Table Specific Information
Select
All columns support server-side processing for the operators =, >= , <=, !=, LIKE, AND, and OR. Other filters are executed client side within the driver. For example, the following query is processed by LDAP:
SELECT * FROM OrganizationalPerson WHERE CN != 'NewUser' AND BaseDN = 'CN=Users,DC=MyDC' LIMIT 5You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refer to any other operators will cause an error.
Columns
| Name | Type | ReadOnly | References | DataFormat | Description |
| Id [KEY] | String | True |
Combined index and DN. Multiple indices are only possible when a column is set to SplitDataByRow. | ||
| DN | String | True |
The full distinguished name. | ||
| RDN | String | False |
The relative distinguished name. | ||
| BaseDN | String | True |
The base distinguished name. | ||
| ObjectClass | String | False | DelimitedData |
The list of classes from which this class is derived. | |
| SN | String | False | DelimitedData |
This attribute contains the family or last name for a user. | |
| CN | String | False | DelimitedData |
The name that represents an object. Used to perform searches. | |
| UserPassword | String | False | DelimitedData |
The user's password in UTF-8 format. This is a write-only attribute. | |
| TelephoneNumber | String | False | DelimitedData |
The primary telephone number. | |
| SeeAlso | String | False | DelimitedData |
List of distinguished names that are related to an object. | |
| Description | String | False | DelimitedData |
Contains the description to display for an object. This value is restricted as single-valued for backward compatibility in some cases but is allowed to be multi-valued in others. See Remarks. | |
| Title | String | False | DelimitedData |
Contains the user's job title. This property is commonly used to indicate the formal job title, such as Senior Programmer, rather than occupational class, such as programmer. It is not typically used for suffix titles such as Esq. or DDS. | |
| X121Address | String | False | DelimitedData |
The X.121 address for an object. | |
| RegisteredAddress | String | False | DelimitedData |
Specifies a mnemonic for an address associated with an object at a particular city location. The mnemonic is registered in the country/region in which the city is located and is used in the provision of the Public Telegram Service. | |
| DestinationIndicator | String | False | DelimitedData |
This is part of the X.500 specification and not used by NTDS. | |
| PreferredDeliveryMethod | String | False | DelimitedData |
The X.500-preferred way to deliver to addressee. | |
| TelexNumber | String | False | DelimitedData |
A list of alternate telex numbers. | |
| TeletexTerminalIdentifier | String | False | DelimitedData |
Specifies the Teletex terminal identifier and, optionally, parameters, for a teletex terminal associated with an object. | |
| InternationalISDNNumber | String | False | DelimitedData |
Specifies an International ISDN Number associated with an object. | |
| FacsimileTelephoneNumber | String | False | DelimitedData |
Contains telephone number of the user's business fax machine. | |
| Street | String | False | DelimitedData |
The street address. | |
| PostOfficeBox | String | False | DelimitedData |
The post office box number for this object. | |
| PostalCode | String | False | DelimitedData |
The postal or zip code for mail delivery. | |
| PostalAddress | String | False | DelimitedData |
The mailing address for the object. | |
| PhysicalDeliveryOfficeName | String | False | DelimitedData |
Contains the office location in the user's place of business. | |
| OU | String | False | DelimitedData |
The name of the organizational unit. | |
| ST | String | False | DelimitedData |
The name of a user's state or province. | |
| L | String | False | DelimitedData |
Represents the name of a locality, such as a town or city. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Filter | String |
Defines the LDAP filter explicitly, overriding any other values set in the WHERE clause. |
Person
Contains personal information about a user.
Table Specific Information
Select
All columns support server-side processing for the operators =, >= , <=, !=, LIKE, AND, and OR. Other filters are executed client side within the driver. For example, the following query is processed by LDAP:
SELECT * FROM Person WHERE ObjectClass = 'top' AND CN LIKE '%NewUser%' LIMIT 5You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refer to any other operators will cause an error.
Columns
| Name | Type | ReadOnly | References | DataFormat | Description |
| Id [KEY] | String | True |
Combined index and DN. Multiple indices are only possible when a column is set to SplitDataByRow. | ||
| DN | String | True |
The full distinguished name. | ||
| RDN | String | False |
The relative distinguished name. | ||
| BaseDN | String | True |
The base distinguished name. | ||
| ObjectClass_1 | String | False | SplitDataByCol |
The list of classes from which this class is derived. | |
| ObjectClass_2 | String | False | SplitDataByCol |
The list of classes from which this class is derived. | |
| ObjectClass_3 | String | False | SplitDataByCol |
The list of classes from which this class is derived. | |
| ObjectClass_4 | String | False | SplitDataByCol |
The list of classes from which this class is derived. | |
| SN | String | False | DelimitedData |
This attribute contains the family or last name for a user. | |
| CN | String | False | DelimitedData |
The name that represents an object. Used to perform searches. | |
| UserPassword | String | False | DelimitedData |
The user's password in UTF-8 format. This is a write-only attribute. | |
| TelephoneNumber | String | False | DelimitedData |
The primary telephone number. | |
| SeeAlso | String | False | SplitDataByRow |
List of distinguished names that are related to an object. | |
| Description | String | False | DelimitedData |
Contains the description to display for an object. This value is restricted as single-valued for backward compatibility in some cases but is allowed to be multi-valued in others. See Remarks. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Filter | String |
Defines the LDAP filter explicitly, overriding any other values set in the WHERE clause. |
Top
The top level class from which all classes are derived.
Table Specific Information
Select
All columns support server-side processing for the following operators =, >= , <=, !=, LIKE, AND, and OR. Other filters are executed client side within the driver. For example, the following query is processed by LDAP:
SELECT * FROM Top WHERE CN != 'NewUser' AND BaseDN = 'CN=Users,DC=MyDC' LIMIT 5You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refer to any other operators will cause an error.
Columns
| Name | Type | ReadOnly | References | DataFormat | Description |
| Id [KEY] | String | True |
Combined index and DN. Multiple indices are only possible when a column is set to SplitDataByRow. | ||
| DN | String | True |
The full distinguished name. | ||
| RDN | String | False |
The relative distinguished name. | ||
| BaseDN | String | True |
The base distinguished name. | ||
| ObjectClass | String | False | DelimitedData |
The list of classes from which this class is derived. | |
| SN | String | False | DelimitedData |
This attribute contains the family or last name for a user. | |
| CN | String | False | DelimitedData |
The name that represents an object. Used to perform searches. | |
| GroupType | String | False | DelimitedData |
Contains a set of flags that define the type and scope of a group object. | |
| MemberOf | String | False | DelimitedData |
The distinguished name of the groups to which this object belongs. | |
| DC | String | False | DelimitedData |
The attribute DC for the Domain object class. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Filter | String |
Defines the LDAP filter explicitly, overriding any other values set in the WHERE clause. |
User
This class is used to store information about an employee or contractor who works for an organization. It is also possible to apply this class to long term visitors.
Table Specific Information
Select
All columns support server-side processing for the operators =, >= , <=, !=, LIKE, AND, and OR. Other filters are executed client side within the driver. For example, the following query is processed by LDAP:
SELECT * FROM User WHERE Title Like '%abc%' AND AdminCount != '1' LIMIT 5You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that refer to any other operators will cause an error.
Columns
| Name | Type | ReadOnly | References | DataFormat | Description |
| Id [KEY] | String | True |
Combined index and DN. Multiple indices are only possible when a column is set to SplitDataByRow. | ||
| DN | String | True |
The full distinguished name. | ||
| RDN | String | False |
The relative distinguished name. | ||
| BaseDN | String | True |
The base distinguished name. | ||
| ObjectClass | String | False | DelimitedData |
The list of classes from which this class is derived. | |
| SN | String | False | DelimitedData |
This attribute contains the family or last name for a user. | |
| CN | String | False | DelimitedData |
The name that represents an object. Used to perform searches. | |
| UserPassword | String | False | DelimitedData |
The user's password in UTF-8 format. This is a write-only attribute. | |
| TelephoneNumber | String | False | DelimitedData |
The primary telephone number. | |
| SeeAlso | String | False | DelimitedData |
List of distinguished names that are related to an object. | |
| Description | String | False | DelimitedData |
Contains the description to display for an object. This value is restricted as single-valued for backward compatibility in some cases but is allowed to be multi-valued in others. See Remarks. | |
| Title | String | False | DelimitedData |
Contains the user's job title. This property is commonly used to indicate the formal job title, such as Senior Programmer, rather than occupational class, such as programmer. It is not typically used for suffix titles such as Esq. or DDS. | |
| X121Address | String | False | DelimitedData |
The X.121 address for an object. | |
| RegisteredAddress | String | False | DelimitedData |
Specifies a mnemonic for an address associated with an object at a particular city location. The mnemonic is registered in the country/region in which the city is located and is used in the provision of the Public Telegram Service. | |
| DestinationIndicator | String | False | DelimitedData |
This is part of the X.500 specification and not used by NTDS. | |
| PreferredDeliveryMethod | String | False | DelimitedData |
The X.500-preferred way to deliver to addressee. | |
| TelexNumber | String | False | DelimitedData |
A list of alternate telex numbers. | |
| TeletexTerminalIdentifier | String | False | DelimitedData |
Specifies the Teletex terminal identifier and, optionally, parameters, for a teletex terminal associated with an object. | |
| InternationalISDNNumber | String | False | DelimitedData |
Specifies an International ISDN Number associated with an object. | |
| FacsimileTelephoneNumber | String | False | DelimitedData |
Contains telephone number of the user's business fax machine. | |
| Street | String | False | DelimitedData |
The street address. | |
| PostOfficeBox | String | False | DelimitedData |
The post office box number for this object. | |
| PostalCode | String | False | DelimitedData |
The postal or zip code for mail delivery. | |
| PostalAddress | String | False | DelimitedData |
The mailing address for the object. | |
| PhysicalDeliveryOfficeName | String | False | DelimitedData |
Contains the office location in the user's place of business. | |
| OU | String | False | DelimitedData |
The name of the organizational unit. | |
| ST | String | False | DelimitedData |
The name of a user's state or province. | |
| L | String | False | DelimitedData |
Represents the name of a locality, such as a town or city. | |
| UserCertificate | String | False | DelimitedData |
Contains the DER-encoded X.509v3 certificates issued to the user. Note that this property contains the public key certificates issued to this user by Microsoft Certificate Service. | |
| NetworkAddress | String | False | DelimitedData |
The TCP/IP address for a network segment. Also called the subnet address. | |
| UserAccountControl | String | False | DelimitedData |
Flags that control the behavior of the user account. | |
| BadPwdCount | String | False | DelimitedData |
The number of times the user tried to log on to the account using an incorrect password. A value of 0 indicates that the value is unknown. | |
| CodePage | String | False | DelimitedData |
Specifies the code page for the user's language of choice. This value is not used by Windows 2000. | |
| HomeDirectory | String | False | DelimitedData |
The home directory for the account. If homeDrive is set and specifies a drive letter, homeDirectory must be a UNC path. Otherwise, homeDirectory is a fully qualified local path including the drive letter (for example, DriveLetter:\Directory\Folder). This value can be a null string. | |
| HomeDrive | String | False | DelimitedData |
Specifies the drive letter to which to map the UNC path specified by homeDirectory. The drive letter must be specified in the form DriveLetter: where DriveLetter is the letter of the drive to map. The DriveLetter must be a single, uppercase letter and the colon (:) is required. | |
| BadPasswordTime | String | False | DelimitedData |
The last time and date that an attempt to log on to this account was made with a password that is not valid. This value is stored as a large integer that represents the number of 100-nanosecond INTEGERs since January 1, 1601 (UTC). A value of zero means that the last time a incorrect password was used is unknown. | |
| LastLogoff | String | False | DelimitedData |
This attribute is not used. | |
| LastLogon | String | False | DelimitedData |
The last time the user logged on. This value is stored as a large integer that represents the number of 100-nanosecond INTEGERs since January 1, 1601 (UTC). A value of zero means that the last logon time is unknown. | |
| DBCSPwd | String | False | DelimitedData |
The account's LAN Manager password. | |
| LocaleID | String | False | DelimitedData |
This attribute contains a list of locale IDs supported by this application. A locale ID represents a geographic location, such as a country/region, city, county, and so on. | |
| ScriptPath | String | False | DelimitedData |
This attribute specifies the path for the user's logon script. The string can be null. | |
| LogonHours | String | False | DelimitedData |
The hours that the user is allowed to logon to the domain. | |
| LogonWorkstation | String | False | DelimitedData |
This attribute is not used. See the User-Workstations attribute. | |
| MaxStorage | String | False | DelimitedData |
The maximum amount of disk space the user can use. Use the value specified in USER_MAXSTORAGE_UNLIMITED to use all available disk space. | |
| UserWorkstations | String | False | DelimitedData |
Contains the NetBIOS or DNS names of the computers running Windows NT Workstation or Windows 2000 Professional from which the user can log on. Each NetBIOS name is separated by a comma. Multiple names should be separated by commas. | |
| UnicodePwd | String | False | DelimitedData |
The password of the user in Windows NT one-way format (OWF). Windows 2000 uses the Windows NT OWF. This property is used only by the operating system. Note that you cannot derive the clear password back from the OWF form of the password. | |
| OtherLoginWorkstations | String | False | DelimitedData |
Non-Windows NT or LAN Manager workstations from which a user can log on. | |
| NtPwdHistory | String | False | DelimitedData |
The password history of the user in Windows NT one-way format (OWF). Windows 2000 uses the Windows NT OWF. | |
| PwdLastSet | String | False | DelimitedData |
The date and time that the password for this account was last changed. This value is stored as a large integer that represents the number of 100 nanosecond INTEGERs since January 1, 1601 (UTC). If this value is set to 0 and the User-Account-Control attribute does not contain the UF_DONT_EXPIRE_PASSWD flag, then the user must set the password at the next logon. | |
| PreferredOU | String | False | DelimitedData |
The Organizational Unit to show by default on user' s desktop. | |
| PrimaryGroupID | String | False | DelimitedData |
Contains the relative identifier (RID) for the primary group of the user. By default, this is the RID for the Domain Users group. | |
| UserParameters | String | False | DelimitedData |
Parameters of the user. Points to a Unicode string that is set aside for use by applications. This string can be a null string, or it can have any number of characters before the terminating null character. Microsoft products use this member to store user data specific to the individual program. | |
| ProfilePath | String | False | DelimitedData |
Specifies a path to the user's profile. This value can be a null string, a local absolute path, or a UNC path. | |
| OperatorCount | String | False | DelimitedData |
Operator count. | |
| AdminCount | String | False | DelimitedData |
Indicates that a given object has had its ACLs changed to a more secure value by the system because it was a member of one of the administrative groups (directly or transitively). | |
| AccountExpires | String | False | DelimitedData |
The date when the account expires. This value represents the number of 100-nanosecond INTEGERs since January 1, 1601 (UTC). A value of 0 or 0x7FFFFFFFFFFFFFFF (9223372036854775807) indicates that the account never expires. | |
| LmPwdHistory | String | False | DelimitedData |
The password history of the user in LAN Manager (LM) one-way format (OWF). The LM OWF is used for compatibility with LAN Manager 2.x clients, Windows 95, and Windows 98. | |
| GroupMembershipSAM | String | False | DelimitedData |
Windows NT Security. Down level Windows NT support. | |
| LogonCount | String | False | DelimitedData |
The number of times the account has successfully logged on. A value of 0 indicates that the value is unknown. | |
| ControlAccessRights | String | False | DelimitedData |
Used by DS Security to determine which users can perform specific operations on the host object. | |
| DefaultClassStore | String | False | DelimitedData |
The default Class Store for a given user. | |
| GroupsToIgnore | String | False | DelimitedData |
The Groups-to-Ignore attribute is not currently used. | |
| GroupPriority | String | False | DelimitedData |
The Group-Priority attribute is not currently used. | |
| DesktopProfile | String | False | DelimitedData |
The location of the desktop profile for a user or group of users. Not used. | |
| DynamicLDAPServer | String | False | DelimitedData |
DNS name of server handing dynamic properties for this account. | |
| UserPrincipalName | String | False | DelimitedData |
This attribute contains the UPN that is an Internet-style login name for a user based on the Internet standard RFC 822. The UPN is shorter than the distinguished name and easier to remember. By convention, this should map to the user email name. The value set for this attribute is equal to the length of the user's ID and the domain name. For more information about this attribute, see User Naming Attributes. | |
| LockoutTime | String | False | DelimitedData |
The date and time (UTC) that this account was locked out. This value is stored as a large integer that represents the number of 100-nanosecond INTEGERs since January 1, 1601 (UTC). A value of zero means that the account is not currently locked out. | |
| UserSharedFolder | String | False | DelimitedData |
Specifies a UNC path to the user's shared documents folder. The path must be a network UNC path of the form \\Server\Share\Directory. This value can be a null string. | |
| UserSharedFolderOther | String | False | DelimitedData |
Specifies a UNC path to the user's additional shared documents folder. The path must be a network UNC path of the form \\Server\Share\Directory. This value can be a null string. | |
| ServicePrincipalName | String | False | DelimitedData |
List of principal names used for mutual authentication with an instance of a service on this computer. | |
| ACSPolicyName | String | False | DelimitedData |
String name of an ACS policy that applies to this user. | |
| TerminalServer | String | False | DelimitedData |
Opaque data used by the Windows NT terminal server. | |
| MSMQSignCertificates | String | False | DelimitedData |
This attribute contains a number of certificates. A user can generate a certificate per computer. For each certificate we also keep a digest. | |
| MSMQDigests | String | False | DelimitedData |
An array of digests of the corresponding certificates in attribute mSMQ-Sign-Certificates. They are used for mapping a digest into a certificate. | |
| MSMQDigestsMig | String | False | DelimitedData |
In MSMQ mixed-mode, contains the previous value of mSMQDigests. | |
| MSMQSignCertificatesMig | String | False | DelimitedData |
In MSMQ mixed-mode, the attribute contains the previous value of mSMQSignCertificates. MSMQ supports migration from the MSMQ 1.0 DS to the Windows 2000 DS, and mixed mode specifies a state in which some of the DS severs were not upgraded to Windows 2000. | |
| MsNPAllowDialin | String | False | DelimitedData |
Indicates whether the account has permission to dial in to the RAS server. Do not modify this value directly. Use the appropriate RAS administration function to modify this value. | |
| MsNPCallingStationID | String | False | DelimitedData |
The msNPCallingStationID attribute is used internally. Do not modify this value directly. | |
| MsNPSavedCallingStationID | String | False | DelimitedData |
The msNPSavedCallingStationID attribute is used internally. Do not modify this value directly. | |
| MsRADIUSCallbackNumber | String | False | DelimitedData |
The msRADIUSCallbackNumber attribute is used internally. Do not modify this value directly. | |
| MsRADIUSFramedIPAddress | String | False | DelimitedData |
The msRADIUSFramedIPAddress attribute is used internally. Do not modify this value directly. | |
| MsRADIUSFramedRoute | String | False | DelimitedData |
The msRADIUSFramedRoute attribute is used internally. Do not modify this value directly. | |
| MsRADIUSServiceType | String | False | DelimitedData |
The msRADIUSServiceType attribute is used internally. Do not modify this value directly. | |
| MsRASSavedCallbackNumber | String | False | DelimitedData |
The msRASSavedCallbackNumber attribute is used internally. Do not modify this value directly. | |
| MsRASSavedFramedIPAddress | String | False | DelimitedData |
The msRASSavedFramedIPAddress attribute is used internally. Do not modify this value directly. | |
| MsRASSavedFramedRoute | String | False | DelimitedData |
The msRASSavedFramedRoute attribute is used internally. Do not modify this value directly. | |
| MS-DS-CreatorSID | String | False | DelimitedData |
The security ID of the creator of the object that contains this attribute. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Filter | String |
Defines the LDAP filter explicitly, overriding any other values set in the WHERE clause. |