none
Accessing an Oracle linked server from a stored procedure with the EXECUTE AS clause produces "Access to the remote server is denied because the current security context is not trusted"

    Question

  • This problem is easy to replicate following these steps:

    Create an Oracle linked server and set connection to "be made using this security context". Provide the remote user and password.

    Create a stored procedure under the dbo schema with the EXECUTE AS OWNER clause. Make sure the owner of the dbo schema is dbo. Within the stored procedure, query the linked server:

    SELECT * FROM OPENQUERY(MYLINKEDSERVER,'Select C1, C2 FROM XYZ.MY_VIEW')

    Create a user and grant this user EXECUTE privilege on the stored procedure. Do not grant any other privileges to this user.

    Log in as that user and execute the stored procedure.

    Although every statement within the stored procedure should run successfully under the security context of the owner of the stored procedure, querying the linked server will fail with the message "Access to the remote server is denied because the current security context is not trusted".

    Since the linked server is configured to ignore the security context of the caller and use an explicit remote user and password, I don't see the reason why it fails. It must have something to do with the context switch that happens when the stored procedure is called and then another context switch that happens when querying the linked server. Thoughts?


    SQL Server Database Administrator

    Monday, October 07, 2013 7:01 AM

Answers

  • That is totally true. I was just making sure you did not try something fancy withe a user matching - then the reason for it not working could have been different.

    In this case it's quite simple: You are running the code within the explicit context of just a database user.

    And such a user is limited to the current database, unless things like cross database chaining, trustworthy or certificates come into play.

    And there you also have your option...

    - unless you can simply revert back to the use of the "original login" - for which a valid server-level token would exist.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, October 08, 2013 1:20 AM
  • Andreas, when using the EXECUTE AS OWNER clause, the stored procedure is being executed under dbo, which is the stored procedure owner in my case. It could be any other database user, but still it will be just a database user without server level visibility as you have pointed out. REVERT has no effect within the stored procedure, and EXECUTE AS LOGIN = ORIGINAL_LOGIN() raises an impersonation error, and that is because there is no way one could grant IMPERSONATE on a login to a database user.

    You are right. The solution is to either make the database Trustworthy or sign the stored procedure. Trustworthy creates more problems than it solves, so I am going with signing the stored procedure with a certificate and mapping a login to the certificate. That login will have the AUTHENTICATE SERVER privilege, necessary to access server scoped objects such as linked servers. The caller still just needs EXECUTE on the stored procedure, nothing else.

    Here is the code so it can help others:

    USE [MyDatabase]
    GO
    CREATE PROCEDURE [dbo].[TestProc] WITH EXECUTE AS OWNER
    AS
    BEGIN
    	SELECT * FROM OPENQUERY(MYLINKEDSERVER,'Select c1, c2 FROM XYZ.MY_VIEW')
    END
    GO
    -- This will fail
    EXECUTE [dbo].[TestProc]
    -- Create a certificate to sign stored procedure with
    CREATE CERTIFICATE [TestProcCertificate]
    ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
    WITH SUBJECT = 'Certificate for signing TestProc Stored Procedure';
    GO
    -- Backup certificate so it can be create in master database
    BACKUP CERTIFICATE [TestProcCertificate]
    TO FILE = 'C:\TestProcCertificate.CER';
    GO
    -- Add Certificate to Master Database
    USE [master]
    GO
    CREATE CERTIFICATE [TestProcCertificate]
    FROM FILE = 'C:\TestProcCertificate.CER';
    GO
    -- Create a login from the certificate
    CREATE LOGIN [TestProcLogin]
    FROM CERTIFICATE [TestProcCertificate];
    GO
    -- The Login must have Authenticate Sever to access server scoped objects
    -- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
    GRANT AUTHENTICATE SERVER TO [TestProcLogin]
    GO
    USE [MyDatabase]
    GO 
    -- Sign the procedure with the certificate's private key
    ADD SIGNATURE TO OBJECT::[dbo].[TestProc]
    BY CERTIFICATE [TestProcCertificate] WITH PASSWORD = '$tr0ngp@$$w0rd';
    GO
    -- This will succeed
    EXECUTE [dbo].[TestProc]

    The only problem is that if we ALTER the stored procedure, we need to make sure we sign it again.

    Thank you, Andreas. And thanks also to Jonathan Kehayias for writing a great article about a similar issue, on which I based my script:

    Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail


    SQL Server Database Administrator

    Thursday, October 10, 2013 5:13 AM

