November 13, 2002 at 9:57 am
Here is my scenario:
I have 2 servers. One local server called SOCMON, the othe called GREP05 (which is in a DMZ). I use SOCMON to query data from GREP05 and pull down graph statistics into a table on SOCMON. Web users can click a link on our website which queries SOCMON and pulls data to display in a graph applet. The job is supposed to get the data from GREP05 and populate SOCMON so the users can see the data faster by pulling from 1 server rather than going from SOCMON to GREP05 and back.
The problem:
When I ran the job, I received this error:
Could not connect to server 'GREP05' because 'sqlservice' is not defined as a remote login at the server.
I changed the Owner to a user who is setup as a remote user to this server (this user can also execute RPCs from SOCMON to GREP05, which does work), and I receive this error:
Remote access not allowed for Windows NT user activated by SETUSER.
I am trying to execute a TSQL script which runs on SOCMON and calls a RPC on GREP05. When I execute this in Query Analyzer it works fine. But, when run as a job it fails.
Can anyone help me resolve this issue? I would greatly appreciate it!
November 13, 2002 at 10:09 am
quote:
Here is my scenario:I have 2 servers. One local server called SOCMON, the othe called GREP05 (which is in a DMZ). I use SOCMON to query data from GREP05 and pull down graph statistics into a table on SOCMON. Web users can click a link on our website which queries SOCMON and pulls data to display in a graph applet. The job is supposed to get the data from GREP05 and populate SOCMON so the users can see the data faster by pulling from 1 server rather than going from SOCMON to GREP05 and back.
The problem:
When I ran the job, I received this error:
Could not connect to server 'GREP05' because 'sqlservice' is not defined as a remote login at the server.
I changed the Owner to a user who is setup as a remote user to this server (this user can also execute RPCs from SOCMON to GREP05, which does work), and I receive this error:
Remote access not allowed for Windows NT user activated by SETUSER.
I am trying to execute a TSQL script which runs on SOCMON and calls a RPC on GREP05. When I execute this in Query Analyzer it works fine. But, when run as a job it fails.
Can anyone help me resolve this issue? I would greatly appreciate it!
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 10:12 am
Sorry for the blank reply.
Have you set up the SQL Server Agent account to logon to the remote server? This is the account that will be considered your local users when trying to connect to your remote sql server.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 10:35 am
Hi Greg,
The SQL Server Agent is trying to pass Windows authentication instead of SQL authentication. Since these servers are not in the same domain, this doesn't work. I need to use SQL authentication, but am unaware of how to do this. Is there anyway to override this authentication in the job?
November 13, 2002 at 10:43 am
I'm doing just what you are and here is what I have done.
On the box that runs the SQL Server Agent job, modify your linked server properties to include a local login that is the SQL Server agent domain account, and have it map to a SQL Server account on the remote server.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 11:23 am
We already have a remote server defined with a user, which works. If we try and add a linked server it says the server already exists. We removed the remote server entry and added a linked server entry but now the RPCs fail. What is the difference between these 2? Also, is there any alternative to the linked server solution you provided?
November 13, 2002 at 12:37 pm
Did you set the Server Options to allow "RPC Out" on the new linked server definition?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 12:44 pm
Yes, but it didn't work. Still searching for a workaround...
November 13, 2002 at 1:00 pm
What is the actual error are you getting?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 1:08 pm
When I set up the remote server as linked server instead of a remote server and I try to open our website and run a report (which executes a RPC) I receive the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
But, if I delete the linked server entry and set it up as a remote server the website works again. Then the job still does not run. It's a catch 22 situation.
November 13, 2002 at 1:51 pm
What versions of SQL Server are out local and remote machines?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 1:57 pm
Both servers are running SQL Server 2000.
November 13, 2002 at 4:37 pm
Just caught onto this
The error [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Is because the DTC service on the remote server cannot call back to the first server. This often is caused when the server2 is not on the same network.
Try conecting from server2 to server1, does this work.
Try adding a hosts entry on server2 for server1.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply