How does Crystal Reports establish a connection to a SQL Server database when the ODBC Data Source is PeopleSoft PeopleTools?

[Former Member]

I am using Crystal Reports 2011 on Windows 2008 R2 successfully, but it is failing on Windows 2012 R2.  Both servers have the same Crystal Reports, the same SQL Server Native Client version, and the same PeopleSoft version installed.  The only difference is the Windows version.

On Windows 2008, Crystal Reports connects to the database successfully.  I create a new connection, select ODBC (RDO), select PeopleSoft PeopleTools as my ODBC Data Source.  I get a Connection Information window where I specify my PeopleSoft username/password.  Then I get the PeopleSoft login window where I specify the database, user, and password.  Aside from having to enter my username and password twice, it works beautifully.

On Windows 2012, however, it all looks the same until I select PeopleSoft PeopleTools from the ODBC (RDO) source list.  At that time, the login window shows me PeopleSoft PeopleTools as the Server, and prompts me for my User ID and password (like it does in Windows 2008), but, it also asks me for the Database in that first window.  Instead of getting a separate PeopleSoft login window, as soon as I hit FINISH on that first window, I get my three errors:

1. Database Connector Error: 'HY000:RPC Error: The server threw an exception. From ISQLAllocConnect'
2. Database Connector Error: 'IM005:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed'
3. Logon failed.  Details: 01000:[Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)
   

Through the Data Link utility, I am able to see the connect string using just the SQL Server Native client, but I can't do that with the PeopleSoft PeopleTools connection type that we use in Crystal Reports. I need to know how Crystal Reports is trying to connect to the database.

• Is Crystal passing connect information to the PeopleSoft module, and letting the PeopleSoft module make the connection?
  • If so, what is Crystal passing to PeopleSoft?
• Is the PeopleSoft module providing instructions to Crystal Reports, and Crystal Reports is making the connection?
  • If so, what is the connect string that Crystal is using to try to connect to SQL Server?

Does anybody have this information?  PLEASE...

Thank you.

[former_member11696 · Accepted solution]

Hi Laura,

You may have but something to check, When you installed CR did you select the PeopleSoft database driver? If not go into Programs and Features and click on CR and Modify, expand the database connections and verify the Peoplesoft driver has a check mark next to it.

The other potential problem is CR Designer will look for the various clients in the PATH statement. HAve a look at the PATH on the 2008 Server and if it states the PS tools\bin ( or what ever it uses ) make sure it's also in the PATH statement on the new server.

You may want to install the PS Client tools under a local PC admin account so everyone using that PC has access to the client.

I believe the reason the PS log is not popping up is because it cannot be found.

You can try enabling ODBC Trace and see if that finds anything.

Finally, make sure you are using the ODBC Administrator located in this folder:

C:\Windows\SysWOW64\odbcad32.exe

It could simply be you are using the 64 bit ODBC admin and since CR is a 32 bit app it can't find your DSN.

Don

[Former Member · Accepted solution]

Don,

Thank you for your suggestions.

Yes, I did install the PeopleSoft database driver in my Crystal Reports installation.

Yes, my PS_HOME\bin\client\winx86 (and PS_HOME\bin\server\winx86) are at the front of my PATH

Yes, everything is installed using the same administrator account.

Yes, I tried enabling the ODBC Trace.  I specified a directory and file name, and nothing is getting written.

Yes, I have confirmed that the 32-bit ODBC Admin is configured and is the one being used.

This was a great checklist, but apparently too short...  Do you have the next set of checklist items?

Thank you.

[former_member11696 · Accepted solution]

Hi Laura,

Thanks for confirming. ODBC trace is a pain to get working....

FI find if you click the Trace tab and point to a log file, create one first sometimes works, and then one that info is set then close ODBC Admin down and start it back up again, now click the Trace tab and Start tracing again. Hopefully it should start now.

Stop tracing and then look at the log file, may have to hit F5 key in Explorer to get it updated. Sometimes the data being logged is cached and you have to stop tracing before it is written to the file.

Try the attached file SQLCON, it's an old tool we created way back when the dinosaurs roamed the earth but it still works and does  not use any CR API's.

You can connect by selecting your DSN and then query the DB for tables. If it returns data then CR should see them also, if not there is still some configuration required.

Unzip the file and then rename it to *.zip and unzip it again then you should get the exe file.

Don

SQLCon32.txt.zip

[Former Member · Accepted solution]

Don,

Thank you for the information on the trace.  I will try it on Monday when I get back to the office (or maybe over VPN this weekend...)

Also, thank you for the SQLCon32...  There's something to be said for tools written "when dinosaurs roamed the earth" -- maybe this will give me the details Microsoft is looking for.

