Problem Resolution

While using SQL Agent with SQL Server 2005, we have encountered some problems. These problems are fixable, but it took some research to get to the solutions. We include these problems here, with the hope that this information will save you some time.

Job Failure Error 3621

When running a job, the following error occurs and the job terminates immediately.

 The job failed.  Unable to determine if the owner (MARINER\wsnyder) of job myjob has server access (reason: Could not obtain information about Windows NT group/user 'MARINER\wsnyder', error code 0x5. [SQLSTATE 42000] (Error 15404)  The statement has been terminated. [SQLSTATE 01000] (Error 3621)). 

In this case, the SQL Server Agent Service Account was local (WSNYDER\sqlserver), not a domain account. I created a job that was running under a domain account (MARINER\wsnyder). When I tried to run this job, the error occurred. The SQL Agent Service Account did not have domain permissions to check the domain active directory to determine the permissions associated with the Mariner\wsnyder domain account.

Use a domain account with proper permissions as the SQL Server Agent Service Account.

Database Mail Message Failed

You are trying to use Database Mail to send to an operator. Notifications work with some operators, but not with others, using the same mail profile in Database Mail. You check the Database Mail error log and see the following message:

 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-07-08T10:46:20). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: You must SMTP authenticate before sending to [e-mail]).) 

Make sure the e-mail address is correct. If you try to send to an e-mail address that does not exist, you will also get an error.

Some ISPs block Email from Unknown SMTP Servers

Some Internet Service Providers (ISPs) block e-mail from unknown SMTP servers to prevent spam. To send to those e-mail addresses, you would have to create another account that uses the ISP's SMTP server and then add the new account to the existing profile.

One way to ensure this is the case is to send a test-message from the Database Mail item in Management Studio for a good e-mail address, and the e-mail is sent. Then send another test e-mail with the second e-mail address (associated with the ISP which may block SMTP messages), and the send fails. Yet you can send an e-mail to the address via your normal e-mail system. This is likely your problem.

SQL Agent Jobs Fail after Installing Service Pack 1

You have installed SP1, and now some of your SQL Agent jobs are failing. The ones that fail are called as the response to an alert. You are receiving the following error message:

 Unable to start execution of step 1 (reason: The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run.).  The step failed. 

Before SQL Server 2005 SP1, you could use any of the tokens by simply including the token in a string ($(A-DBN)). SQL Server 2000 also allowed some bracketed tokens ([Date]). Beginning with SP1, you must use one of the escape macros, or your tokenized jobs will fail.

In a knowledge-base article (#915845), SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1 (http://support.microsoft.com/kb/915845)

Microsoft provides a function that will update all jobs, all jobs for an owner, or a specific job, adding the ESCAPE_NONE macro wherever tokens are used. This will allow your jobs to run. However, you should analyze and replace the escapes with one of the SQL injection-resistant escapes.

Note

If you are using Master/Target Servers, you should run the function on the master and all targets.