locked
Execute a stored procedure from different domain RRS feed

  • Question

  • Hi Guru,

    I have two stored procedures are on different servers and different domains. usp_A is on server A domain A. usp_B is on server B domain B. I want usp_A to fire off usp_B. Is that possible? I'm not so sure if linked server or other options are possible if we have firewall rule open properlty.

    Thanks,

    KongDBA

    Tuesday, May 29, 2012 1:50 PM

Answers

  • this link contains good advice:

    http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/329709ca-349d-490d-9b42-7443caa97364



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Maggie Luo Wednesday, June 6, 2012 9:31 AM
    Tuesday, May 29, 2012 7:01 PM
  • From the SQL Server side of things it is not much that different. You set up a linked server and that's that. Well, since they are in different domains, it is likely that will need to set up login mapping, since it is unlikely that a user who is logged in your server will be authenticated on the other server - there has to be trust between the domains.

    Now, you mention big and evil firewalls. And if there are firewalls between the servers, you will need to talk with the network administrator to open a connection in the server. This does not have much do with SQL Server.

    If the network admin will not agree, you will need to consider different solutions. Exactly what do you want to achieve? Must the execution be synchronous? If you can accept an asynchronous solution you could set up a Service Broker dialog that jumps multiple servers before it reaches the target server. Not that this is trivial. It certainly takes some work and calls for a good understanding of Service Broker to get going.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Wednesday, June 6, 2012 9:31 AM
    Tuesday, May 29, 2012 7:35 PM

All replies

  • Hi,

    You could use linked servers.  Set one up called "ServerB" on ServerA, and in the stored proc on ServerA, you can say: EXECUTE('exec mydatabase.myschema.USP_B') AT ServerB



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Tuesday, May 29, 2012 1:59 PM
  • Hi Andrew,

    Is it possbile to execute sp without Linked servers ? In SERVER B domain.

    Tx


    subu

    Tuesday, May 29, 2012 2:40 PM
  • you could use opendatasource http://msdn.microsoft.com/en-us/library/ms179856.aspx

    what's the problem with linked servers?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Tuesday, May 29, 2012 3:03 PM
  • Andrew,

    We have a big firewall blocks between these two domains. Would you be able to provide me a link to setup linked servers across domains?

    Thanks,

    KongDBA

    Tuesday, May 29, 2012 3:17 PM
  • this link contains good advice:

    http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/329709ca-349d-490d-9b42-7443caa97364



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Maggie Luo Wednesday, June 6, 2012 9:31 AM
    Tuesday, May 29, 2012 7:01 PM
  • From the SQL Server side of things it is not much that different. You set up a linked server and that's that. Well, since they are in different domains, it is likely that will need to set up login mapping, since it is unlikely that a user who is logged in your server will be authenticated on the other server - there has to be trust between the domains.

    Now, you mention big and evil firewalls. And if there are firewalls between the servers, you will need to talk with the network administrator to open a connection in the server. This does not have much do with SQL Server.

    If the network admin will not agree, you will need to consider different solutions. Exactly what do you want to achieve? Must the execution be synchronous? If you can accept an asynchronous solution you could set up a Service Broker dialog that jumps multiple servers before it reaches the target server. Not that this is trivial. It certainly takes some work and calls for a good understanding of Service Broker to get going.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Wednesday, June 6, 2012 9:31 AM
    Tuesday, May 29, 2012 7:35 PM