DB Credentials with SQL Server - SAP BO

[former_member254991]

We are using below architecture

4.1 SP08 - Windows Server 2008

SQL Server 2012

Would like to know if it is possible to use "Enable database credentials" to authenticate the user who is viewing the report to SQL Server

1) On SQL Server side added an AD group which will have access to the database (So this will be NT user)

2) On BO Side

a) Created an ODBC with SQL server driver with NT authentication

b) Created a relational connection with "Use BusinessObjects Credential Mapping"

c) Updated user properties with DB credentials (domain\username)

The problem is since ODBC is using NT authentication, SIA's service account is used to fetch the data (since this account does not have access the request fails) And yes, requirement is that SIA service account should not have access

If SQL authentication is used in ODBC, correct username is passed however SQL server fails the request since an attempt to do an SQL authentication with an NT user is done

[BasicTek]

If using ODBC or OLE DB on windows OS then the driver will pull the username running the process (in this case the SIA > reporting server). I have not tested the scenario using JDBC (which SQL should now support) but I do know when connecting on non windows OS this issue does not occur (so it's the way the drivers work)

If you use ODBC or OLE DB with SQL accounts then DB credential mapping will work fine but you need to use SQL accounts not AD accounts (which usually isn't desirable either).

A 3rd possibility that I have not tested is using JDBC to connect to SQL, as JDBC would be OS independant it should work the same on unix, Linux, and windows and might allow you to use predefined or credential mapping with AD accounts. Let me know if it works and I'll update KBA 2043151 with that solution.

Regards,

Tim

[former_member254991]

Thanks for response Tim

Will check on JDBC, if at all we do not take the route of JDBC the only possible option is to use Kerberos I suppose ?

[former_member254991]

Just tested with JDBC, unfortunately it does not work.

During creation of connection and universe all request are sent through the DB credentials of the user (when credential mapping is selected). However once the universe is published, the request fails when we try to create a report on top of it

At SQL Server side we can see that the request is sent through DB Credentials till the time universe is developed and published. As soon as we try to create a report on top of the published universe SIA's service account is used

So unfortunately it seems that DB Credential mapping would not be of any use especially if

1) It is SQL Server

2) NT authentication needs to be used

This will work perfectly if at all SQL Authentication was to be used, but that would not be feasible as we will have to create unknown number of SQL accounts

I am not sure if this would be deemed as by design or a bug or there would be a way around this

As of now I feel Kerberos would be our best bet

[BasicTek]

unless you are willing to create SQL accounts. Potentially there is another option called @ DBuser where you could create a a bunch of SQL accounts matching the usernames in BI, assign them all the same complex password.

In the connection you use @ DBuser + complex password. Then when each user access the connection it uses their username combined with the secret password and provides an SSO like experience, you can setup your SQL permissions per each SQL user.

This has not been documented (except how to use @ DBuser which replaces an older variable @ BOuser)

-Tim

[BasicTek]

instead of DB credentials, test the same with predefined (AD user/password) I'm surprised a java driver would be able to use a logged in AD user, kerberos would work for on demand but you would still need a solution for scheduled reports. I also replied to your previous post with a long shot option for SQL accounts

-Tim

[former_member254991]

I actually started with the thought of using DBUSER but stuck on that because

1) It seems to be documented that DBUSER variable is not supported with IDT (Replaced by "Use BusinessObjects credential mapping") (SAP Note 1737293)

2) In UDT as well, even after creating a connection after passing DBUSER and DBPASS (Not sure if this one is still supported or obsolete) - connection is success but not able to create any report (Error - No business layer sent back from server)

So that's the reason came up with using credential mapping in connection + secondary credential of user

[BasicTek]

It didn't work for a while but eventually it was fixed

https://apps.support.sap.com/sap/support/knowledge/preview/en/2209200

[former_member254991]

So we decided to stick with SSO to SQL Server database via Kerberos

Everything looks fine, only problem is Webi report fails to refresh the first time and subsequent refreshes works

1) The very first refresh it throws error "cdzsrv_error_en_30600" (We can see users login at SQL Server end)

2) Subsequent refreshes works

3) Kill the user session in SQL Server and try refresh again. The same error is seen

Fun facts,

1) Works in standalone server with 4.1 SP07

