Skip to Content

ODBC driver of SAP SDK 16.0 cannot change database/loginID/password at Microsoft Query

This is very similar to KBA 2019465, but different one.

I tried to get the contents of two tables on different databases to Microsoft
Excel using ODBC of SDK 16.0 SP02 PL05 on Windows7.

Retrieving the contents of first table was successful.
Then I changed database/loginID/password at Microsoft Query logon screen to
get rows of second table, but failed with "invalid user".

Sorry, it is complicated and time consuming to reproduce the problem.

*** How to reproduce the issue ***

1. Prepare 2 databases (e.g. testdb1 and testdb2) on the target ASE server
2. Create a table on each databases
For example,

use testdb1
go
create table t1 (col1 int, col2 char(10))
go
insert into t1 values (1, "TEST1")
insert into t1 values (2, "TEST2")
go

use testdb2
go
create table t2 (col1 int, col2 char(10))
go
insert into t2 values (2, "TEST2")
insert into t2 values (3, "TEST3")
go

3. Create logins and users to access each tables

create login testdb1_user with password testdb1_user
go
create login testdb2_user with password testdb2_user
go

use testdb1
go
sp_adduser testdb1_user
go
grant select on t1 to testdb1_user
go

use testdb2
go
sp_adduser testdb2_user
go
grant select on t2 to testdb2_user
go

4. Prepare ODBC DSN to connect to the target ASE server using SDK 16.0 SP02 PL05

DSN Configration

Data source name: testdb-dsn
Server Port: 23456
Database Name: testdb1
Login ID: testdb1_user

5. Start Microsoft Excel

6. Open [Data] tab

7. Select [From Other Sources]-[From Microsoft Query]

8. Select the target ODBC DSN and enter the password of "testdb1_user"

9. Select table "t1" on database "testdb1" on the Query Wizard screen

10. Contents of table "t1" are displayed on Excel

11. Open [Data] tab again

12. Select [From Other Sources]-[From Microsoft Query]

13. Select the same ODBC DSN and logon screen pops up again

Change database to "testdb2"
Change loginID and password to "testdb2_user"

14. Select table "t2" on database "testdb2" on the Query Wizard screen

15. Tried to execute the query but failed with

"Server user id 11 (=testdb2_user) is not a valid user in database 'testdb1'"

When I use ODBC of SDK 16.0 GA (16.0.00.00), it works fine.
I can get the result of both "testdb1..t1" and "testdb2..t2" on Excel
by changing database/loginID/password at logon screen of Micosoft Query.

But when I try the same thing with SDK 16.0 SP02 PL05 (16.0.02.05),
it ends up with "invalid user" when accessing second table.

Is this an expected behavior or malfunction to be modified?

Regards,
Kazuo Otani

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 02, 2017 at 06:09 PM

    Hi,

    I wasn't able to reproduce this but only have SDK 16.0 SP02 PL04 & 16.0 SP03 PL01 which were easily available to me.

    I would check the mda script version on the ASE.
    sp_version

    This should be greater than the odbc version trying to connect.
    https://wiki.scn.sap.com/wiki/x/LQxYGQ

    KBA# 2302255

    Please try SDK 16.0 SP03 PL01.
    If this persists you can open an incident as well.

    Tested versions Excel 2010 and 2016.

    Regards,
    Ryan

    Add comment
    10|10000 characters needed characters exceeded