Current Security Context Not Trusted When Using Linked Server From ABAP

[Matt_Fraser]

Hello,

I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.

The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was 4.7x2.00, also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.

Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. The application itself has been upgraded to ECC 6.0. I performed the migration with a homogeneous system copy, and everything has worked just fine. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as SAPServiceSID with a trusted connection, and with a SQL login as the schema owner (i.e., 'sid' in lowercase). From outside of R/3, I cannot make it fail. It works perfectly.

That all changes when I try to use the Linked Server within an ABAP application, however. The basic code in use is

EXEC SQL.

SET XACT_ABORT ON

DELETE FROM [SERVER\INSTANCE].DATABASE.dbo.TABLE

ENDEXEC.

The only thing different about this code from that before the upgrade/migration is the reference to [SERVER\INSTANCE] which previously used the alias of just SERVER.

The program short dumps with runtime error DBIF_DSQL2_SQL_ERROR, exception CX_SY_NATIVE_SQL_ERROR. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."

I have set the "trustworthy" property on the R/3 database, I have ensured SAPServiceSID is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but naturally that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).

What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).

Any insight someone could offer on this thorny problem would be most appreciated.

Best regards,

Matt

[brent_eckhout2]

I was able to workaround the problem with the SAP 'sid' database user having reduced permissions to where access to a remote database caused SS Error 15247 - current security context not trusted problem with linked servers. Create a separate DB user and login at the SQL Server level and then a new DBCON connection in TCODE DBCO or dbacockpit pointing back to the same SAP SQL Server database. Be sure to use the new SQL Server DB login and password credentials in the DBCON connection configuration. Within the ABAP program that will be accessing the remote database, be sure to connect to your new secondary connection to the same SAP database that has the linked server definition pointing to the remote server.

An example:

DATA: R_COUNT TYPE I VALUE 0.

EXEC SQL.

CONNECT TO 'SAPSID2' AS 'C1' <---- this connection uses the new SS user credentials, not sid or SAPServiceSID

ENDEXEC.

EXEC SQL.

SELECT * INTO :R_COUNT FROM OPENQUERY(LINK2RDB,

'select count(*) from REMOTEDB..REMOTETABLE')

ENDEXEC.

EXEC SQL.

DISCONNECT 'C1'

ENDEXEC.

*

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

SKIP.

WRITE: / R_COUNT.

[Matt_Fraser]

Hello Ramesh,

Thank you for your response, but unfortunately that does not solve the problem. In fact, we were not using impersonation, but just a straight SQL login, but when I try it using impersonation of the SAPServiceSID user (and yes, I ensured the user was defined as a logon in the remote server and had rights to the database), it makes no difference. I get the same result.

I should reiterate that this connection <i>works</i> when tested via SQL Management Studio (Query Analyzer), osql, etc. It is only when trying it via Native SQL from within ABAP that it fails.

--Matt

[former_member193399]

Matt,

Sorry that it is not working. Other option is running the SQL Profiler and see what is going on when you run you ABAP code. You can view all the SQL Statements issued to the SQL Server. That should give you some clue. But you will get overwhelmed when you connect the profiler to the server since the calls from SAP to SQL is enormous. So make sure you put some filters in the profiler. Let me know if this helps or contact me directly.

Ramesh

[Matt_Fraser]