2) Works on subsequent refreshes on clustered 4.1 SP08

I saw a couple of notes on this

1542570 - Single Sign On to the database fails while running a WebI report for the first time

2059379 - First refresh of webi fails with error cdzsrv_error_en_30600 (Error: INF ) (I am not seeing this note anymore, weird)

[BasicTek]

The SAP note 2059379 was archived as part of a cleanup process because it was never associated with a fix.

If you create an ultra simple CR report with only 1 row of data do you see the same behavior?

As you have a clustered server are both SIA's using the same service account? Is the SPN in the CMC in the format of principal/account.domain.com?

Regards,

Tim

[former_member254991]

Yes, it is a very simple Webi report on top of a UNX universe that I have created. In fact backend just has two rows, I created it as a test to check connectivity

Yup, both SIA's are on same account and CMC has BOCMS/accountname (does not have domain in it as SPN was registered in that way). But would this be related to SSO to database as SSO to all BO applications works perfectly

I tested this on 4.2 SP05, same behavior. First refresh there is same error mentioned above and subsequent refreshes work

[former_member254991]

This sounds like a bug now

1) 4.1 SP07 standalone - Works

2) 4.1 SP08 clustered - Does not work

3) 4.1 SP08 standalone - Works

4) 4.2 SP05 clustered - Does not work (Works with UNV though)

So all in all it is not working in clustered environment on first refresh. I will have to capture some logs on this to see what's going on

[BasicTek]

The SPN has quite a bit of leniency when performing SSO to BI or manual login, as long as the value in the CMC = an SPN (any SPN value such as spaghetti/meatballs). However when performing SSO to the DB this is not the case. When performing SSO with unv it can get a lot more complicated.

So webi opening a unv or webi opening a unx with no LOV uses c++ libraries and and only the webi report server is involved in the communication using Microsoft SSPI (This is the reason for asking for a simple 1 row report).

Now if you add LOV to a webi > unx then the APS gets involved, which is using java (GSSapi vs SSPI) and it requires additional configuration. any APS connecting to a DB must have krb5, bsclogin, keytab. (KBA 1688079) and the format of the CMS SPN must match the principal/something.domain.com. I've seen setup with user or user@domain fail for SSO2DB but I think if your SPN is principal/something it will be ok especially for simple reports

logging would be a good idea and microsoft kerberos logging on the BI server could help.

-Tim

[former_member254991]

Issue was resolved. Was not related to SPN for SQL server or on BO side (SQL Server is running on Local System so SPN was not required, at least till now :D)

For some weird reason read/write access was required on Data folder for the user who will run the report (This will be replaced by an AD group).

Weird because, this was not required on a standalone server

So all in all,

1) SQL Server runs on Local System

2) BO setup with Kerberos SSO

3) Connection created using IDT using an SQL Server Native Client 11.0 ODBC which is setup with NT authentication

4) Connection uses "Use single sign on" option (Will not work with schedules, but that's ok for business)

5) Provide access on database to an AD group. Same AD group should have read/write access to "Data" folder

6) Create UNX universe using the above mentioned connection with a user who is part of above AD group and is a part of BO

Thanks for assistance and patience Tim

[BasicTek]

That's great information to know, how the heck did you figure out it was the data folder? Is that the data folder under BI-install directory? or something in the windows OS? I've never seen any type of local user access be required in the past, (so this could be a coding issue). I'll be sure to write up a KBA on it if you can give me the details.

Regards,

Tim

[former_member254991]

Yes, it is Business Objects Data folder. It is actually mentioned in SAP note

1542570 - Single Sign On to the database fails while running a WebI report for the first time

But I never implemented it as I found it not to be feasible and that it worked in standalone environment without having this access. Additionally it did not work with just Read access, had to provide Write access as well

But generally speaking, we should not be going to this extreme of giving access to users on server box

[BasicTek]

I agree, I wrote that back in 2010 (thought it sounded a little familiar), so I've seen this before but considering the article was never updated in 8 years that goes to show this is not the norm. Possibly there are other ways to solve the issue like putting the AD users group in the local users or something along those lines. I'm working with development on some SSO2DB for the coming releases I'll see if I can find some more info, and definitely update that KBA with what I find.

Thanks for passing the knowledge. Glad it's working!

-Tim