A PowerBuilder 12.6 DataWindow has SQLCA set as its transaction object:
dw_datawindow.SetTransOject(SQLCA)
SQLCA connects to MS SQL Server via ODBC and uses Windows Integrated Authentication.
SQLCA.DBMS = "ODBC"
SQLCA.DBPARM = "ConnectString='DSN=maindb;Trusted Connection=yes;'"
The other database that the app connects to uses a specific SQL user:
otherdb.DBMS = "ODBC"
otherdb.DBPARM = "ConnectString='DSN=otherdb;UID=dbuser;PWD=dbpassword'"
When selecting data (in the Retrieve() function), the correct database is queried.
When doing an Update(), the error below occurs (copied from dialog box):
Database Error in Row 1
SQLSTATE = 08004 [Microsoft][ODBC SQL Server Driver][SQL Server]The server principal "domain\username" is not able to access the database "otherdb" under the current security context.
No changes made to database.
INSERT INTO dbo.t_table ( column1, column2, column3, column4, column5, column6, column7, column8, column9, column10, column11, column12, column13, column14, column15, column16, column17, column18, column19)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
Inside the sqlpreview event, I check the transaction object with each execution (both Retrieve and Update) using GetTrans, and it appears that the transaction object is correct there.
However, the error reflects that the insert statement is being sent to the "otherdb" with Integrated Authentication credentials (whereas the otherdb transaction object specifies a SQL user instead).
Any suggestions?
Check for a trigger on the table being updated which references something in the other database.