LBI Connection to AS400 Server failure

First thing I would do is change your password for user LAWPAY.

Make sure that you have the DB2 client installed on the LBI server.

And you'll need the datasource configured on the LBI server as well (and if you have a remote RAS, configure it there as well). You'll want to configure the datasource in the 32-bit ODBC Data Sources program if you are running a 64 bit OS. ie C:\Windows\SysWOW64\odbcad32.exe

We use the Client Access ODBC drivers, but I dimly recall a similar problem with those.

My notes say that when the report is developed on the local workstation, it must be done with a system DSN, previously we had always used file DSNs for convenience. The name of the system DSN on the workstation must match the name of the system DSN on the LBI server, or no data source will be found when the reports runs on the LBI server with it's internal datasource (if you don't user overrides.)

We use override datasources in LBI, which might be irrelevant for you, but I thought I should mention it.

First, verify that your Infinium ODBC drivers are installed on the LBI server, and a working ODBC system DSN has been set up on the LBI server that points to your AS400 Lawson data. Test the DSN by doing a simple MS Query or similar while logged in to the LBI server. We are hosted, so we had to have an admin at the host end do that for us.

Make sure the test is done with the credentials used in your report. We use a single generic user account for reporting, maybe similar to your LAWPAY account.

Here, for our setup, we created override datasources in LBI, so we could switch reports between test and production environments. When creating an override datasource, you supply the name of a system DSN that's already been created on the LBI server, and the credentials that will be used to access the Lawson data.

We always select an override datasource as soon as we publish, and I think we got the “Failed to open the connection error” when those weren’t set up correctly, so all this my not apply to you, but I might get you pointed in the right direction.

Thanks guys for your responses. Looks like we have two installations to do. In addition to installing on the Windows server we need to also install it on the INFINIUM Server.


imty.

We installed the IBM ODBC Driver to the LBI Server but we are still having problems with connecting. We still get the same error message Failed to open the connection. Failed to open the connection. {269E30EB-CAED-4B65-856A-91AF187F03F0}.rpt . Does anyone have any further thoughts on what we should do to resolve this.

thanks,
imty

So you installed the driver, but did you create the ODBC datasource on the LBI server? If you have the Crystal Reports client installed on the LBI server, try running the report that way. This should help isolate the issue.

Chris,

The data source was created in LBI but I am getting the same error message. We are using a system DSN

You are using system dsn when you develop it in Crystal Reports, but you need to create an identical system dsn on the LBI server. This in addition to the Override datasource that you created in Reporting Services. Just wanted to clarify...

Chris,

Do you happen to know where in LBI would I do this. Could you please provide me the menu path.

thanks,
Imty

Take a look at Greg and Mike's replies. They have provided the information that you need.

Hello All,

Please see attachment. I created a new Data source in LBI. I was told by the LBI Administrator that when he created the ODBC connection in LBI he used INFINIUM as the DSN Name. For Database I chose INFINIUM. The INFINIUM administrator provided the following information to connect to INFINIUM.

Infinium User Profile for ODBC connect.

User Profile: LAWPAY
Password: PASSLAW

IP address: INFINIUM or 10.8.253.117

Library (Directory) USLAWSON
Table (File) USP1470HF

For the Schema I chose DB2 and user name and password LAWPAY and PASSLAW respectivley. After I completed this I went to DATA SETTINGS and for Override datasource I chose INFINIUM (which is the data source i just created). After doing this I tried to run with Data Refresh and I am still getting the message "Connection failed to open."

Do you notice anything that may not be correct? Any thoughts or suggestions as to what I should do next, is most welcome.

thanks again for your time.

Imty.

I compared your datasource screen clip with our datasource.

"DSN" is an arbitrary name assigned when creating the DSN, in our case Lawson 9 EUG.

"Database" is the name of what our provider calls a "data area". I don't know if that applies to you, but if it helps, our production environment has one data area, while our test environment currently has two. Each data area contains all the Lawson tables.

"Schema". Our value is "dbo". My notes say this must be in lower case. This info was provided by Lawson support when we were working out problems with override datasources.



I compared your report settings with one of our reports. The Override Datasource is set to the name of the production datasource, in our case "Lawson9 EUG". In our case, our override datasources are named the same as the their underlying system DSNs on the LBI server. Not sure if this is required.

The Default User ID and password are left in blank, I think because this information is already stored in the overide datasource.

I think your first step should be to work with your LBI admin to verify that, while logged on to the LBI server, you can use the INFINIUM *DSN* to pull Lawson data via MS Query or other ODBC query tool. If so, then try to run your CR. Once you verify your CR will run locally from the LBI server using the *DSN*, you can try running it in LBI using the override datasource.

My notes say that the report must be developed on using a system DSN.

I'm not currently able to view your attachment and see how your Override Datasource is configured, but...

I would stress what mikeP said and what I mentioned earlier:

Run the report from the LBI Server (not from within the LBI/Reporting Services application) using the Crystal Reports designer. If this is successful, then you probably have an issue with your Override Datasource.

Additionally, you can run it from LBI/Reporting Services without the Override Datasource (hard coding the user/pw in the report: Details --> Data Source) to further isolate the cause.

I tried running the report by using "use report data source" in Override datasource and selecting use Crystal Designer and it does not like that either. The problem here is that the person administering the LBI is not a proficient LBI administrator. Based on what he tells me that he has created an ODBC connection on the LBI server. I told him that we could test the LBI connection using MS query to see if the test is successful. I went to MS Query in Excel and i don't even see an LBI data source for INFINIUM in there to even test it. I appreciate you guys trying to help out but there does not seem much else that I can do at this point. When I run the report in the Crystal designer the report runs fine but fails in LBI. I will keep toying with it and hopefully figure it out.

Crystal Reports (and therefore LBI) are still 32-bit apps. Verify that the ODBC datasource was created using C:\Windows\SysWOW64\odbcad32.exe and not C:\Windows\system32\odbcad32.exe

If you can, create the exact same name on the Designer side as well, using the syswow64 executable. This will allow you to 'use report datasource' more seamlessly.

We finally got it working. There were a couple of issues to iron out. Lawson informed us that

There are two odbcad32.exe programs on your server:
1. C:\Windows\System32\odbcad32.exe (This is the 64-bit version that launches from Start menu > Administrative Tools. It won't work for LBI/RAS.)
2. C:\Windows\SysWOW64\odbcad32,exe (This is the 32-bit version, which you must use to create any DSNs to be used by LBI/RAS, and they must be System DSNs.)

The other issue was that the DSN created on the LBI server was a user DSN and not a System DSN. So, this was another hiccup.

Once these two issues were fixed we tested using the report data source and when this was successful we used the Override data source which was also successful.