Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error running a stored procedure that references a remote server

I am running a stored procedure that has some references to a remote server through linked server. The stored procedure gets run OK when I run it through query analyzer. However, when I attempted to to call it using xp_Sendmail @query 'Exec StoreProc_name', I am getting this error: "ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER." I am running Window 2003/SQL Server 2003 on cluster congfiguration. Any help you can give me would be greatly appreciated. I looked at Microsoft's Web site, http://support.microsoft.com/default.aspx?scid=kb;en-us;811031&Product=sql2k. I performed their recommendation, but I am still getting the error.

The reason for the difference is that when XPs are run they are run under the context of the service accounts that are used for SQL Server. When you login to the server using query analyzer, your account probably has system admin or the needed rights on both servers to execute the stored procedure. When you run the XP, the rights that are being used are that of the service accounts for the SQL Server services. Check the rights of the service account and make sure it has appropriate rights on both the local server and the remote server. If use a domain account that has the necessary rights to run SQL Server you could use this same domain account for the SQL Server services on both of your servers.

For More Information

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close