Enterprise Data Architects wanted to demonstrate a concept for dynamically displaying ERStudio models that could be viewed by everyone in the enterprise. Their proof of concept included Elasticsearch and Kibana connecting to Microsoft SQL server resources through ODBC and displaying content with an Apache web server and PHP.  This is the setup for their platform build on a CentOS 7 server.

• Install CentOS
• Install Apache Web Server
• Install Java
• Install Elasticsearch
• Install Kibana
• Install PHP
• Install MSSQL ODBC Driver
• Setup ODBC for PHP

Install CentOS7

Only the minimum install is necessary for this POC.

Install Apache Web Server

Update the server and install the httpd service on CentOS7.
sudo yum update
sudo yum install httpd

Install Java

Elasticsearch can install with OpenJDK, but this install will use the latest Oracle JDK instead.

Copy JDK install file to server home and run install. Be sure to install as sudo user, not as root for correct 10/43 directory permissions.
sudo tar xzfv ~/jdk-8u*.tar.gz -C /usr

Configure using alternatives (note: change jdk1.8.0_201 to match downloaded version number)
sudo alternatives --install /usr/bin/java java /usr/jdk1.8.0_201/bin/java 2
sudo alternatives --config java

Choose the number for whatever option has the jdk path
sudo alternatives --install /usr/bin/jar jar /usr/jdk1.8.0_201/bin/jar 2
sudo alternatives --install /usr/bin/javac javac /usr/jdk1.8.0_201/bin/javac 2
sudo alternatives --set jar /usr/jdk1.8.0_201/bin/jar
sudo alternatives --set javac /usr/jdk1.8.0_201/bin/javac

Configure Java environment variables
sudo nano ~/.bashrc

# User specific aliases and functions
# JAVA
export JAVA_HOME=/usr/jdk1.8.0_201
export JRE_HOME=/usr/jdk1.8.0_201/jre
export PATH=$PATH:JAVA_HOME/bin:JRE_HOME/bin

Apply the changes in current running environment
source ~/.bashrc

Check if working
echo $PATH

Install Elasticsearch

Disable SELinux
sudo nano /etc/sysconfig/selinux

SELINUX=disabled

Reboot
sudo shutdown -r now

Test
getenforce

Import the repository's GPG key
sudo rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch

Create repo file
sudo nano /etc/yum.repos.d/elasticsearch.repo

[elasticsearch-6.x]
name=Elasticsearch repository for 6.x packages
baseurl=https://artifacts.elastic.co/packages/6.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

[elasticsearch-7.x]
name=Elasticsearch repository for 7.x packages
baseurl=https://artifacts.elastic.co/packages/7.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

Install Elasticsearch
sudo yum install elasticsearch

Disable memory swapping (enable memory locking) and config network.
sudo nano /etc/elasticsearch/elasticsearch.yml

# ----------------------------------- Memory -----------------------------------
#
# Lock the memory on startup:
#
bootstrap.memory_lock: true

# ---------------------------------- Network -----------------------------------
#
# Set the bind address to a specific IP (IPv4 or IPv6):
#
#network.host: 192.168.0.1
#
network.host: 10.11.12.13


# --------------------------------- Discovery ----------------------------------
#
# Pass an initial list of hosts to perform discovery when this node is started:
# The default list of hosts is ["127.0.0.1", "[::1]"]
#
#discovery.seed_hosts: ["host1", "host2"]
discovery.seed_hosts: 10.11.12.13

Notes:

• See setup configuration reference guide.
• The network_host parameter allows cURL commands, while the discovery_seed_hosts parameter allows http connections to port 9200

Remove memory lock limit by creating systemd folder and override file - see reference:
cd etc/systemd/system
sudo mkdir elasticsearch.service.d
sudo nano elasticsearch.service.d/override.conf

… or run the command
sudo systemctl edit elasticsearch

[Service]
LimitMEMLOCK=infinity

Remove max-memory limit
sudo nano /etc/sysconfig/elasticsearch

# The maximum number of bytes of memory that may be locked into RAM
# Set to "unlimited" if you use the 'bootstrap.memory_lock: true' option
# in elasticsearch.yml.
# When using systemd, LimitMEMLOCK must be set in a unit file such as
# /etc/systemd/system/elasticsearch.service.d/override.conf.
MAX_LOCKED_MEMORY=unlimited

Enable and start the service
sudo systemctl daemon-reload
sudo systemctl enable elasticsearch.service
sudo systemctl start elasticsearch.service

