BusinessObjects Credential Mapping with SQL Server Database

[venp81]

I have come across a few links related to this topic in the SAP Community forums however unable to find an answer. We are trying to use 'BusinessObjects Credential Mapping' in IDT's connection connecting to SQL Server database. We use LDAP for authentication to BOE (BI4.2 SP5 Patch600). The universe connection test is successful from the workstation but the report fails from BOE server because the user's network credentials are unable to bypass the service account SIA is running on. On the BOE server we have defined ODBC DSN for SQL Server database (Integrated Windows Authentication and not SQL Auth). I found this Kbase 1869952 to configure kerberos however the point #2 under 'Important things to note before engaging in this complex process' says this:

Kerberos SSO to the DB is not possible if you logged into BI with enterprise, LDAP, SAP, siteminder, trusted authentication, or anything other than AD/kerberos (KBA 1631734)

Has anyone successfully used BO credential mapping to SQL Server database with LDAP authentication? Is this supported or even doable?

Please note: We have the database credentials enabled for each of our users under CMC --> Users and 'Enable and update user's Data Source Credentials at logon time' under CMC - Authentication - LDAP

BO Credential mapping works for us with Oracle database when report is run from BOE server (BI Launchpad or CMC).

Appreciate any inputs.

[omacoder]

Following!! We have not gotten it to work.

We instead ended up using SQL Server local logins for each user and set the DBUser and DBPassword properties in each CMC user.

[venp81]

Thanks Brian. When you say SQL Server local logins, did you mean a separate account in SQL Server database for each ldap user and then update this password for each user in CMC -- Users? If my understanding is correct, that's quite a maintenance overhead especially during password reconciliation. Correct me if I am wrong.

[denis_konovalov]

I have fixed your tags, please select more careful next time,

[Joe_Peters]

Are you trying to do credential mapping (entering each user's ID and password in the CMC), or end-to-end SSO (connecting to the database using the SSO credentials obtained during login)?

The kb articles you linked to deal with end-to-end SSO, and the text you highlighted is pretty explicit about it only working for Windows AD authentication.

If you're using credential mapping, then there is no role for LDAP or AD in the process. It simply passes through the ID and password from the user's properties page in the CMC. This means that you can't use Windows Authentication for the ID in SQL Server -- you have to use SQL Server authentication instead. It's working for you in IDT because your workstation is doing Windows authentication to the database using your local Windows credentials. When refreshing a report on the server, it's attempting to authenticate with the server's Windows credentials.

So, your options are:

1) To use credential mapping, ensure your users have SQL Server authentication IDs on the database, then change the ODBC connection to "SQL Server authentication".

2) To use end-to-end SSO, switch to Windows AD authentication from LDAP.

[venp81]

Hi Joe,

Thanks for the explanation. I understand why it was working in IDT and not through the server but I wasn't quite sure how to skip that server's windows credentials.

Option 2 is not feasible for us at this moment. So I guess I was in the wrong direction when following that kbase.

Option 1: When you say 'ensure your users have SQL Server authentication IDs on the database', does this mean a separate SQL auth id for each of our users? This team wants to have the ability to run reports for only those who have individual access to the database due to the confidentiality of the data.

We have other teams who connect to SQL Server databases via BOE, they use one SQL Auth id and in their universe connection they use the option 'Use specified username and password' specifying the sql auth id and password (maintained in cyberark) and 'SQL Server Authentication' selected in ODBC on BOE server.

Thanks,

Venkat

[Joe_Peters]

When you say 'ensure your users have SQL Server authentication IDs on the database', does this mean a separate SQL auth id for each of our users? This team wants to have the ability to run reports for only those who have individual access to the database due to the confidentiality of the data.

Yes, and the ID must be using SQL Server authentication, not Windows Authentication.

[venp81]

So how does SQL Server know (authorize) when a user runs a report from BI Launchpad using his/her network credentials (LDAP authentication) with BO credential mapping used in the universe connection? assuming that the credentials of the SQL auth account for each user is going to be different than the individual network credential unless an integration is possible between sql server auth and LDAP accounts.

[Joe_Peters]

At runtime, BO will use the user ID and password that is saved in each user's "Database Credentials" fields in the user properties in CMC.