Error 7410 when doing a SELECT from a Link table in xp_sendmail

Really would appreciate some advice on this problem. I have a cluster, CORPUSGA and a stand alone server CORPUSAPP22. CORPUSAPP22 has a linked server set up to CORPUSGA using 'sa' as the security context. Within a stored procedure I am running a SELECT to CORPUSGA.msdb.dbo.sysjobs.

When this is run from Query Analyzer it is fine and runs under the 'dbo' security context, but when is is run under xp_sendmail that uses the SQL Agent's security context (a domain account called
IntServiceGA) it fails with this message:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

For the following command:

exec master.dbo.xp_sendmail
@.recipients = 'stelzner_eve@.emc.com;dabas_ravi@.emc.com;bergin_gi llian@.emc.com;lynch_eoin@.e
mc.com;sherman_ nancy@.emc.com',
@.message = @.email,
@.query = @.query_text,
@.no_header = 'true',
@.width=80,
@.subject = 'Job Failed on CORPUSGA ',
@.attach_results = 'true',
@.set_user = 'dbo'

Where @.query_text is something like "select * from CORPUSGA.msdb.dbo.sysjobs"

The IntServiceGA account is identical on both servers and is in the local administrators group on both servers and is in the system administrators role within SQL Server, has access to the database and sysjobs has granted permissions to IntServiceGA (SELECT, INSERT,UPDATE,DELETE).

When I run profiler against this, it tries to execute under IntServiceGA (an Windows authentication account) and then tries to SETUSER to sa . This is what it objects to the switching. I have tried to set up the IntServiceGA as the security context for the linked server too and this does not work, same error.

Any help would be great,
Thanks,
EveWhy do you need @.set_user if SQLAgent service account is already in sysadmin server role?|||I have another stored procedure that uses this and it works with the
@.set_user on this one. However, whether I leave it in or take out the
@.set_user parameter it still fails. Any ideas?

Thanks,
Eve|||Try to run your xp_sendmail after doing SETUSER 'DOMAIN_NAME\IntServiceGA' and see what you get.|||no go, using the SETUSER prior to the xp_sendmail still netted the
same results? Do you know what generates this message- NT or SQL?|||It's NT. It just proved that your SQLAgent account is lacking some rights. Was mail profile setup using SQLAgent service account?|||Yes, the profile was set up on both servers from the
corp\IntServiceGA account that is a local admin on
the both servers. Are there additional policies that need
to be put on for this account that are not in the default?

Error 444 - Permission denied.

OK, I'm at a complete loss. Any advice on the following would be greatly
appreciated. What I've got is multiple reports that are heavy process
intensive(multiple datasets, multiple joins, etc). Reports usually take
between 25 and 35 minutes. All was fine until yesterday. Now the reports seem
to run forever. Then finally after an hour we get a 444 error and a
permission denied dialog box. These reports have been running for months
without issue. We've even gone so far as to switch to a new server and still
get the same errors.
Any suggestions?Can you launch SQL Profiler and run a trace and see what connection
credentials are being presented to the dataserver and what the
queries/sprocs are? After capturing the trace you should be able to open
the trace and replay it. My guess is that it is not a SQL issue but this
will help confirm that.
I tried to do some looking up on what error 444 means, is that from MS SQL,
or DB2? or from IIS? Also can you open the Add/Remove Programs, check the
'show updates' box and look for updates installed around the same time as
when the problem started occuring. I've been keeping our machines up to
date and haven't had issue, so not much help here, sorry.
Steve MunLeeuw
"dbutler" <dbutler@.discussions.microsoft.com> wrote in message
news:52C330E6-AEBE-4B59-91C6-99F4E8FD5574@.microsoft.com...
> OK, I'm at a complete loss. Any advice on the following would be greatly
> appreciated. What I've got is multiple reports that are heavy process
> intensive(multiple datasets, multiple joins, etc). Reports usually take
> between 25 and 35 minutes. All was fine until yesterday. Now the reports
> seem
> to run forever. Then finally after an hour we get a 444 error and a
> permission denied dialog box. These reports have been running for months
> without issue. We've even gone so far as to switch to a new server and
> still
> get the same errors.
> Any suggestions?|||I'd also use the RS 2005 Configuration tool to check what accounts are used,
and if they have had a password change or expire. Maybe it's a private key
backup issue...grasping at straws.
Steve MunLeeuw
"dbutler" <dbutler@.discussions.microsoft.com> wrote in message
news:52C330E6-AEBE-4B59-91C6-99F4E8FD5574@.microsoft.com...
> OK, I'm at a complete loss. Any advice on the following would be greatly
> appreciated. What I've got is multiple reports that are heavy process
> intensive(multiple datasets, multiple joins, etc). Reports usually take
> between 25 and 35 minutes. All was fine until yesterday. Now the reports
> seem
> to run forever. Then finally after an hour we get a 444 error and a
> permission denied dialog box. These reports have been running for months
> without issue. We've even gone so far as to switch to a new server and
> still
> get the same errors.
> Any suggestions?|||Thanks Steve. It ended up being exactly that. We switched to a new server,
but our network guy swapped one of the harddrives so it was still 'linked' to
the old server. Once we updated a few keys and played around with RS, we got
it back to working.
"Steve MunLeeuw" wrote:
> I'd also use the RS 2005 Configuration tool to check what accounts are used,
> and if they have had a password change or expire. Maybe it's a private key
> backup issue...grasping at straws.
> Steve MunLeeuw
> "dbutler" <dbutler@.discussions.microsoft.com> wrote in message
> news:52C330E6-AEBE-4B59-91C6-99F4E8FD5574@.microsoft.com...
> > OK, I'm at a complete loss. Any advice on the following would be greatly
> > appreciated. What I've got is multiple reports that are heavy process
> > intensive(multiple datasets, multiple joins, etc). Reports usually take
> > between 25 and 35 minutes. All was fine until yesterday. Now the reports
> > seem
> > to run forever. Then finally after an hour we get a 444 error and a
> > permission denied dialog box. These reports have been running for months
> > without issue. We've even gone so far as to switch to a new server and
> > still
> > get the same errors.
> >
> > Any suggestions?
>
>

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<

connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA"providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance NameOK!Database, Username and passwordOK!SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe NamesOK!SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared)OK!SQL Server Browse running and activeOK!

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Nevermind, I reinstalled again .NET 2.0 and it works. I can't believe that I expend two days thinking it was a configuration setting

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak