Remote Procedure Call

Question

Remote Procedure Call

beath

SSCrazy

Points: 2939
More actions
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

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

beath SSCrazy Points: 2939 More actions · Answer 2

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....

Christine Glew SSC Eights! Points: 827 More actions · Answer 3

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

Neville Pugh Valued Member Points: 62 More actions · Answer 4

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 5

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

Neville Pugh Valued Member Points: 62 More actions · Answer 6

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 7

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