All replies

  • I am wondering how exactly you set up the linked server's security.

    What User/Login did you enter there?

    Could you please share a Screenshot or Code?


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 07, 2013 9:38 AM
  • Hi Andreas,

    The linked server is set up the simplest possible way. There is really nothing special about it. I just selected "be made using this security context:" and typed the Oracle user name and password.

    It works perfectly when I run a query against the linked server outside that stored procedure or even within that stored procedure if I remove the EXECUTE AS OWNER clause, so surely it has nothing to do with the linked server security, hence my suspicion the error occurs due to a security context switch that happens when calling the stored procedure under the scenario I have described.

    By the way, using SQL 2008 R2 Enterprise 64Bit build 10.50.4270


    SQL Server Database Administrator

    Tuesday, October 08, 2013 1:05 AM
  • That is totally true. I was just making sure you did not try something fancy withe a user matching - then the reason for it not working could have been different.

    In this case it's quite simple: You are running the code within the explicit context of just a database user.

    And such a user is limited to the current database, unless things like cross database chaining, trustworthy or certificates come into play.

    And there you also have your option...

    - unless you can simply revert back to the use of the "original login" - for which a valid server-level token would exist.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, October 08, 2013 1:20 AM
  • Hi Marcelo,

    According to your error message, we need to verify if your user account have necessary permission on both source and remote server database.

    There is a similar issue about this error , you can refer to it.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-us/eb9abea0-5ceb-4322-90b6-7444c33b776e/current-security-context-is-not-trusted?forum=sqldataaccess

    For more information about creating a linked server to access an Oracle database instance, you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms190618(v=sql.105).aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Tuesday, October 08, 2013 3:43 AM
  • That is totally true. I was just making sure you did not try something fancy withe a user matching - then the reason for it not working could have been different.

    In this case it's quite simple: You are running the code within the explicit context of just a database user.

    And such a user is limited to the current database, unless things like cross database chaining, trustworthy or certificates come into play.

    And there you also have your option...

    - unless you can simply revert back to the use of the "original login" - for which a valid server-level token would exist.

    Yes the code is running under the database dbo user (stored procedure owner), which I understand is limited to database boundaries.

    If I remove the EXECUTE AS OWNER clause, the stored procedure will run the linked server query successfully. In this case, the code is running under a database user with EXECUTE privilege. This user is also limited to database boundaries.

    Connection to the linked server should always use the explicitly defined Oracle user and password, regardless of the circumstances, so whatever security context the query is executed under, that should not matter at all.

    I understand we need to worry about database chaining, trustworthy and certificates, particularly when the remote system is another SQL box, but in this case the remote system is an Oracle unix box and connecting to the Oracle database is just a matter of providing a user name and password.

    I tried editing the stored procedure and adding REVERT just before the linked server query (just to see if I could revert to the security context of the caller), but this has no effect as the session user name remains dbo (stored procedure owner). I also tried adding EXECUTE AS LOGIN = ORIGINAL_LOGIN() just before the linked server query (to see if I could query the linked server under the security context of the original login) but it fails saying the principal cannot be impersonated or I do not have permission. Anyway, I did not expect any of these to work and by no means I would use it as a solution.

    The obvious solution would be to move the linked server query out of the stored procedure, but unfortunately the process design relies on it being executed within the stored procedure.

    Thanks for your help.


    SQL Server Database Administrator

    Tuesday, October 08, 2013 8:02 AM
  • Hi Marcelo,

    According to your error message, we need to verify if your user account have necessary permission on both source and remote server database.

    There is a similar issue about this error , you can refer to it.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-us/eb9abea0-5ceb-4322-90b6-7444c33b776e/current-security-context-is-not-trusted?forum=sqldataaccess

    For more information about creating a linked server to access an Oracle database instance, you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms190618(v=sql.105).aspx

    Thanks,
    Sofiya Li

    Sofiya, it does not apply to may particular scenario, but thanks anyway.

    SQL Server Database Administrator

    Tuesday, October 08, 2013 8:03 AM
  • ...

    And such a user is limited to the current database, unless things like cross database chaining, trustworthy or certificates come into play.

    And there you also have your options...

    ...

    (2) I understand we need to worry about database chaining, trustworthy and certificates, particularly when the remote system is another SQL box, but in this case the remote system is an Oracle unix box and connecting to the Oracle database is just a matter of providing a user name and password.

    I tried editing the stored procedure and adding REVERT just before the linked server query (just to see if I could revert to the security context of the caller), but this has no effect as the session user name remains dbo (stored procedure owner). I also tried adding EXECUTE AS LOGIN = ORIGINAL_LOGIN() just before the linked server query (to see if I could query the linked server under the security context of the original login) but it fails saying the principal cannot be impersonated or I do not have permission. Anyway, I did not expect any of these to work and by no means I would use it as a solution.

    ...

    (2) not quite:

    Setting the database to Trustworthy or signing the procedure with a certificate are the most common options of granting server-level access. What's on the Oracle side has nothing to do with it, as we first just have to get access to the linked server. Everything from there on will be handled by the user you provided, as you stated correctly. Execute AS Login would also be a valid option when the appropriate Impersonate Permission has been granted.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, October 08, 2013 11:57 AM
  • Andreas, thanks for the input. I will keep that in mind when I get back to this issue later on.

    SQL Server Database Administrator

    Wednesday, October 09, 2013 6:09 AM
  • Andreas, when using the EXECUTE AS OWNER clause, the stored procedure is being executed under dbo, which is the stored procedure owner in my case. It could be any other database user, but still it will be just a database user without server level visibility as you have pointed out. REVERT has no effect within the stored procedure, and EXECUTE AS LOGIN = ORIGINAL_LOGIN() raises an impersonation error, and that is because there is no way one could grant IMPERSONATE on a login to a database user.

    You are right. The solution is to either make the database Trustworthy or sign the stored procedure. Trustworthy creates more problems than it solves, so I am going with signing the stored procedure with a certificate and mapping a login to the certificate. That login will have the AUTHENTICATE SERVER privilege, necessary to access server scoped objects such as linked servers. The caller still just needs EXECUTE on the stored procedure, nothing else.

    Here is the code so it can help others:

    USE [MyDatabase]
    GO
    CREATE PROCEDURE [dbo].[TestProc] WITH EXECUTE AS OWNER
    AS
    BEGIN
    	SELECT * FROM OPENQUERY(MYLINKEDSERVER,'Select c1, c2 FROM XYZ.MY_VIEW')
    END
    GO
    -- This will fail
    EXECUTE [dbo].[TestProc]
    -- Create a certificate to sign stored procedure with
    CREATE CERTIFICATE [TestProcCertificate]
    ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
    WITH SUBJECT = 'Certificate for signing TestProc Stored Procedure';
    GO
    -- Backup certificate so it can be create in master database
    BACKUP CERTIFICATE [TestProcCertificate]
    TO FILE = 'C:\TestProcCertificate.CER';
    GO
    -- Add Certificate to Master Database
    USE [master]
    GO
    CREATE CERTIFICATE [TestProcCertificate]
    FROM FILE = 'C:\TestProcCertificate.CER';
    GO
    -- Create a login from the certificate
    CREATE LOGIN [TestProcLogin]
    FROM CERTIFICATE [TestProcCertificate];
    GO
    -- The Login must have Authenticate Sever to access server scoped objects
    -- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
    GRANT AUTHENTICATE SERVER TO [TestProcLogin]
    GO
    USE [MyDatabase]
    GO 
    -- Sign the procedure with the certificate's private key
    ADD SIGNATURE TO OBJECT::[dbo].[TestProc]
    BY CERTIFICATE [TestProcCertificate] WITH PASSWORD = '$tr0ngp@$$w0rd';
    GO
    -- This will succeed
    EXECUTE [dbo].[TestProc]

    The only problem is that if we ALTER the stored procedure, we need to make sure we sign it again.

    Thank you, Andreas. And thanks also to Jonathan Kehayias for writing a great article about a similar issue, on which I based my script:

    Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail


    SQL Server Database Administrator

    Thursday, October 10, 2013 5:13 AM
  • Hi Marcelo,

    Thanks for your post and I am glad to hear that the issue is resolved. Thanks for you posting a reply to share your solution and I will mark it as answer. That way, other community members could benefit from your sharing.

    Regards,
    Sofiya Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 10, 2013 5:17 AM
  • Andreas, when using the EXECUTE AS OWNER clause, the stored procedure is being executed under dbo, which is the stored procedure owner in my case. It could be any other database user, but still it will be just a database user without server level visibility as you have pointed out. REVERT has no effect within the stored procedure, and EXECUTE AS LOGIN = ORIGINAL_LOGIN() raises an impersonation error, and that is because there is no way one could grant IMPERSONATE on a login to a database user.

    ...

    Just so this is all correct:

    Yes, indeed one could simply

    USE master;
    GRANT IMPERSONATE ON LOXIN_X TO USER_Y

    That is quite possible.-

    EDIT: But only in such scenarios where the current database is master!
    In General it is not possible to grant a user Impersonate on a Login!

    But the other approaches that I already mentioned are way better in terms of clear architecture, so yes, stick with Certificate, that's the most elegant way. :-)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Edited by Andreas.WolterMVP Thursday, October 10, 2013 3:26 PM correcting and making sure this works only in master-db
    Thursday, October 10, 2013 5:33 AM
  • Andreas, sorry mate but that will not work. You can have a db user impersonate another db user, or a login impersonate another login. But you can't have a db user impersonate a login. So this will work:

    GRANT IMPERSONATE ON USER::[DBuser1] TO DBuser2
    GO
    GRANT IMPERSONATE ON LOGIN::[login1] TO login2
    GO

    But this will not:

    GRANT IMPERSONATE ON LOGIN::[login1] TO DBuser1
    GO


    SQL Server Database Administrator

    Thursday, October 10, 2013 6:17 AM
  • Andreas, sorry mate but that will not work. You can have a db user impersonate another db user, or a login impersonate another login. But you can't have a db user impersonate a login....


    You are of course totally right.

    Shame on me. My only excuse is the JetLag. (dumb enough too answer at such a time after landing :-D)

    - Unless the database in use is master itself - a user cannot be Granted Impersonate on a Login, correct.

    I’ll update my late-night comment so nobody gets this wrong answer..

    That’s like testing transactions with Tempdb *lol*



    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, October 10, 2013 3:21 PM