Yes, I had tried SQL Profiler traces, on both source and target servers. Nothing shows up on the target server, and on the source server (where, yes, you're right, the trace instantly fills with all sorts of other things generated by SAP), the error is caught, but at least with the filters I've tried so far, it hasn't engendered any new information. I can see that the connection is being made with the SAPServiceSID NT user mapped to the sid schema user, which is a little confusing as the NT user has its own database access. However, this doesn't really help, as I have been able to prove that both users, NT and database, have the permissions required at the database level to perform the distributed transaction via Query Analyzer.

So, it does not appear to be security related, as far as I can tell. More and more I am beginning to think there is a bug in the SAP dbsl interface.

--Matt

[former_member193399]

Matt,

Sorry to hear that you are still having problem. In one of the post you mentioned that the query is working fine in the Query Analyzer. During that time you logged in as NT User or SQL Server? You also mentioned that you tried both. But just curious to know.

Thanks,

Ramesh

[Matt_Fraser]

Yes, I've tried this with both NT and SQL logins in the Query Analyzer / Management Studio, with identical results.

I have been working with SAP tech support on this issue, and this is what we've come up with so far.

The essential difference between running the distributed query from the Management Studio vs from within the R/3 application is the way the R/3 kernel establishes its security context when connecting to the database. The R/3 kernel connects to SQL Server as the NT user SAPServiceSID, and then executes a "SETUSER 'sid'" statement to establish the security context for the 'sid' schema in the database (this is new behavior from WebAS 6.40 and later, I believe, when schema installations began to be recommended by SAP, as opposed to all the tables being in the 'dbo' schema). The 'sid' database user has much reduced permissions from the SAPServiceSID NT user -- it is not a member of the sysadmin fixed server role, for instance -- thus adding to the overall security of the installation.

Normally "mixed mode" authentication is disabled on an R/3 installation, so it is not possible to logon interactively as the sid user. However, we have enabled it so that this linked server interface works coming the other direction (and that part does work), so I was able to connect directly as the 'sid' user and confirm that it CAN use the Linked Server. However, there is something inherently different about interactively logging on as the user vs switching to the user's context with a SETUSER or EXECUTE AS statement.

I have seen posts in other forums discussing the use of distributed transactions and the EXECUTE AS context switch. The essential problem, however, is that from within ABAP we cannot execute SETUSER or EXECUTE AS without causing R/3 to crash. We also cannot modify the permissions given to the 'sid' database user (like making it a sysadmin) without causing the same disastrous result, although I'm not certain why this last should be the case. We have been experimenting with exporting our Native SQL statements into a Stored Procedure with the EXECUTE AS context set on the procedure, but it hasn't worked. We still get the 15274 error ("Security Context Not Trusted") when we call the SP from within ABAP.

SAP is probably going to forward this issue to Microsoft for assistance. Naturally, I'm not hopeful for a resolution in time for our GoLive next weekend, so we are working on a different workaround plan, which will probably involve using externally scheduled SSIS packages to extract data from custom tables and export it to the remote system, thus removing all security contexts from association with the R/3 application entirely. If we can then find a way to trigger execution of the SSIS package from within ABAP, so the scheduling can be integrated, that would work much better. I haven't looked into that yet, so I don't know what possibilities exist, but I'm open to suggestions!

Regards,

Matt

[former_member193399]

Looks like you are pretty much in control of what is going on.

Like you mentioned I am not sure why it will be a disaster if you make the 'sid' as sysadmin. But definitely it is not recommended in security point of view. I have made 'sid' as sysadmin in a EP box in Sand Box environment and registered the server in management studio across network to manage the server temporarily and it worked fine. This is FYI.

Best of luck with the solution. And please post the resolution once you work out with SAP and MS.

Thanks,

Ramesh

[Matt_Fraser]

I'm not sure what Java-based systems like EP might do differently, but adding 'sid' to sysadmin in an ABAP system, or at least in OUR ABAP system, causes the application to instantly disconnect from the database, effectively crashing it. It is the same result as if you issue a SETUSER statement from within your ABAP program, essentially causing the application to not connect to the correct schema, so it can no longer find the tables. So, I'm wondering if changing the security role on the 'sid' user somehow interferes with the default schema for the user.

Anyway, once a solution is found, I will definitely post it.

--Matt

[Matt_Fraser]

Good news! We have got it to work. However, we did it in something of

a backwards way, and I'm sure you'll laugh when you see how it was done. Also, the solution depends upon the fact that the remote server is still using SQL Server 2000, and so doesn't have quite so many restrictions placed upon it for distributed transactions and Linked Servers as SQL Server 2005 now does.

At the heart of the solution is the fact that the Linked Server coming FROM the remote server TO our SAP system works fine. Finally, coupled with the knowledge that using DBCON on the SAP side to the remote server also does actually provide a connection (see Notes 323151 and 738371), we set up a roundabout way of achieving our goal. In essence, from ABAP, we set up the DBCON connection to the remote server, at which point all the Native SQL commands execute in the context of the remote server. From within that connection, we

reference the tables in SAP via the Linked Server defined on the remote

server, as if SAP were the remote server, selecting data from SAP and inserting it into the remote (but apparently local to this connection) tables.

So, to spell it out, we define a Linked Server on the remote server pointing back to the SAP server as SAPSERV, with a SQL login mapping defined on the remote system pointing back to a SQL login in the SAP database. We also define a connection to the remote server from SAP using DBCON, using that remote SQL login for authentication.

Then, in our ABAP code, we simply do something along the lines of

exec sql.

set connection 'REMOTE'

endexec.

exec sql.

connect to 'REMOTE'

endexec.

exec sql.

insert into REMOTE_TABLE

select * from SAPSERV.SID.sid.SAP_TABLE

endexec.

exec sql.

commit

endexec.

exec sql.

disconnect 'REMOTE'

endexec.

This is, of course, a test program, but it demonstrated that it worked,

and we were able to see that entries were appropriately deleted and inserted in the remote server's table. The actual program for use is a little more complex, in that there are about four different operations at different times, and we had to resolve the fact that the temp table SAP_TABLE was being held in a lock by our program, resulting in a deadly embrace, but our developer was able to work that out, and all is now well.

I don't know if this solution will have applicability to any other customers, but it works for us, for now.

SAPSERV, REMOTE, REMOTE_TABLE, and SAP_TABLE are, of course, placeholder names, not the actual server or table names, so as not to confuse anyone.

Best regards,

Matt

[former_member193399]

When creating the linked server in your SQL Server 2005, under "security" tab, did you enter the local login, impersonate(if the login id and password is same in the target sql server), remote user and remote password? this is important in order for the SQL Server 2005 to authenticate the remote server.

I hope this helps!

Ramesh