Symptom
Using SAP Business One Query Manager you cannot Query data from another database restored in the same environment.
Reproducing the issue
Prerequisites:
You have restored two company databases, example Schema_A and Schema_B , into the same environment.
Scenario:
- Log on to SAP Business One and open company Schema_A.
- Go to Tools → Queries → Query Generator and try to query data from Schema_B using the query:
'SELECT * FROM [Schema_B].[dbo].OPOR' or 'SELECT * FROM "Schema_B".OPOR'
This behavior can also be reproduced when using Crystal Reports/Layouts from SAP Business One if their underlying queries point to multiple databases. Please, confirm the specific queries that these Reports/Layouts are executing by Opening them on SAP Crystal Reports Designer. Then, from the menu Database / Show SQL Query you will confirm the queries once their parameters are parsed. If the queries are pointing to data from multiple database the SAP Business One would fail to execute them. You can confirm by adapting them so they can be executed from the SAP Business One Query Manager where you should be prompted an error like the one detailed in the Actual behavior of this section of this note.
Expected behavior:
The query returns results from company Schema_B.
Actual behavior:
In the MS SQL Server version you receive error "Internal error (-1) occurred [131-183]".
In the SAP HANA version you receive one of the following errors:
1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation; 257 sql syntax error: line 1 col 8 'User-Defined Values' (CSHS) (at pos 8)
2). [SAP AG][LIBODBCHDB DLL][HDBODBC] General error; 258 insufficient privilege: Detailed info for this error can be found with guid '88DFE26117C96C4D98D2615223F4171D' 'Received Alerts' (OAIB)
Cause
Behavior Change
Solution
On SAP Business One version 10.0, to strengthen security, users cannot query data from other databases.
On SAP Business One Cloud, this behavior is driven by a change introduced on Cloud Control Center (CCC) 1.1 PL15, where Windows Authentication is no longer available as authentication type when creating new tenants; you must use SBO Managed Authentication for connections between the SAP Business One client and the tenant's company database. Please see the SAP Business One Cloud Administrator's Guide, to confirm that SBO Managed Authentication is now the default. SAP Business One Users do not connect directly to the database, but receive access credentials from the SLD. Please manually assign the privilege to the Database user if necessary (This meaning, the MS SQL or SAP HANA server user)
Notice that by default SAP Business One protects the execution of cross-database queries as databases might belong to different client. To modify this behavior, you could configure database privileges allowing cross-database queries only upon client's approval and under the SAP Business One Server Administrator responsibility.
First, check the database user configured for the database on the Service Landscape directory.
To give the privilege on SQL Server:
- Open SQL Management Studio, go to Security → Logins.
- Double-click the user and go to User Mappings.
- Choose the database you want to query
- In ‘Database Role Membership’ area, tick the checkbox “db_datareader”.
To give the privilege on HANA Server:
- Open HANA Studio, go to Security → Users
- Rigth-click, Open.
- Go to the Object Privileges Tab
- Select the schema in the Catalog Object area
- Tick the Select privilege
- Please note that Cross Instance queries are not supported.
SAP Business One Support describes this process as an example, but does not provide support on modifying these privileges as this is considered consulting and under the scope of the SAP Business One Server Administrator or Implementing partner.
