Post reply

Remote Procedure Call

  • September 2, 2004 at 4:16 am

    #86362

    I'm having some issues with remote procedure calls.

    ServerA and ServerB are both setup as linked servers. ServerA is SQL 2000/Windows 2000 and ServerB is SQL 2000/Windows 2003

    I can successfully run:

    exec ('[ServerA].master.dbo.xp_loginconfig') and also

    insert into tblConfig exec ('[ServerA].master.dbo.xp_loginconfig')

    I can also run exec ('[ServerB].master.dbo.xp_loginconfig')

    The problem is when I run:

    insert into tblConfig exec ('[ServerB].master.dbo.xp_loginconfig')

    The process just hangs. What is so different between calling the remote stored procedure (which I can do) and trying to insert the retrieved data into a table (which I can't)? Of the 20 servers I run this on it only fails on 2 of them and they are our only 2 Windows 2003 servers.

    Thanks

  • September 2, 2004 at 5:51 am

    #521482

    I would get with the people that manage that server and ensure that the ports, permissions, etc..  are set-up to allow the xp_loginconfig to run.  I would also make sure that the linked server is set-up correctly.

    I know that Win2K3 is set-up with a lot tighter security and hoops to jump through (I am currently testing it with SQL2K5).

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 2, 2004 at 7:54 am

    #521511

    It's pretty much me that manages the server! I don't think it's a SQL permission issue as I can execute the xp_loginconfig procedure fine - it's only when I'm trying to insert the results into a table that it hangs....

    I agree that Windows 2003 is a lot tighter on security and think the issue lies there somewhere....

  • September 10, 2004 at 4:49 am

    #522507

    Have you ticked the collation compatible box in the linked server options?

  • November 26, 2004 at 3:44 am

    #531582

    Sorry to jump onto this thread, but does anyone else have any ideas / solutions on this, as I've got exactly the same problem.

    One thing I do have is that the server it works on is Win2000, but the server it doesn't work on is a freshly built Win2003 box ... could this be something to do with it, and what (Windows ?!?) settings do I need to check ?

    Many thanks in advance !

    Neville, UK

  • November 26, 2004 at 4:34 pm

    #531641

    I believe the difference you are seeing is due to DTC security settings.  When my company upgraded to I Win 2003 ran into a similar problem.  The straight RPC call does not invoke a transaction, but the insert into a local table from a remote stored procedure does implicitly use a distributed transaction.  Windows 2003 modified the security settings for these (see:  http://support.microsoft.com/default.aspx?scid=kb;en-us;817064)

    You (or a Windows admin) should navigate to the following:

    • Start / Program Files / Administrative Tools / Component Services (or follow the path in the KB article above)
    • Expand the Component Services node until you reach "My Computer"
    • Right-click on My Computer and select Properties
    • Go to the MSDTC tab and select "Security Configuration"

    Verify that "Network DTC Access" is selected.  You may also need to select "Network Administration", "Network Transactions", and "Network Clients". 

    Also verify that the DTC Logon Account has network privileges.  The account NT AUTHORITY\NetworkService has priviliges; LocalSystem does not.

    At this point, stop and start MSDTC.  I think you also need to stop/start SQL Server service as well. 

    You may need to do the above on each Win 2003 server.

    After that retry insert into tblConfig exec ('[ServerB].master.dbo.xp_loginconfig') and see if it works.

     

    Hope this helps,

    Scott Thornburg

    Volt Information Sciences

     

  • November 29, 2004 at 5:27 am

    #531693

    Scott

    Superb, I passed on your post to our DBA and he checked all the settings and adjusted / altered where required (you're right, win2k3 does seem to be a lot more 'secure' on stuff like this).  Those changes and one reboot later and it's working great !

    Thanks also for explaining the difference between the two calls (i.e. one does a distributed transaction, the other doesn't ... it was driving me potty as to why one would work but the other wouldn't !)

    Thanks once again ... sorted !

    Cheers

    Nev

  • November 29, 2004 at 10:11 am

    #531753

    Nev,

    Glad it worked for you.  This seems to be a problem that trips up many who upgrade to Win 2003.  

    One point of clarification:  a full server reboot is not necessary after setting the DTC security checkboxes -- all that's needed is a stop / start of SQL Server service itself.  In some shops, it's much easier to stop a service than to bring down a server.

     

    Scott Thornburg

    Volt Information Sciences

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply