acpzehetal
Enthusiast
Enthusiast

IDM 2.71 or 2.8 fails to connect to external MS SQL database instance

We have tried to deploy a Vmware Identity Manager appliance (V2.71 and v2.8) for a Pilot Installation, but we are ending always in the same issue. To have the ability to do a load balancing we decided to use a external Microsoft SQL Server 2008 instance named VDI for the saas database. We used the SQL script provided in several Blogs to produce the saas database.

If we use the appliance the Setup wizard we stop at the database jdbc con Connection string  ( we use something like that: jdbc:sqlserver://192.168.5.175\VDI:1433;databaseName=saas;)

If we are testing the Connection, the wizard gui ist showing up, everthing is ok, but if we configure the database connection, the service restart is ending with a error message.

The intermediate wizard tells up that the System is waiting for a service restart (1/5, 2/5, 3/5, 4/5 and 5/5)

We have a look at the saas database on the MS SQL database and no tables or views were created, nevertheless the user horizon has sufficient rights.

pastedImage_2.png

We need help to deal with this issue. At least anyone could tell us where the log files for the database reside on the idm appliance?

Labels (2)
0 Kudos
12 Replies
cdubz
Enthusiast
Enthusiast

have you tried using the fqdn of the database server, cluster name, or always on listener (if the database is cluster)?

also the jdbc command should read something like this:

jdbc:sqlserver://<hostname-or-DB_VM_IP_ADDR>;DatabaseName=saas



So try jdbc:sqlserver://yoursqlserverfqdn;DatabaseName=saas and leave out the actual port number (1433 is default for SQL anyways (since your named instance isnt the default you may have to add it in like you did originally behind the fqdn)).

One last note, i had issues with 2.7.1 IDM deploying to any sort of external SQL database. I tried a basic standalone sql 2012 box and a always on cluster sql 2012 and they both always errored out 2.7 install whether it be a fresh install or an upgrade.  2.7 worked fine on the same SQL instances.  2.8 also seems to work fine as well (im using sql 2012 always on cluster)

0 Kudos
DDunaway
Enthusiast
Enthusiast

I am having the exact same issue. Running 2.8 here with SQL server 2014. followed the same documentation, used the same scripts and I'm using all FQDNs. I keep getting the ???NULL??? error at the top of the screen.

-David

0 Kudos
cdubz
Enthusiast
Enthusiast

DDunaway‌ is your 2.8 an upgrade or a fresh install?

0 Kudos
DDunaway
Enthusiast
Enthusiast

cdubz Its a fresh install. The SQL database connection test is successful, but I keep getting the same error that the original poster is getting.

after it runs through the "Checking of server is up ...5/5" I ultimately get a message at the top of the screen saying "error when configuring database connection"

If I try to hit continue again I get the ???NULL??? message.

I am using SQL 2014 running on Server 2012 R2. I used the script int he install documentation to build the database and user account.

I even blew my appliance today and re-deployed the OVA and got the same result. Can't figure out what I'm doing wrong.

all of my networking and DNS are configured correctly. I can even validate them by logging into the console as root. I can ping my DC and SQL server by name.

Capture1.PNGCapture3.PNG

0 Kudos
cdubz
Enthusiast
Enthusiast

Yeah that looks like the same i was getting with 2.71.  Didnt get it with 2.8 though. 

SQL Questions:

Is your SQL installation a standalone sql server or a clustered sql server?

Is your SQL installation setup for Mixed authentication (SQL and Windows)?

If cluster is it a traditional cluster or Always On cluster?

When creating the DB you used the following code from the reference install documentation?:

           CREATE DATABASE saas

COLLATE Latin1_General_CS_AS;
ALTER DATABASE saas SET READ_COMMITTED_SNAPSHOT ON;
GO
BEGIN
CREATE LOGIN horizon WITH PASSWORD = N'H0rizon!';
END
GO
USE saas;
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'horizon')
DROP USER [horizon]
GO
CREATE USER horizon FOR LOGIN horizon
WITH DEFAULT_SCHEMA = saas;
GO
CREATE SCHEMA saas AUTHORIZATION horizon
GRANT ALL ON DATABASE::saas TO horizon;
GO

Appliance Deployment Questions:

When deploying the appliance are you configuring all the networking fields?  Install documentation mentions domain name and domain search path are not needed:

VMware Documentation Library

Are you using multiple DNS servers during deployment and comma separating them or single dns?

0 Kudos
DDunaway
Enthusiast
Enthusiast

I'm just using a standalone, not clustered, SQL 2014 instance. Mixed authentication. Other systems like Composer and View events are going to it, so its working.

yep, that's the code I used to create the database and user.

at first I was filling in all the networking fields, this time around I left the domain name and search path blank.

I'm using two DNS servers, separated by a comma. I'm sure this is configured correctly as I can log into the appliance as root and ping everything by hostname.

SQL 2014 threw a message saying the "grant ALL on Database" command is deprecated, so I'm looking into that.

0 Kudos
cdubz
Enthusiast
Enthusiast

i received the deprecated warning as well but that shouldnt affect the installation of the appliance.

when you re-ran the sql db create code, did you delete the old saas db and horizon login?

Also when you comma separated them did you do a comma + space + second dns ip or just a comma+ second dns ip?

0 Kudos
DDunaway
Enthusiast
Enthusiast

I did delete the old database and login before running the script.

I figure since the install is broken, I should do that on each attempt. I'll give that a try.

funny, I was thinking the same thing about comma separating my DNS servers. I do comma + space + second entry. is that what you did?

0 Kudos
DDunaway
Enthusiast
Enthusiast

well, it finally completed just fine.

I don't know what might have finally fixed it, but it was a combination of starting with a fresh deployment (just a reboot or a refresh of the web browser wouldn't do). also, a delete and re-create of the database and user each time.

I left the domain name and search path fields blank this last deployment and I did have a space in my comma separated DNS entries.

0 Kudos
cdubz
Enthusiast
Enthusiast

Good to hear it is working.  I dont think i used a comma in my setup. I finally gave up on 2.7.1 back in the day and went back to 2.7 which gave me no issues in external database configuration. Finally got around to trying 2.8 and it seemed to work for me as well

0 Kudos
mvanvli2
Contributor
Contributor

I had to modify the etc/resolv.conf file to add the domain and search domains.

When connecting to the DB with the IP it would connect fine, when using FQDN it worked as well, however, short names failed. This resolved this issue.

After the resolv.conf file was modified I was then able to use the short names without issue.

0 Kudos
DDunaway
Enthusiast
Enthusiast

I also learned that you must access the appliance by FQDN rather than IP or it fails.

0 Kudos