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 SAP 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 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. 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 the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.
That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface 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 the service account 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 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
Hi, Matt
The error you came across is a pure security problem, hence I suggest you post your question in SQL Security Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1
Good Luck!
Ming.
|||I originally posted this message to the Database Access forum, as I was thinking it might be related more to the methods used to access the database, as opposed to a pure security issue, as there does not appear to be an issue when using Microsoft native tools. However, now I am not so sure.
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. 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.
Regards,
Matt
|||SETUSER cannot interact with linked servers and in SQL Server 2005 is marked as deprecated. In SQL Server 2005 we introduced a new EXECUTE AS and it has two different scopes: database or server.
The database scoped model is accessed via EXECUTE AS USER or by using the EXECUTE AS clause in a DB-scoped module (i.e. a SP, function, etc.). Because this model is permission based, by default the impersonated context is bound to the database where the impersonation took place, making this context invalid when you try to access other databases or when trying to access server resources (such as linked server information).
The server scoped model is accessed via EXECUTE AS LOGIN or by using EXECUTE AS on server scoped modules (server scoped DDL triggers). This impersonation is implicitly by the server, therefore the impersonated context is valid to access any database and server resources.
My guess is that you are using one of the database-scoped EXECUTE AS features and that’s why you are hitting this problem, can you please verify if this is correct, and in that case try using EXECUTE AS LOGIN instead? It should work with linked servers as long as long as the impersonated context is not a Windows account.
Please, let us know if this information was useful.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||Raul,
Thanks for this clarification. This is helpful, although it doesn't solve the problem. So, apparently we cannot define a Stored Procedure with a server-scoped execution context (CREATE PROC xxx WITH EXECUTE AS LOGIN = 'login' is apparently not legitimate). However it does seem that we can insert an EXECUTE AS LOGIN = 'login' statement within the body of the SP.
Nevetheless, either way, the stored procedure works fine when executed from a Management Studio query window. It does not work when called from the R/3 application.
So, to recap, the R/3 application connects to the database first as a Windows user (SAPServiceD02), and then behind the scenes (transparent to the user and any programs we might write within R/3) it executes a SETUSER 'd02' statement. d02 is a SQL login, and all the user tables are in the d02 schema in the D02 database. This behavior is built into the R/3 kernel and we cannot change that. Although we are running on SQL Server 2005, the application is built to run on either 2005 or 2000.
From SQL tools such as Management Studio, both d02 and SAPServiceD02 can access the linked server. Both have a login mapping defined to a SQL login on the remote server. Likewise, using a Stored Procedure saved in the d02 schema with EXECUTE AS 'linkadm' (linkadm is a SQL user mapped to the dbo schema in both the D02 and master databases, a member of sysadmin, and also mapped in the Linked Server to the remote user), both d02 and SAPServiceD02, and indeed other sysadmin users, can access the Linked Server when executing the SP from Management Studio.
When calling the SP from within the R/3 application, however, which is using the same security credentials as just described, but has that implicit SETUSER 'd02' context, calling the SP doesn't get around the fact that we can't access the Linked Server. We still get the 15274 error, just like when we tried to call the Linked Server directly from within R/3. All of this worked very well when the DBMS was SQL 2000 and the schema was dbo. After upgrading to 2005 and migrating to the d02 schema, our troubles began. The essential problem appears to be the combination of Windows Authentication, schema-based installation with the schema based on a non-Windows, non-sysadmin SQL login name, and distributed transactions.
I think at this point (GoLive is just a few days away, and this is a potential showstopper) we must find some completely different way of executing these distributed transactions, and then hope that in the future there will be a fix, whether from Microsoft or SAP I don't know, that will allow us to revert to a more robust and integrated method. Meanwhile, the users will just have to ignore the man behind the curtain pulling the strings to make this work.
Best regards,
Matt
|||You mentioned updating the R/3 application. Was it performing a SETUSER in the previous version as well?
Thanks
Laurentiu
Laurentiu,
No, I don't believe it was, unless you had an MCOD (Multiple Components One Database) installation, which was not usual. In prior releases the standard installation had all the user tables in the dbo schema. With the most recent releases, however, SAP is moving towards schema-based installations. I believe this might be for multiple reasons, including supporting having multiple applications running in a single database, but also to increase security by having the connections be made using a schema user that normally cannot be interactively logged on (by default, an R/3 installation uses Windows Authentication only, so SQL logins are disabled), and that does not have to be a member of sysadmin. However, I cannot be certain what all the reasons for the switch may be.
Regards,
Matt
|||SETUSER will not work with linked servers. This has been the case in SQL Server 2000 as well, so this issue appears to be hit because the R/3 application changed to perform a SETUSER. I believe that if it would have used an EXECUTE AS LOGIN instead, the issue would have been avoided, but with SETUSER, you are bound to get the 15274 error.
I suggest you contact the vendor of the R/3 application to get further help on this.
Thanks
Laurentiu
Yes, we had been working with SAP (the vendor) from the beginning to resolve this issue, and although they were working diligently with us, a resolution did not look to be forthcoming before our GoLive deadline. In fact, I believe they escalated the issue to Microsoft themselves. Nevertheless, I posted here hoping against hope for something that might help us out.
However, we have now resolved the issue, although in a roundabout way, and not one that will work in a wide array of customer scenarios. The solution depended upon the fact that the remote server, being still on SQL Server 2000, did not have as many restrictions placed upon it with regard to the use of Linked Servers -- at least, the Linked Server connection from the remote system back to SAP continued to work just fine, despite SAP having been upgraded to SQL 2005. Additionally, SAP does provide a native capability to create a client-based connection to remote databases from within their application. It doesn't use Linked Servers, but rather opens up a SQLNCLI-based connection from the SAP server to the remote server, and allows the developer to execute Native SQL code against the remote database. This capability is referred to as DBCON with SAP. The limitation on DBCON, unfortunately, is that when working within this connection, the context is entirely that of the remote database, so access to local (SAP) tables is not available while in that context.
However, access to the Linked Server defined on the remote system is available.
So, we are able to perform distributed transactions between the two systems by executing them in the context of the remote system, while still controlling that execution from the SAP system.
I thank everyone for their suggestions and assistance while we worked on this issue.
Best regards,
Matt
|||We experienced similar problems using Linked Server in combination with user context switching at a customer's site, although no SAP was involved. The scenario was getting data from Win2k3 SQL 2005 (target) by switching a user context to a domain user on another Win2k3 SQL 2005 (source). Logging on directly to the target with the domain user and getting the was no problem, also getting data with a sysadmin user on the target server via linked server on the source server actually worked. The source database also had the trustworthy property set.
The problem only occurred when connecting to the source server, switching the user context with "execute as user ..." and trying to get data from the target via linked server, which produced a connection error. What finally solved the problems was setting the remote connection properties in the surface area config to both TCP/IP and named-pipes, since the target only had tcp-ip activated. In the current config both protocols are activated on source and target server and now everything seems to work. Maybe these additional bits of experience are helpful to those struggling with similar problems.
No comments:
Post a Comment