This document is meant to supplement the TM1 9.5 Operations Guide as it describes the task of configuring LDAP authentication for TM1 9.5 in greater detail. It adds value though as it covers documentation gaps, documents yet undocumented configuration items and helps to overcome pitfalls not mentioned in documentation at all.
The configurations described in this document apply to all versions of Microsoft Windows and TM1 9.5 only. While the concepts may work in earlier versions of TM1 they have not been verified by the author.
So far the following LDAP servers have been verified to work in conjunction with TM1 9.5: OpenLDAP, Sun ONE LDAP, Novell eDirectory, Tivoli Directory Services, Microsoft Active Directory. While there is no official statement about the support of LDAP servers it's safe to assume that all LDAP V3 compliant LDAP servers will work.
TM1 9.5 does currently not support LDAP authentication on any other platform than Microsoft Windows. In particular it's impossible to get this working on UNIX as there is no LDAP client API available in TM1 9.5 on those platforms.
The document will refer to concepts of Public Key Infrastructures (PKI) like certificates, Certifying Authorities and key- and truststores. The reader is expected to be familiar with those concepts as the document won't explain them.
When TM1 is configured to use LDAP authentication it will verify user credentials against the configured LDAP server. However IBM Cognos TM1 does not support a stand-alone LDAP configuration. As pointed out in the Operations Guide Chapter 7, TM1 will always use a look-up in the TM1 database (it's internal security) too. Technically the steps are
- TM1 users log in through any client and present credentials.
- TM1 provides the credentials to the configured LDAP server for verification
- If LDAP server accepts credentials TM1 checks whether the provided user name has a matching entry in the }Clients.dim. If so the user is logged in to TM1.
With that being stated it evolves that before being able to use LDAP authentication users and groups from the LDAP must be imported into the TM1 database. This is handled by the ETLDAP tool.
The ETLDAP tool will create user name entries in the }Clients.dim and entries in the }ClientProperties.dim.
IBM Cognos TM1 makes use of Secure Socket Layer (SSL) encrypted connections for two purposes.
First there is the internal communication between the TM1 Server(s) and clients like Architect, Perspectives and the (web-)client. Since version 9.1 TM1 supports encrypting those connections by SSL which is considered best practice. (refer to TM1 Operations Guide, Chapter 1).
Second, for LDAP authentication there is communication between the TM1 server(s) and the LDAP server. The LDAP protocol too can be encrypted by SSL which is denoted as Lightweight Directory Access Protocol over SSL (LDAPS). As of version 9.1 TM1 requires LDAPS, the use of unencrypted LDAP is deprecated and unsupported as of that version. It's important to anticipate that communication to the LDAP is always handled by the TM1 server only, clients don't connect to the LDAP directly.
Figure 1 - SSL Communication in TM1 9.5
The internal SSL communication by default is based on Keys created by TM1 and certificates signed by the Certifying Authority (CA) built into TM1. They get stored to the <TM1 ROOT>/bin/ssl folder by default. At the same time this implies that the server certificates used for the internal SSL are trusted by all TM1 components. For this trust, a client must trust the CA which signed the server certificates. For internal SSL that is the "Applix CA" (the name the TM1 built-in CA uses) of course. Since TM1 leverages the Windows Operating System cryptographic functions to verify SSL trust this implies importing the <Applix CA> certificate into the Windows LocalComputer truststore as a trusted root authority. This is done by the installer automatically so after installation the system is completely configured for internal SSL.
It is suggested though to replace the default certificates by certificates signed by a commercial CA for production use. Doing so requires providing TM1 with a server certificate, the private key, the CA certificate which signed them and a Certificate Revocation List (CRL) certificate for that CA. Due to the fact that TM1 uses the Windows API for cryptographic functionality that new CA certificate is not yet present in the Windows truststore and has to be imported manually (refer TM1 9.5 Operations Guide , Chapter 12: "Running TM1 in Secure Mode Using SSL", Paragraph: "Using Independent Certificates"). If this is a CA certificate of a commercial CA like Thawte or Verisign there is a good chance these CAs are already trusted by windows as even Windows comes with some pre-installed CA certificates in it's truststore. To check use the Certificates snap-in for Microsoft Management Console (MMC) and view the <Trusted Root Certification Authorities> node like shown below.
Figure 2 - Windows Truststore with Trusted CA certificates
The screenshot shows all trusted root CA certificates in a default Windows 2003 Server plus two additional certificate which have been added so far (CS Germany CA and Applix).
Note: There is an undocumented tool "
importsslcert.exe" in the <TM1_ROOT>/bin/ssl folder which automates the process of importing a certificate into the windows truststore. It's used by TM1 internally but it can do the job for any certificate and saves the hassle of using the MMC Snap-in.
Only after importing the certificate into the Windows truststore TM1 Server will be able to use the server certificate to establish the SSL socket.
If now focussing on external SSL the same concept applies. For TM1 Server to trust the server certificate presented by the LDAP server upon SSL connect the certificate of the CA which signed the LDAP server certificate must be imported into the Windows truststore as well.
If the LDAP server is using a self-signed certificate (a certificate where subject and issuer are identical and which does not have the "is CA" attribute set) it serves as server AND CA certificate at the same time, so the server certificate has to be imported to the windows truststore. Mind that self-signed certificates are considered bad practice for production servers and certificates signed by a CA should be used instead.
Since the LDAP server is an external 3rd party software the configuration of SSL support is outside of TM1's scope. A TM1 administrator must obtain some information from the LDAP Administrator.
The ETLDAP utility used to run the initial user import to TM1 is coded in JAVA and hence leverages other truststores and APIs to establish an SSL connection. Extra steps are required to connect ETLDAP by LDAPS. The Operations Guide describes them though there is a small glitch in documentation which is pointed out in the next section. For sake of understanding the bigger picture it's sufficient to anticipate that ETLDAP uses the "external SSL" connection.
While the Operations Guide of TM1 9.5 covers the most basic concepts and tasks of configuring LDAP authentication it unfortunately misses out on mentioning some important/interesting configuration options. Some other steps which are required may pose a challenge if not implemented correctly.
This section provides a step-by-step walk-through in configuring LDAP authentication for TM1 9.5. It has worked several times for different LDAP servers and installs. It is a proven practice.
The first step before even installing TM1 9.5 is to verify all the required information about the LDAP server is available and it's possible to connect to it via LDAPS.
- For a start the first piece of information required is the type of LDAP server. This can be any LDAP V3 compliant LDAP server like Tivoli Directory Service (TDS), OpenLDAP, SunONE LDAP and even including Microsoft Active Directory (AD). Very roughly spoken AD really is "just an LDAP" built into Windows - heavily extended by Microsoft, so it can be used for LDAP authentication to TM1 9.5. The type of LDAP will determine the attributes of a user entry which potentially could be used for TM1 login names and, in case of Active Directory, how TM1 server authenticates to the LDAP server. For AD there is a special Single Sign-On feature available, it will be discussed later.
- Second the connection information of the LDAP is required. This includes
- host IP, Netbios name or preferably the fully qualified DNS name.
Tip: In case of AD using the domain name is valid as well and is a best practice as this will allow to leverage the Windows DC Locator feature (AD failover support).
- port , LDAPS by default uses port 636
- Base Distinguished Name (BaseDN), something like o=Business Analytics,dc=ibm.com
- host IP, Netbios name or preferably the fully qualified DNS name.
- Third, and most important, the information whether the LDAP server uses a self signed certificate or a certificate signed by some CA is required.
In case of a self signed certificate the server certificate is required.
In case of a CA signed certificate, the CA certificate of the CA which signed the server certificate is required.
Obtain either certificate in Privacy Enhanced Mail (PEM) format (Base64 encoded ASCII).
- Last piece of information required are some Binding Credentials (BC), basically an LDAP account which can be used to bind to the directory and search it. This BC need to have browsing and read access to at least all the user entries which should be imported into TM1.
In case of Active Directory the BC can be some domain user account, note the account.
After obtaining all this information it's a good idea to verify some arbitrary LDAP client can connect to the LDAP via SSL. Use an LDAP browser of your choice and connect to the LDAP using the host, port ,BaseDN and BC collected. IF the connection works continue, if not try troubleshooting the connection issue. TM1 won't be able to connect if an LDAP client can't.
A good test is to use the "LDP" utility which is part of the Windows Server Tool package from Microsoft (included in Win2008 server by default). Another LDAP client with SSL support is Apache Directory Studio (Open Source) or Softerra LDAP browser (commercial).
It's even possible to test with the OpenSSL toolkit using the "
s_client -connect host:port -ca <
If not already done, install TM1 9.5 running the install wizard. Make sure to select the LDAP Authentication check box.
Figure 3 - TM1 Installation Wizard - LDAP Authentication option checked
Provide the information gathered about the LDAP in the previous step. The actual value for LDAP search field depends on your type of LDAP. It can be changed later if you're unsure. This will create the required entries in the server's tm1s.cfg file.
If TM1 has been installed already then tm1s.cfg will already have some settings in place for the currently configured authentication mode. The settings for LDAP must be added manually in this case. Mind that depending on the current authentication mode additional configuration settings might be required for ETLDAP so it can connect to the TM1 server.
As mentioned in section 2.1 to use LDAP authentication for TM1 one of the prerequisite steps is to run the ETLDAP utility to create users and groups in the TM1 database. ETLDAP connects to the LDAP and TM1 and writes data to the TM1 database which it has extracted from the LDAP. It is coded in JAVA and hence uses a JAVA API to handle cryptographic functions. ETLDAP does not use the Windows truststores like TM1 server but JAVA truststores. Thus an additional step is required to establish ETLDAP's trust to the LDAP server's certificate.
In addition several settings are required to achieve a successful import.
Before starting with ETLDAP ensure that in the
tm1s.cfg file for your Server the property for the password source is set to TM1 (
PasswordSource=TM1) as pointed out in the Operations Guide, Chapter 10, "Configuring LDAP validation". It cannot be switched to LDAP yet because there are no users imported into TM1 yet and ETLDAP must be able to connect to TM1 to import the users and groups.
Make ETLDAP trust the LDAP server's certificate
ETLDAP is invoked via a Microsoft Windows command file which calls a Java Runtime Environment (JRE) and explicitly passes the name and location of a truststore (a file containing certificates which should be trusted). By default the truststore being referenced is the one used for internal SSL, it's being passed in two parameters passed to the JRE like this
The truststore is <TM1_ROOT>/bin/ssl/tm1store and it's password is "applix".
While it's fine to change those parameters in <TM1_ROOT>/bin/etldap.cmd to use a separate truststore it's easiest to simply add the certificate required for ETLDAP's trust to the truststore for internal SSL. There are no adverse effects to the internal SSL nor does it raise security concerns, hence this is what is described below. If you absolutely want to use a separate truststore for ETLDAP adjust the batch file and don't forget to import the Applix CA certificate into it as well, without it ETLDAP won't be able to connect to TM1.
To establish trust, the CA certificate which signed the LDAP server certificate or the server certificate itself (in case it's self signed) must be imported into the tm1store. To import issue the following command (mind there is a WRONG path in the Operations Guide, the glitch has been reported and will be fixed in the future) while being in the
keytool -import -keystore <TM1_ROOT>\bin\ssl\tm1store" -storepass applix -alias LDAPTrust -file <your_cert>
The tool will print the certificate and ask whether it should be imported. Press "y" and press ENTER . That's it, the certificate has been added and ETLDAP will trust each certificate presented to it which was signed using the imported certificate.
Trust for the LDAP server's certificate has hence been established now for the ETLDAP tool.
Connect to LDAP
Run the ETLDAP tool by starting <TM1_ROOT>\bin\etldap.cmd.
Figure 4 - The ETLDAP tool
Once the tool opens click Edit -> Login -> LDAP.
In the upcoming dialogue provide the LDAP connection information and BC gathered in section 3.1.
If the connection was established successfully, that is the SSL handshake worked, a message in green will indicate so. Press OK to complete the login.
Figure 5 - LDAP Login Dialogue - SSL option checked
If the connection cannot be established refer to the Troubleshooting section for hints. Basically ensure
- the certificate is correct
- the correct certificate has been imported into the truststore provided to ETLDAP in the command file
- the Binding Credentials are valid , test them using some other LDAP client software
- the LDAP server is up and running.
Connect to TM1
Make sure the Administration Server and the TM1 Server are started. By default the installer will have created two Windows Services for those. The account running those services is yet irrelevant.
Next, connect to TM1 by clicking Edit -> Login -> TM1.
Type in the host name of the box running Admin Server and click the button next to "Server". A dialog displaying all the active TM1 servers will appear. Select the TM1 server you want to connect to by double-clicking on it.
Finally provide user name and password, if this is a new server "admin" and "apple" will do.
Click Test. If the connection succeeds click OK to complete the login.
Figure 6 - TM1 Login Dialogue
If the connection cannot be established verify that
- TM1 Server and Administration server are running
- tm1s.cfg is configured for the correct authentication mode. This must not be LDAP yet.
Note: If some other authentication mode is used, ensure that all the proper settings are in place to allow ETLDAP to connect to TM1 server.
You're now connected to LDAP and TM1 and can start importing users from the LDAP.
Click File -> Connect. This connects ETLDAP to the LDAP.
The "Search" button will become enabled now.
The next step is to craft an LDAP search which will return a set of entries to import into TM1. This search consists of
- a "SearchDN" which is a DN where to start the search of
- a filter which defines which entries to find
- a comma separated list of attribute names which defines which attributes of the entries in the result set to display. (the DN of the entry is displayed by default).
- a search scope which defines whether child entries of the Search DN will be searched or not
Contact your LDAP administrator and/or refer to the Operations Guide for more information about searching LDAP servers.
Press "Search" to run the defined search.
Figure 7 - LDAP Load tool dialogue
The tool will display the result set, if any, in a list view where each column represents one attribute. By right-clicking on an entry one can display all attributes of the particular entry.
This is a preview of the data which potentially get's imported, however this is just informal to verify the search parameters. To define which attributes of an entry found by the search go in to TM1 another dialogue is used.
In the TM1 database each user has several properties. Each TM1 user has a unique user name, he belongs to at least one group and he may have an email address. To make LDAP authentication work those user properties must be populated with data read from the LDAP. The dialogue shown below allows for mapping attributes of an LDAP user to those properties.
To get to the mapping click Edit -> Mapping
Figure 8 - ETLDAP TM1 Mapping dialogue
First there is the TM1 user name ("client") a user provides to authenticate to TM1. It must be unique and is case-sensitive. The client field will be used to create the user in the }Clients.dim and }ClientProperties.dim. Choose an attribute which contains strings adhering to the required characteristics from the drop-down, mind that the attribute specified here determines what the user has to type in for login. The listed attributes depend on LDAP server type (the schemas supported by the LDAP) and BC permissions. Usual mapping is an attribute like "uid", "cn" or sAMAccountName for Active Directory but even "email" is fine if user's shall authenticate using their email to authenticate.
Note: In case no attributes appear in the drop-down refer to Troubleshooting section for a work-around or verify LDAP permissions of the BC in a third party LDAP browser tool. The BC must be able to view all the attributes of a user.
The next property is "group". This property denotes the name of a group the user is a member of and it's value will be used to create entries in the }Groups.dim. One may choose an LDAP attribute which categorizes the user into groups like a department ID, a building or location ID/name.
Finally one can specify an LDAP schema attribute to map to the email property of a TM1 user. Most LDAP server schemas support an attribute holding the user's email address. Map this one here and the users created in TM1 will have the email property populated.
Figure 9 - ETLDAP TM1 Mapping defined
When done click OK.
By now the "Export" button should have become activated and it's time to start the export to TM1. During the export ETLDAP will read data from the LDAP and create the users and groups in TM1. Verify the log by clicking View -> Log after the export to learn which users and groups have been created and which failed.
The assignment of users to groups will have to be done manually after the export though. For details refer to the TM1 9.5 operations Guide "Running the ETLDAP tool". This is of particular interest since most probably none of the imported users will be part of the ADMIN group, refer to section 3.3.7 for details.
Now after users have been created in TM1 the authentication for the TM1 server can eventually be switched to LDAP. For this the
Passwordsource property in TM1s.cfg must be changed to LDAP. Next the general connection information for the LDAP server must be provided as well.
However there are quite a few other things to be aware of. As explained in Section 2.2.1 the TM1 server will connect to the LDAP server to verify the user credentials. Since the LDAP server runs SSL the process of establishing a connection to it consists of two steps.
First the network connection must be established which in this case means network layer connection (which we assume is provided) and more important the SSL handshake. This handshake involves verification of the LDAP server's certificate and acknowledgement of the trust to it.
The second step is to bind to the LDAP server, that is authenticate and establish a session for retrieving data.
Both steps can configured by settings in the TM1s.cfg file and hence an administrator can tweak the processing of those steps to adjust to specific setups.
In general tm1s.cfg must be tailored in three areas which are discussed now.
After the tm1s.cfg file has been adjusted, don't forget to save it.
To enable LDAP authentication for TM1 edit the PasswordSource property to "LDAP".
Next provide the basic LDAP connection information in these properties
LDAPHost=<host> LDAPPort=<port> LDAPSearchBase=<baseDN> LDAPSearchField=<mapped client attribute>
Specify host and port, the default LDAPS port is 636 but it could be anything, depends on what port the LDAP server uses. The SearchBase should be the same DN used during running the ETLDAP tool, the BaseDN requested in 2.2.1. The LDAPSearchField must be the same LDAP schema attribute specified for the client mapping in 2.2.3.
Verifying the SSL certificate
TM1 leverages the Microsoft Windows API for cryptographic functions. This includes verification of SSL certificates. By default TM1 will open a secure socket to the configured host and port and expect the server on the other end to initiate an SSL handshake by presenting it's certificate. The certificate will then be passed to the Windows API for verification. This implies that the Windows operating system must be able to verify the trust relationship to the certificate. For this to work the CA certificate which signed the LDAP server certificate or, in case of a self signed certificate, the LDAP server's certificate itself, must be trusted by Windows which means they must be a “Trusted Root Certifying Authority”. After this trust is confirmed the Windows API will request the Certificate Revocation List (CRL) certificate for the Trusted Root CA if applicable to ensure the certificate is not already revoked. Only then it will signal to the caller that the presented certificate was successfully verified.
This most convenient handling of the SSL handshake can be overridden to address some challenges. TM1 expects the subject of the certificate received to match the server name of the configured endpoint. If there is a mismatch the verification will fail as TM1 requests the windows API to verify the received certificate against the configured server name. If the configured host and port don't actually represent an LDAP server but for example a proxy this will fail. For that reason it's possible to delegate the verification of the SSL certificate to TM1. This is achieved by adding
LDAPVerifyServerSSLCert=T to the tm1s.cfg. This will instruct TM1 to handle the verification itself.
If TM1 is handling the certificate validation it will process the two steps of verification (trust, CRL checking) like the Windows API would have done but uses a slightly different approach.
Instead of verifying the received certificate against the configured host name and validate trust it will first look at a list of server names. This list is a white list, meaning all server names which should be accepted are explicitly listed. The entries must exactly match the subject of the certificate presented to TM1 in the SSL handshake by the server on the other end. Per server there must be an entry like this:
LDAPVerifyCertServerName=<server_cert_subject>. Only if a match is found TM1 will continue to process. If the certificate subject matches one of the servers on the white list TM1 calls the Windows API explicitly asking to verify this single certificate only. Again this requires Windows to trust the certificate which implies to have the correct trusted root CA imported.
If for whatever reason that trust doesn't work one can skip the trust verification step by specifying
LDAPSkipSSLCertVerification=F. With this setting TM1 will not verify the server certificate at all but simply accept it.
Once trust is confirmed (or that test has been skipped) TM1 will want to verify the CRL certificate. This again works by calling the Windows API so the CRL certificate for the trusted root must have been imported to Windows. If that certificate doesn't exist in Window's truststore one must skip the CRL processing by specifying
LDAPSkipSSLCRLVerification=T otherwise TM1 will insist on checking the CRL. If
LDAPVerifyServerSSLCert=F (Window handles all) this is not required as the API in that case is smart enough to tolerate an empty or not existent CRL certificate.
If all the tests above signal success the SSL handshake is complete and TM1 will now try to authenticate to the LDAP server.
Authenticate to LDAP server
Authentication to the LDAP server can happen two ways.
If the LDAP server really is a Microsoft Active Directory then TM1 can use Windows integrated authentication protocol to authenticate to the LDAP (AD in this case). Theoretically this could work for other LDAP servers as well but it will require specific modules to be configured for the LDAP server. If unsure whether the targeted LDAP server supports integrated Windows authentication ask your LDAP administrator. For now we assume this only applies to Microsoft AD.
So if integrated Windows authentication should be used, the
LDAPUseServerAccount=T entry must be added to the tm1s.cfg. This will instruct TM1 to authenticate to the LDAP using Windows integrated authentication for the account running the TM1 server. That is, whichever account runs TM1 server will authenticate to the Active Directory and hence this account must have sufficient privileges in AD.
As an additional hint it's a best practice to specify the domain name for host rather than an actual host name. This way the Windows Domain Locator process can be leveraged which will route requests to the Domain controller "best suited" to handle it. This can be based on availability (fail-over), geographical proximity or load, a Windows Domain administrator can configure this. If a specific domain controller is referenced by it's IP or DNS requests will only handled by this single host.
Usually the mapping for clientID should be sAMAccountName which is the user's windows login name.
Example for tm1s.cfg attaching to some AD
PasswordSource=LDAP LDAPPort=636 LDAPHost=some.domain.com LDAPSearchBase=dc=some,dc=domain,dc=com LDAPSearchField=sAMAccountName LDAPUseServerAccount=T # Should TM1 verifiy the cert (=T)or Windows (=F)? LDAPVerifyServerSSLCert=F # only if LDAPVerifyServerSSLCert=T # verify based on a whitelist (=F)or simply default to true (=T) LDAPSkipSSLCertVerification=F # whitelist of accepted servers LDAPVerifyCertServerName=wcsfrkxp99.mydomain.com # skip CRL processing (=T) or process (=F) LDAPSkipSSLCRLVerification=T
In case the LDAP server does NOT support integrated Windows authentication tm1s.cfg must specify
LDAPUseServerAccount=F and explicitly provide Binding Credentials for the TM1 server to use. Those get specified in several properties however. First there is the absolute DN of the binding user, which TM1 labels as “Well Known User Name”. This is specified in
LDAPWellKnownUserName. The password for this user is not specified in the file in clear text. Rather the TM1 took tm1crypt must be used to store the password into an encrypted file.
Run the tm1crypt utility like this:
<TM1_ROOT>/bin/tm1crypt -pwd <password> -keyfile <key_output_file> -outfile <encrypted_output_file> [-validate]
For password specify the password for the WellKnownUser, so the password from the BC. For key_output_file chose a file name which indicates that this is a key for an encrypted file like "ldappasskey.dat". For encrypted_output_file chose a file name which indicates that this is an encrypted password file like "ldappass.dat".
In tm1s.cfg one specifies the encrypted file and the key file which holds the key used to encrypt the password in
LDAPPasswordKeyFile and the encrypted password file in
Example for tm1s.cfg attaching to OpenLDAP
PasswordSource=LDAP LDAPHost=wcsfrkxp99.mydomain.com LDAPPort=636 LDAPSearchBase=ou=people,dc=cognos,dc=com LDAPSearchField=uid LDAPUseServerAccount=F LDAPWellKnownUserName=cn=binduser,dc=cognos,dc=com LDAPPasswordFile=e:\cognos\tm1\9.5\bin\ssl\ldappass.dat LDAPPasswordKeyFile=e:\cognos\tm1\9.5\bin\ssl\ldappasskey.dat # Should TM1 verifiy the cert (=T)or Windows (=F)? LDAPVerifyServerSSLCert=F # only if LDAPVerifyServerSSLCert=T # verify based on a whitelist (=F)or simply default to true (=T) LDAPSkipSSLCertVerification=F # whitelist of accepted servers LDAPVerifyCertServerName=wcsfrkxp99.mydomain.com # skip CRL processing (=T) or process (=F) LDAPSkipSSLCRLVerification=T
Add a user to ADMIN group.
At this point everything is ready to recycle the TM1 server and activate the LDAP authentication. When doing so however, there won't be any administrator account available. This is because ETLDAP only created users and groups based on the LDAP data but it didn't assign any user to a group. Further more, the users which were assigned to the ADMIN group won't be accessible any more once authentication is switched to LDAP. It's hence necessary to promote at least one user to administrator before finally switching to LDAP authentication.
- To do so bring up Architect.
- Double-click the TM1 server to be switched to LDAP authentication
- Login as an administrator (admin/apple might do).
- Right-Click the server name and choose Security -> Clients/Groups
Figure 10 - Navigate to Security -> Clients/groups
- Find one of the newly created user imported form the LDAP and make him an administrator by checking the box for the ADMIN group. This account will now be your new Administration account.
- Close Architect
To finally activate the LDAP authentication for this server make sure the changes to tm1s.cfg have been saved and recycle the TM1 Admin Server and the TM1 server configured for LDAP authentication.
Import trusted root CA certificate into Windows truststore
The final step to complete the setup is to import the right certificate into the Windows truststore so that Windows trusts the server certificate presented by the LDAP server.
This can be done two ways:
- Using the Microsoft Windows certificate snap in for the Microsoft Management Console (MMC) either directly or via Internet Explorer Wizard.
- Use the
importsslcerttool from TM1
There is an undocumented tool which TM1 uses silently when setting up it's certificates for internal ssl called
importsslcert. This tool will place a certificate along with it's CRL certificate into Window's truststore. This is by far the easiest way to do it since there's basically no room for errors.
To call the tool go to <TM1_ROOT>/bin and call it like this
importsslcert -ca <certificate> [-crl <crl_certificate>]
For certificate provide the absolute path to the file containing the CA certificate (or the server certificate in case you use self-signed certificate). One can optionally provide a CRL certificate as well. The tool will print out either success or failure notices.
That's it, your done.
Use Windows Certificate snap-in
To importing a certificate into Windows as a trusted root CA is straight forward with one slight exception. One must be careful when Windows is asking about the certificate store to place the certificate in. There is a subtle difference between logical stores and physical stores. The Windows API expects certificates to be in the physical store on the local host. If for some reason they are not placed in there trust cannot be established although Windows may show the certificate as a trusted root CA.
To import using the Internet Explorer Browser
- open the browser
- go to Internet Options, Content tab
- Find the Certificates section and click on Certificates
- Click "Import..." , the Certificate Import Wizard will appear
- Click Next
- Browse to the certificate file to import and click Next
- The Certificate Store selection dialogue will come up. Make sure to check "Place all certificates in the following store" option. Then click Browse.
Figure 11 - Certificate Import Wizard
- In the upcoming dialogue, check the "show physical stores" option and navigate to Third Party Root Certification Authorities -> Local Computer and click on it to select it. Press OK.
Figure 12 - Select Certificate Store dialogue
- Back in the Certificate Import Wizard click Next
- On the final summary page click Finish
- Once the message box announcing the successful outcome of the import appeared the certificate should appear in the list on the "Trusted Root Certification Authorities".
This completes the import task.
Note: You can achieve the same by using the Certificates snap-in for the Microsoft MMC. Ensure that once n you selected the Trusted Root Certification Authorities element you click on View-> Options and specify the "physical certificate stores" option.
Figure 13 - certmgr View Options
Import the certificate into Trusted Root Certifying Authorities -> Local Computer -> Certificates folder.
Figure 14 - Trusted Root Certifying Authorities / Local Computer / Certificates
Now that the setup is complete you should be able to log in to TM1 using any client with your LDAP credentials. Mind that a TM1 administrator will yet have to map users to groups if not already completed earlier as the LDAP import step doesn't bring over the group memberships.
This section tries to provide answers to the most common issues observed when setting up LDAP authentication for TM1 9.5.
By far the most common error is the "Connection Error" with error code 0x51 /81. This almost always indicates SSL handshake issues. The below points may help resolving those.
When TM1 is configured to verify the certificate there is a logger which can be added to the the
tm1s-log.properties file. With this logger tm1.log will contain additional information about why the certificate validation failed or what else went wrong. If Windows is validating the server certificate (
LDAPVerifyServerSSLCert=F) the logger will only show LDAP ERROR: 0x51 - ldap_connect failed. In that case it's a good idea to have TM1 verify the SSL certificate (change to
LDAPVerifyServerSSLCert=T) and enable this logger to gather more information.
To enable the logger edit <TM1_SERVER_ROOT>/
Right after the line
log4j.logger.TM1=INFO, S1 add a new line like this
log4j.logger.TM1=INFO, S1 log4j.logger.TM1.LDAPAuth=DEBUG
Save the file and stop the tm1 server. Move out or delete the old tm1.log and restart the server. Retry the authentication and review the new error messages in the log.
To ensure the certificate presented by the LDAP server is actually valid try looking at it in either Windows (rename to *.cert to get it recognized by windows) or use some PKI tools to view it. OpenSSL is useful too. Things to look at are
- is the subject/CN the Netbios hostname or the fully qualified domain name of the LDAP server?
- Is this a self signed certificate? You would recognize a self signed certificate by the fact that subject and issuer fields contain the same string while there is no certificate attribute set indicating this is a CA certificate.
In case it is a self-signed make sure this certificate has been imported to Windows.
- Each certificate has an expiry. Make sure the certificate is NOT expired
- Ensure the certificate is not revoked. If the trusted root CA has a CRL provided check that the certificate is not listed there as being revoked.
- Can you connect to LDAP using other LDAP clients like Microsoft LDP, OpenSSL, Softerra LDAP...
A good approach to troubleshooting is to leverage the advanced settings in tm1s.cfg. Change the configuration to have TM1 process the certificate validation and skip certificate validation and CRL checking in a first step like this:
... # Should TM1 verifiy the cert (=T)or Windows (=F)? LDAPVerifyServerSSLCert=T # only if LDAPVerifyServerSSLCert=T # verify based on a whitelist (=F)or simply default to true (=T) LDAPSkipSSLCertVerification=T # whitelist of accepted servers LDAPVerifyCertServerName=wcsfrkxp99.mydomain.com # skip CRL processing (=T) or process (=F) LDAPSkipSSLCRLVerification=T
If this doesn't work the issue is not about SSL handshake but network connection or LDAP authentication for the BC or the Service Account. In case of using the ServerAccount (
LDAPUseServerAccount=T) switch to use explicit BC and run tm1crypt to generate the required files. Even for AD the explicit BC work and this rules out issues with the integrated Windows authentication.
If the above configuration worked, re-enable the SSLCertVerification and enable the LDAPAuth logger if not already in place. Scan the error code in tm1.log.
- LDAP ERROR: 0x800b0109
The following error stack indicates an issue with the white list of acceptable server names:
LDAP ERROR: 0x800b0109 - Error verifying server certificate chain validity LDAP ERROR: Error verifying server certificate no match for <server> LDAP ERROR: 0x51 - ldap_connect failed.
Basically this means that the subject of the presented server certificate (the one printed for <server> didn't match any entry in the white list. The next action is to obtain the certificate's subject string and copy and paste it to the white list in an entry of
- LDAP ERROR: 0x800b010f
The following error stack indicates the certificate is not trusted by Windows
LDAP ERROR: 0x800b010f - Error verifying server certificate chain validity LDAP ERROR: Error verifying server certificate no match for <server> LDAP ERROR: 0x51 - ldap_connect failed.
This indicates an issue with the trust to the LDAP server certificate by Windows. Suggested action is to ensure the certificate has been imported to Windows correctly , refer to section 2.2.6
- LDAP ERROR: 0x80092012
The following error stack indicates an issue with the CRL processing.
LDAP ERROR: 0x80092012 - Error verifying server certificate chain validity LDAP ERROR: Error verifying server certificate no match for <server> LDAP ERROR: 0x51 - ldap_connect failed.
Either the certificate is revoked or TM1 is looking for the CRL certificate but cannot find it in the Windows store. Suggested action is to skip the CRL processing (set
LDAPSkilSSLCRLVerification=T) of import the CRL certificate from the CA.
In case ETLDAP mapping dialogue drop-downs don't show any LDAP attributes to map this indicates a permission issue with the BC. If you cannot run ETLDAP using proper BC with sufficient privileges you can work around the issue by specifying the mapping in a configuration file instead. This requires to know the names of the attributes to map of course.
To do so, after having connected to the LDAP and having run the search go to
File -> save as
This will allow to save the configuration setting made in the ETLDAP tool to a file. Specify a descriptive name like ETLDAP.conf.
Open the saved configuration file and add the following settings or change if already existing.
mapval:tm1client || <client_mapping> mapval:tm1group || <group_mapping> mapval:rep.email || <email_mapping>
Save the file.
You may now re-Open ETLDAP and load the configuration file via File -> Open.