View source for Database Settings V10

[[Category:FMR_Configuration_Reference]]
==Overview==
The Database settings function is available to logged-in users and found on on the Server Settings menu.

==Database type==
This page allows you to provide the connection details to your chosen database service which will be used as the main operating database.

Options available are:
===1. MySql===
'''Example'''

[[File:Sql1.png|Example MySQL|800px]]

===2. Oracle===
'''Example'''

[[File:Sql2.png|Example Oracle|800px]]

===3. SQL Server===
'''Example'''

See example of a [https://fmrwiki.sdmxcloud.org/Database_Settings#Single_Sign-On_.28SQL_only.29 SQL connection below].

===4. Custom===

The schema must already exist but can be empty in which case the Registry will create all of the necessary tables. If you specify an existing Registry schema here, the service will attempt to load the content and settings within.

If your database service requires special parameters such as authentication details, use the Custom Database Type option which allows the JDBC connection string to be set.

An example of a custom connection using MySQL and the data base schema is called "my-database" is:

'''Connection String''': jdbc:mysql://localhost:3306/my-database<br>

'''Dialec'''t: org.hibernate.dialect.MySQL55Dialect<br>

'''Class Name''': com.mysql.cj.jdbc.Driver<br>

==Server==
If you are running Fusion Metadata Registry on a local machine using (for example Apache Tomcat) the server is "'''localhost'''".  Alternatively, enter the appropriate server path.

==Port==
The default port used by Apache Tomcat is 3306.

==Schemea==
Enter the name of the schema as it appears in your chosen application.

==Userid==
This is the userid for the database (NOT the User Name used in the Registry).

==Password==
This is the password for the database (NOT the password used in the Registry).

==Single Sign-On (SQL Server only)==

Fusion Metadata Registry 10+ supports Single Sign-On (SSO) when connecting to a SQL Server database.  This can be activated via the Registry User Interface if SQL Server is selected.

[[File:Singleso1.PNG|Single Sign-On|800px]]<br>


For this feature to work a DLL is also required.

The DLL can be obtained from Microsoft.  You will need to download the "Microsoft SQL Server JDBC Drivers" package which contains a number of drivers named “sqljdbc_auth .dll” but for different systems (e.g. x86, 64 bit, etc.).  You need to locate the appropriate DLL for your system.

This DLL needs to be supplied to the Java Runtime running your Web Application Server.  There are several ways in which this can be achieved.  Two of the simplest methods are listed below:

# Copy the DLL file to the Java Runtime “bin” directory that is running your Web Application Server.  It is important to place the DLL in the correct directory (for example:  C:\Java\jdk1.8.0_92\jre\bin).  Note: that modifying a Java Runtime in this manner means that all applications that use this Java Runtime will be affected.
# Pass the DLL location to the Web Application Server on server startup.  If you are running Apache Tomcat as a service, please refer to [https://fmrwiki.sdmxcloud.org/Install_Apache_Tomcat this article].  Otherwise this can be achieved by modifying the “setenv.bat” file located in the Tomcat bin directory. Locate the directory with the DLL you wish to add (e.g. c:\temp) then add the following line to setenv.bat and the Java library path will be modified allowing Tomcat to access the DLL file:

'''set CATALINA_OPTS=%CATALINA_OPTS% -Djava.library.path=C:\temp\SSO_DLL'''

Once your Web Application Server has started and can access the correct DLL, SSO can be enabled via the database settings.  When attempting to enable SSO, if you receive an error like the following, then the DLL could not be located or is the wrong version for your system:

'''java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path'''

In this scenario, please double-check the actions you performed and ensure that you are using the correct driver.




'''If you make any changes, don't forget to use the Apply Settings button before leaving the page.'''