I'll let you know...  Thank you.

Don,

I tried the SQLCon32.  Unfortunately, I got the same errors trying to connect.  I'm not surprised that SQLCon32 wouldn't know how to login to a PeopleSoft database, but the fact that I got the same errors made me try it on the Windows 2008 machine.  It worked beautifully on Windows 2008.  It launched the PeopleSoft login window, let me log in, and showed me that it was connecting to the 64-bit .dll, not the 32-bit one.  So, I "deleted" the 64-bit .dll and tried it again on Windows 2008, and got the same error messages I get on Windows Server 2012R2.

So... my question is: Why is it able to negotiate the 32-bit Crystal Reports with the 64-bit PeopleSoft connection to the database on Windows 2008, and not on Windows Server 2012R2...???

Thank you SO much for this "tool written when dinosaurs roamed the earth"  --  It's AWESOME!!!

[former_member11696 · Accepted solution]

Hi Laura,

Some Client 64 bit dll's may have "Thunking" built in, meaning they support both 32 and 64 bit.

One other thing we have run into with Oracle clients is the order the client is installed. Check the PATH statement, could be they are in different order than the working PC.

About all I can suggest now... try installing the 32 bit client first...

Don

[Former Member · Accepted solution]

Dear Laura,

Can you tell if you were able to resolve the issue, please?

I am having the same on Windows Server 2012 R2.

Thank you.

[Former Member · Accepted solution]

Ronny,

Oracle has stated that they will not support the interface from Crystal Reports developer to the PeopleSoft databases beyond Windows 2008.  I had a case open with them for over six weeks, they released new DLL files that still didn't work, they switched my support to a new representative, then they told me that it had been known for more than 6 months that Windows 2008 was the last operating system being supported.  I tried opening a case with Microsoft, but there was nothing they could do because the ODBC engine was working as designed.  My client ended up building a Windows Server 2008 VM just to support edits to their extensive collection of Crystal Reports.

I'm sorry there isn't a better answer.

[former_member11696 · Accepted solution]

Hi Laura,

Thank you for the info...

Ronnie, If Oracle PeopleSoft is not going to support Crystal Reports any more unfortunately for you all other users is the only option now is to move off of PeopleSoft/Oracle and use MS SQL Server or any of the other major DB Servers.

You could even have a look at SAP Hana ( in memory server )

Converting all of your reports to any other DB should be relatively easy to do, our SDK will convert them. Just need to verify them once converted of course...

Actually ironic... Peoplesoft, before Oracle bought them, was a huge OEM Partner of ours and we worked together for years integrating into their Tools...

So until MS stops supporting 2008 Server that looks to be your only option. Assuming Oracle will continue support 2008 Server.

Don

[Former Member · Accepted solution]

Thank you Laura for your answer.

[Former Member · Accepted solution]

Thank you Don for your answer.

[Former Member · Accepted solution]

Don,

PeopleSoft security is a little complex, but the database can be accessed in "bootstrap" mode, allowing access to all of the data.  The problem will be integrating the row-level security.   PeopleSoft creates queries that not only extract the data, but also limit the results according to the security for the user running them.  When Crystal Reports were run, the user would log is as their PeopleSoft user, the connect ID would actually login (because PeoleSoft users do not have database user accounts), the connect ID would verify the user, confirm the password, and login to the database as the symbolic ID associated with that user's profile.  The connect ID and the symbolic ID are the only actual database accounts created in PeopleSoft.  When the symbolic id would run the query, it would apply a series of "where" conditions, limiting what data was returned to that data permitted by the permissions associated with the specified user account.  These criteria would include department, position, etc.

Oracle has stated that it will no longer support Crystal Reports on any database or operating system, and that the current PeopleTools version will be the last to support even the runtime version (that does work on Windows Server 2012R2, but does not work with Crystal Reports developer).

[former_member11696 · Accepted solution]

Hi Laura,

Interesting.... We did have a Peoplesoft database driver kit you could install and this is likely why they no longer support CR. They do not want to maintain it, or for whatever reason....

So bottom line is if users continue to use PeopleSoft they have to do so with Windows 2008 Server and never upgrade.

Don

[Former Member · Accepted solution]

Don,

That pretty much sums it up.  Go backwards and stay there.  LOL

No upgrade, unfortunately, includes both the operating system and PeopleTools.

Laura

[vitaly_izmaylov]

Make sure it is 32 bit (not 64) and System DSN (not User DSN)

[Former Member]

Vitaly,

I have installed only the 32-bit SQL Native Client, but both the PeopleSoft 32-bit and 64-bit ODBC drivers.  When Microsoft was assisting last night, they were able to confirm that the connection was using the 32-bit configuration on my Windows 2008R2 machine, and we confirmed that the settings are the same on the Windows Server 2012R2 machine.

