LANSA to MS SQL Server on separate machine

[kofoster]

We are attempting to deploy a LANSA WAM application to an environment where the LANSA code is on a different server from MS SQL Server database. The deployment executes successfully - DB tables, Logins and Users are created on the database but attempts to pull up our login page gives:

Unrecoverable error occurred while processing the Web request
Message text: Failed to access application server.
Message index: <N/A>
Further information: CM_PROGRAM_STATE_CHECK

Or

Unrecoverable error occurred while processing the Web request
Message text: Failed to access application server.
Message index: <N/A>
Further information: CM_UNSUCCESSFUL

The trace entries, as interpreted by LANSA, indicate that our PC User does not have access to the database, even though there is a Login and User with that name created, by the deployment, in the database.

Has anyone done this successfully and can offer some insights on how?

[René Houba]

Hi Kofoster,

What about the Web Administrators DFTUSR? Is that user OK (can you do a proper test connection)?

KInd regards,
René

[kofoster]

Hi René,

Thanks for responding.

No, the Web Administrators Test Connection does not complete successfully. It returns a pop up with the following messages:

Connection failed (0x0000001c)
Is the database started?
Is the user allowed to access the database?
Is the IP address or hostname of the database server correct?

[René Houba]

The problem is there then.....

[kofoster]

Hi René,

I followed the instructions in the LANSA documentation but have not been able to connect to the database. It seems the instructions work for the situation where the database and the LANSA code reside on the same machine. I am trying to have the database on a separate server. Do you have such a set up and can provide some guidance on any additional instructions required?

Thanks,
Karl

[René Houba]

Having the database on another machine is no problem at all. The best you can do is to create a separate ODBC connection manually and test the connection there. If the ODBC connection is OK, then the rest will be OK as well. Do you know what to do know?

[kofoster]

Unfortunately no. This is uncharted territory. Our LANSA background has been on AS/400 or Windows where the database and code reside on the same machine. Our experience with SQL Server on a separate machine has been with other languages, not LANSA.

Can you provide any instructions or point to some documentation online?

Thanks
Karl.

[LansaHelp]

Hi Karl, if both the app and DB servers are in the same windows domain, you can try creating the LANSA Web user as a domain user (DFTUSR) with appropriate permissions on both the app and DB server. You can then get rid of the local web user profile on the app server and test that out using the new domain user. This should work well.

[kofoster]

Hi LansaHelp,

I wasn't able to get this working with the domain user. We kept fiddling with it, though, and were eventually able to display the login page. Here is what worked for us. Let me know if this is logically correct:

1. In the database configuration of the deployment we set Trusted Connection to no and entered a Database User and Password.
2. We made the Database User and Password the same as the PC User and Password.
3. The PC User and Password were created during the deployment (MSI) execution but we think a manually created ID would have worked also.
4. On the database server, we created a PC user with the same ID and Password as the PC user in 3. above.
5. We also created a database login, in SQL Server Management Studio, with the same ID and Password as in 3. above. The ID was give rights to the database we had manually created.

Once the deployment (MSI) completed execution, we entered the licenses and attempted to test connectivity via the Web Administrator. This was successful. Then we attempted to display the login page which was also successful. I will conduct some additional testing but I think we might be past the original issue we experienced.

Thanks to René and you for your help.

Karl