Clarity

Normally our infrastructure team does our installations and upgrades.  Unfortunately they are booked solid at the moment, and we need to setup a new sandbox in our Azure cloud ASAP.  I have installed Tomcat, SQL Server and the PPM NSA, but can't get NSA to connect to the db.  I used the install guide found on docops, but I still can't get this to work.  I'll be the first to admit although I support Clarity, I have never done an install in any way.  So...for the gurus out there...is there any chance I can get a detailed doc on how to setup my SQL server to actually connect to the app?  I have the niku db imported, and setup a securityadmin mapped to the schema.  I'm missing something though.  Any help would be appreciated.  Thanks in advance!

Note, we have a TA in progress to get someone from CA onboarded to help with this as well as our migration to a new PROD environment, but the paperwork won't be completed in time for when they want the sandbox (isn't this always the way?!).  We need the environment up by May 7th, and the earliest we will have our contract in place is the 14th.

Hi Chris,

Is there anything in the PPM logs as to why it cant connect to the DB?  Also, you menitoned SQL 2017, which as per our support matrix is not currently supported or certified with PPM 15.4, so I am not sure that it will work with that version.  We are currently certified with SQL 2016 and SQL 2014 at the moment.  Most likely they will certify SQL 2017 in the next release.  

Hope this points you in the right direction.

Jon

Thanks for responding, Jon.

I have uninstalled SQL Server 2017 and have performed a fresh install of 2016.  Again, I followed the install doc, and also compared it to this great thread (step-by-step guide).  Still no look when trying to use NSA to connect the Database within the Servers menu item (continues to flag as being unavailable).

The nsa-ca.log indicates "Found invalid database connection for local tenant clarity"

Where can I find any logs which might prove to be a little more helpful?

Thanks!

Are you installing PPM on the same machine?

If so, you might have to use the private IP address for the database connection.

If not, are both your servers (PPM/MSSQL) in the same resource group?  If so, you should be able to use the private ip address of the MSSQL server in the NSA.  It still might want the 1433 firewall rule for the private subnet.

If in different resource groups, you will have to use the public IP address and configure firewall rules to allow traffic between the two public IP addresses.

Just some thoughts,

Gene

As far as I can see the Step by step ends where the connection problems start.

What needs to be done is to set up the client protocol and port and to quick and dirty test of the connection with ODBC,

Some of that is covered in

At least one validated Database must exist in Multitenant mode 

Hi Gene,

The installation is on the same box.  After some further updates and testing, it appears I managed to get it to work (insert celebration here).

There are definitely issues with the installation however.  Sometimes when I start the services (cmd prompt: services start all) I get a failure with the CA APP Service (if user is local, preface it with ".\", but after a couple of tries it works...makes little sense to me).

On to configuring the data warehouse...

Chris - I think that Jasper is going to be the pain point for you - It is not certified with SQL 2017 either, and I dont think anyone has tried an install for it yet    Let us know what you come across when you get to that point as well.  This is all good information sharing.  Great team work!!!

Here is a powershell script to test if you can connect to the database.  This assumes your account has access to MSSQL otherwise you will need to update the connection string.

$sqlServer = "localhost"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection;
$sqlConn.ConnectionString = "Server=$sqlServer;Integrated Security=true;Initial Catalog=master";
$sqlConn.Open();
$sqlcmd = $sqlConn.CreateCommand();
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand;
$sqlcmd.Connection = $sqlConn;
$sqlcmd.CommandText = "SELECT name, database_id FROM sys.databases";
$data = New-Object System.Data.DataSet
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$adp.Fill($data) | Out-Null
Write-Host $data.Tables[0].Rows.Count;

V/r,

Gene

Thanks to all for the help and direction.  The community is definitely active, and willing to throw some expertise at any questions raised.  Greatly appreciate it!