The thing that you said here, though, that surprises me, was the "make sure it is SYSTEM DSN".  On the Windows 2008R2 machine, the PeopleSoft PeopleTools driver by default is installed only under the USER DSN.   When I try to connect to any database using the SQL Server Native Client, I can only see those registered in the USER DSN, I can't see any registered in the System DSN.  Again, the installation on Windows 2008R2 works just fine.  No SYSTEM DSN entries at all.

[vitaly_izmaylov]

I was researching the "The driver doesn't support the version of ODBC behavior that the application requested" error and found
https://forums.asp.net/t/1323565.aspx?+Logon+failed+Details+IM002+Microsoft+ODBC+Driver+Manager+Data...+

I know it is not related to PeopleSoft, but User fixed similar problem by changing DSN type. That is why I suggested to try.

[vitaly_izmaylov]

Laura,

according to this blog:

PeopleSoft ODBC Driver, DSN for Crystal Reports Development for PeopleSoft 9.1

you are correct - It should be User DSN

[vitaly_izmaylov]

Should be something generic if multiple users had exactly the same issue 8 years ago:

http://scn.sap.com/thread/892967

[Former Member]

Vitaly,

I saw this earlier.  I kinda ruled it out because it is Oracle, TNS, and obvious connection related.  The error messages aren't the same -- they actually have much better information than the ones I'm receiving.

Thank you.

[vitaly_izmaylov]

Laura,

I will look for the errors in our Knowledge Base, but I want to share with you some useful links:

PeopleSoft Integration Guide for CR 2008:

https://websmp102.sap-ag.de/~sapidb//011000358700001646972008E/xi3-1_bip_psft_install_en.pdf

The same Information available for CR 2013, but it is mixed with Administration Guide:

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp5_bip_admin_en.pdf

It is 942 pages document where Firewall settings for PeopleSift you can find on page 186, PeopleSoft Authentication on page 299 and the rest of PeopleSoft Integration configuration on page 776

and in User Guide, page 611:

http://help.sap.com/businessobject/product_guides/cr2013/en/cr13sp3_cr_usergde_en.pdf

Some info for ODBC connection available here:

http://docs.oracle.com/cd/E39332_01/psft/acrobat/pt853tcyr-b0213.pdf

and some info how it works here:

http://docs.oracle.com/cd/E39332_01/psft/acrobat/pt853tcyr-b0213.pdf

finally to be aware about future changes read:

The Crystal Reports® Underground

Hope it helps,

Vitaly

[Former Member]

Vitaly,

Thank you for the documents.  Here are my comments:

xi3-1_bip_psft_install_en.pdf:

• First, I can't access this document.  When I try to login, it keeps returning to the  Authentication Required window, saying "The site says: SAP Service Marketplace"  It won't accept my user name and password.
• Second, I am using CR 2011

sbo41sp5_bip_admin_en.pdf

• First, it is still downloading
• Second, the fact that it is called SBO... doesn't that stand for SAP Business Objects?
• Third, I am really looking forward to the information on the Authentication and the Integration

CR13sp3_cr_usergde_en.pdf

• It is also still downloading

pt853tcyr-b0213.pdf

• I have read this document several times, and it does not have the information I need.  It did help me get the installation working on Windows 2008R2, but, even though I did the same thing, using the same source software, the behavior is different on Windows Server 2012R2.

The Crystal Reports Underground

• This has been made very clear to me by Oracle's Customer Support team...  (enough said), but, again, Thank you.

Message was edited by: Laura Ganino

Vitaly, I read the information in the sbo41sp5_bip_admin_en.pdf document and unfortunately, it is all about the Business Intelligence Platform.  We are using only Crystal Reports, no BI Platform.  The connectivity for Crystal, by itself, is very different from what BI Platform appears to want.

The CR13sp3_cr_usergdr_en.pdf file contained excellent information about connecting to PeopleSoft for Crystal Reports 2013, but, it requires the BI Platform.  Do you have the same document for Crystal Reports 2011?  Perhaps there is something in that document that may help. Thank you, again.

[vitaly_izmaylov]

There is a document for CR 2011:

http://help.sap.com/businessobject/product_guides/CR2011/en/xi4sp9_cr_usergde_en.pdf

but I do not believe there is a difference for PeopleSoft connectivity.

[Former Member]

Vitaly,

Thank you for the document.  You were right, there is no difference in the connectivity to PeopleSoft -- even in 2011, they want you to have the BI Platform apparently.

This would be so much easier if Oracle was willing to help...

Thank you again.