Which guidelines must my external database meet to be connected to DocuWare? (Oracle - MySQL - MSSQL)

Question:
Which guidelines must my external database meet to be connected to DocuWare?
 
Answer:
DocuWare supports three types of database engines: MySQL, Microsoft SQL, and Oracle. The default database that comes with the software is MySQL Engine. However, you can also use your own database engine instead of the one that comes with the software. Here are some guidelines for preparing the database server before installing or using DocuWare:
Note: If you are going to use the external database for installation (Autoindex or Select List) the same prerequisites apply.
 
External MySQL (Not DocuWare Internal Database) for Installation 

1. Have a look at the System Requirements to get more information about the DocuWare version and its supported database.
2. Download the MySQL GUI tools from www.mysql.com (optional). This tool will allow you to create backups, restores, and database maintenances.
3. Create a new user with same rights as the user root account. In MySQL Administration Tool, right-click on the user root and select the option “Clone user”.
4. The default port for this database is 3306.

1. Make sure the MSSQL server is up to date with its service packs (SP), especially if you are using an old version of Microsoft SQL Server.
2. Microsoft SQL Server has to be configured with a static port number for installation. Dynamic porting is not supported by DocuWare during the installation. Additionally, MSSQL Express Edition is not supported by DocuWare The default port for MSSQL is 1433.
   • To see if SQL server instance has been configured with static or dynamic port you can use the tool called "SQL Server 20xx Configuration Manager"
   • Also take a look at the "System Requirements" for each version of DocuWare. 
3. Microsoft SQL server has to be configured to accept SQL Authentication and Windows Authentication. During DocuWare installation, SQL authentication will be utilized. This is typically done in SQL Server Management Studio.

4. By opening “SQL Server Management Studio,” login into the SQL Server and create an SQL user with:
   • Securityadmin role rights
   • Sysadmin role rights
     • This SQL user must be able to create databases and objects with the database (tables, triggers, indexes, etc). 
5. This SQL user should also be mapped as securityadmin role for tempdb databases.
6. If your SQL server has multiple instances running, then you have to connect from DocuWare using the full instance name (E.g.: ServerName\InstanceName) during the installation.

Note: Once the database has been prepared, DocuWare Authentication server installation will connect to that database and create all necessary components required for the DocuWare software. If you are installing DocuWare in a different server, then you could test the connection by creating a test ODBC connection from that DocuWare server to the Microsoft SQL Database server (as test only).

​Oracle Database for Installation / Direct Connection (No longer supported in DocuWare 7.10)
First of all, if you are running Oracle in Windows, you cannot install DocuWare and Oracle database engine in the same server. 
In order to install DocuWare using an Oracle Database Engine, you need to prepare the database. This is valid both for new installations and upgrading from older versions of DocuWare to new versions. In some instances, you may have to remove the old Oracle Client and install the new version of Oracle Client. Look at the system requirements that Oracle Client is required for your version of DocuWare. 
In the Oracle environment, all objects created by the installation will reside under a single Oracle user schema. 

Until DocuWare 6.11:
Oracle Client has to be installed on the machine where DocuWare Servers will be installed. Please install the Oracle client under the Windows account (local or domain), which will be running the DocuWare services. 

• Make sure that you have an Oracle user who with DBA privileges at least for the installation (suggested). This user has to be able to create and manipulate any database objects within that database schema. Once the installation is completed, you can restrict its rights.
• 32-bit Oracle Data Access Components (ODAC) have to be installed first: ODAC 11.2 Release 5
  Install the following components:
  • ​Oracle Data Provider for .NET 4 - 11.2.0.3.0
  • Oracle Data Provider for .NET 2 - 11.2.0.3.0
  • Oracle Providers for ASP.NET 4 - 11.2.0.3.0
  • Oracle Providers for ASP.NET 2 -
  • Oracle Database Extensions for .NET 4 - 11.2.0.3.0 - for upgrade only
  • Oracle Database Extensions for .NET 2 - 11.2.0.3.0 - for upgrade only
  • Oracle Providers for OLE DB - 11.2.0.3.0
  • Oracle Objects for OLE - 11.2.0.3.0
  • Oracle Services for Microsoft Transaction Server - 11.2.0.3.0
  • Oracle ODBC Driver - 11.2.0.3.0
  • Oracle SQL Plus - 11.2.0.3.0
  • Oracle Instant Client - 11.2.0.3.0
     
• TNSNAMES.ORA file (required for installation or utilization ) 
  The tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.
  A net service name is an alias mapped to a database network address contained in a connect descriptor. A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. Clients and database servers (that are clients of other database servers) use the net service name when making a connection with an application (source: https://docs.oracle.com). 
  The file "tnsnames.ora" contains the information about the Oracle server and its connections. This file can be obtained from a DBA. The file must be placed in the machine where DocuWare will be installed and under a Oracle Client Path Directory (E.g.: C:\app\product\11.2.0\\Administrator).
• Create Windows System Environment variables
  1. Open the run command (press [Windows]+[R]) and type: sysdm.cpl
     
  2. Open Tab "Advanced" and open the menu "Environment Variables..."
     
  3. Create a new System variable
     
  4. Enter a name and the path where the tnsnames.ora file is located
     
  5. Restart the machine where Oracle client was just installed

• Test Oracle Connection with SQLPlus+
  Before you install DocuWare, you need to make sure that the user can connect to the Oracle server. This will also verify the Oracle user schema is present in the database. You can use any Oracle tool to connect and create a test table. In this example, we use SQLPlus, which you can run in DOS. When running in DOS, ensure that the credentials are provided, create a test table, and display the test objects for the user. 
  
  
   
• When Authentication server asks to connect to the database server you have to provide the configuration name that is found in the tnsnames.ora file. Typically you will enter the server-name of the database but in Oracle you have to enter the configuration name. You can find this information in your local tnsnames.ora file. The deafult port number for Oracle is 1521.

NOTE: Once the Oracle client has been configured in DocuWare server, authentication server installation will create all database objects required for DocuWare services. There no additional configuration that needs to be done in Oracle server.

Troubleshooting Oracle client

• Verify any logs generated by the setup or application in %programdata%\DocuWare\
• DocuWare support only 32bit of Oracle client
• If your database is in version Oracle 12c, try installing the Oracle 12c ODAC client
• TNS_NAME path is invalid
• Oracle user has no rights or not sufficient rights.  
• The server has not been restarted after an Oracle client install
• The port number not been opened from DocuWare server to Oracle server, verify the Oracle server port or firewall  

From DocuWare 6.12 on, no ODAC is required

KBA is applicable to On-premise Organizations ONLY.