Test  for host address listening on port 9200 and 9300
netstat -plntu

tcp6       0      0 10.11.12.13:9200    :::*         LISTEN      -
tcp6       0      0 10.11.12.13:9300    :::*         LISTEN      -

Confirm service is running on localhost
curl -X GET 'http://localhost:9200'

{
  "name" : "elasticserver",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "4H6bdDEjRqiynn2NzqtxKg",
  "version" : {
    "number" : "7.1.0",
    "build_flavor" : "default",
    "build_type" : "rpm",
    "build_hash" : "606a173",
    "build_date" : "2019-05-16T00:43:15.323135Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

Confirm memory locks are all off
curl -XGET 'localhost:9200/_nodes?filter_path=**.mlockall&pretty'

{
  "nodes" : {
    "6omlBhpfRJaRq3KAvUX8SQ" : {
      "process" : {
        "mlockall" : true
      }
    }
  }
}

Confirm service is running over http by opening http://host.domain:9200 in browser and looking at the response.

{
  "name" : "elasticserver",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "4H6bdDEjRqiynn2NzqtxKg",
  "version" : {
    "number" : "7.1.0",
    "build_flavor" : "default",
    "build_type" : "rpm",
    "build_hash" : "606a173",
    "build_date" : "2019-05-16T00:43:15.323135Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

Remove the "-quiet" from the startup service
sudo nano /usr/lib/systemd/system/elasticsearch.service

View the messages logged by the Elasticsearch service
sudo journalctl -f
sudo journalctl -u elasticsearch

Note:

• Reinstalling or upgrading? Need to delete the /var/lib/elasticsearch/nodes directory or get a node lock error on startup

Send some data to Elasticsearch.
curl -XPUT 'http://localhost:9200/twitter/_doc/1' -H 'Content-Type: application/json' -d '
{
    "user": "selfie",
    "post_date": "2019-11-15T13:12:00",
    "message": "First tweet, trying out Elasticsearch"
}'

curl -XPUT 'http://localhost:9200/twitter/_doc/2' -H 'Content-Type: application/json' -d '
{
    "user": "selfie",
    "post_date": "2019-11-15T14:12:12",
    "message": "Another tweet, this should be indexed"
}'

curl -XPUT 'http://localhost:9200/twitter/_doc/3' -H 'Content-Type: application/json' -d '
{
    "user": "selfie",
    "post_date": "2011-01-15T01:46:38",
    "message": "Last tweet, who cares if this fails"
}'

See if the data comes back from the server.
curl -XGET 'http://localhost:9200/twitter/_doc/1'
curl -XGET 'http://localhost:9200/twitter/_doc/2'
curl -XGET 'http://localhost:9200/twitter/_doc/3'

Query Elasticsearch for the data.

curl -XGET 'http://localhost:9200/twitter/_search?q=user:selfie'

Use JSON query language to get all documents stored.
curl -XGET 'http://localhost:9200/twitter/_search' -H 'Content-Type: application/json' -d '
{
    "query" : {
        "match_all" : {}
    }
}'

Do range search (the @post_date@ was automatically identified as date)
curl -XGET 'http://localhost:9200/twitter/_search' -H 'Content-Type: application/json' -d '
{
    "query" : {
        "range" : {
            "post_date" : { "from" : "2009-11-15T13:00:00", "to" : "2009-11-15T14:00:00" }
        }
    }
}'

Install Kibana

Import the repository's GPG key
sudo rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch

Open your text editor and create the following repo file:
sudo nano /etc/yum.repos.d/kibana.repo

[kibana-7.x]
name=Kibana repository for 7.x packages
baseurl=https://artifacts.elastic.co/packages/7.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

Install
sudo yum install kibana

Configure network settings
sudo nano /etc/kibana/kibana.yml

# Specifies the address to which the Kibana server will bind. IP addresses and host names are both valid values.
# The default is 'localhost', which usually means remote machines will not be able to connect.
# To allow connections from remote users, set this parameter to a non-loopback address.
#server.host: "localhost"
server.host: 10.11.12.13

# The URLs of the Elasticsearch instances to use for all your queries.
#elasticsearch.hosts: ["http://localhost:9200"]
elasticsearch.hosts: http://10.11.12.13:9200

Note: don't use quotes in the addresses

Enable and start the service
sudo systemctl enable kibana.service
sudo systemctl start kibana.service

Opening the service in a browser to test, e.g. http://host.domain:5601/

Install PHP

PHP 7.2.5 ODBC
sudo yum --enablerepo=remi install php73-php-odbc

PDO_ODBC
sudo yum -y install php_odbc

Test if PHP is working
sudo nano /var/www/html/info.php

Install MSSQL ODBC Driver

This driver has a history of significant changes, so see Microsoft's latest instructions for installing ODBC drivers on Linux.

Setup the server for downloading the driver as root user.

sudo su
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit

Remove any previous odbc_connect driver versions to avoid conflicts.
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel

Install Microsoft driver (creates /opt/microsoft)
sudo ACCEPT_EULA=Y yum install msodbcsql17

Install tools like bcp and sqlcmd (creates /opt/mstools)
sudo ACCEPT_EULA=Y yum install mssql-tools

Install unixODBC development headers
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Update the environment PATH statement
sudo yum install unixODBC-devel

Check /etc/odbcinst.ini file for correct version
sudo nano /etc/odbcinst.ini

[ODBC]
Trace=Yes
TraceFile=/dev/stdout

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

Check the driver's dependencies for any "not found" errors
ls -l /opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.3.so.1.1

Test if the driver works
dltest /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1 SQLGetInstalledDrivers

Setup ODBC for PHP

Map the ODBC resources to where odbc_connect expects them to be
sudo ln -s /etc/odbc.ini /usr/local/etc/odbc.ini
sudo ln -s /etc/odbcinst.ini /usr/local/etc/odbcinst.ini

Check the ODBC setup
odbcinst -j

Add SQL DSN's through proxy file
nano /home/user/odbcadd.txt

[EDW]
Driver =        ODBC Driver 17 for SQL Server
Description =   EDW SQL Server
Trace =         No
Server =        host1.domain.com
Port =          1433
Database =      DatabaseName
User =          serviceaccountuser
Password =      password

[ERStudio]
Driver =        ODBC Driver 17 for SQL Server
Description =   ERSTUDIO SQL Server
Trace =         No
Server =        host2.domain.com
Port =          1433
Database =      DifferentDatabaseName
User =          serviceaccountuser
Password =      password

Register the DSN information into /etc/odbc.ini
sudo odbcinst -i -s -f /home/user/odbcadd.txt -l

Check the DSN installation
cat /etc/odbc.ini

Test a connection using the odbc_connect method
isql EDW serviceaccountuser password -v

Test a connection using the sqlcmd method
sqlcmd -D -S EDW -U serviceaccountuser -P password
sqlcmd -D -S ERStudio -U serviceaccountuser -P password

Test simple connection using PHP by editing the info.php file and viewing it in the browser
sudo nano /var/www/html/info.php

<?php
	// Data Source Name (DSN) from the file /etc/odbc.ini	
	$dsn = "EDW"; 								
	
	// MSSQL database user	
	$user = "serviceaccountuser";
	
	// MSSQL user password
	$password = "password";

	// Connect string
	$connect = odbc_connect($dsn, $user, $password);

	// Verify connection
	if ($connect) {
		echo "Connection established.";
		odbc_close($connect);
	} else {
		die("Connection could not be established.");
	}
?>

Test a SQL response by editing the info.php file and viewing it in the browser
sudo nano /var/www/html/info.php

<?php
	// Replace the variables as needed
	$dsn = "EDW";
	$user = "serviceaccountuser";
	$password = "password";
	$connect = odbc_connect($dsn, $user, $password);

	// Verify connection
	if ($connect) {
		echo "Connection established.";

		// SQL Query variable
		$sql="SELECT TOP 10 * FROM dbo.Tableau_Workbooks";
		
		// Recordset variable
		$rs=odbc_exec($connect,$sql);

		// Exit if results are empty
		if (!$rs)
		{exit("Error in SQL");}

		// Otherwise, return HTML table and headers
		echo "<table>";
		echo "<tr>";
		echo "<th align='left'>ID</th><th align='left'>Name</th>";
		echo "</tr>"

		// Return data rows
		while (odbc_fetch_row($rs))
		{
			// ID field variable
			$id=odbc_result($rs,"id");
			// Name field variable
			$name=odbc_result($rs,"name");

			echo "<tr><td>$id</td>";
			echo "<td>$name</td></tr>";
		}

		// close connection
		odbc_close($connect);

		// close HTML table
		echo "</table>";

	} else {
		die("Connection could not be established.");
	}
?>