SQLServer Agent NOT starting after upgr frm SQL Server 7.0 to SQL Server 2K

SQLServer Agent NOT starting after upgr frm SQL Server 7.0 to SQL Server 2K

Post by rcs » Mon, 21 Oct 2002 04:59:32



hi,
SQL Server 7.0 was running fine and was accessible by all
my applications. After upgrade to SQL Server 2000, the
SQLServer Agent is refusing to start. Infact, it starts
and stops IMMEDIATELY as is reflected in the Win2K
Services (Start from Services and refresh)

SQLAgent.OUT contains the following entries :
1. [298]SQL Server Error: 1311, Cannot generate SSPI
Context [SQLSTATE HY000]
2. [000] Unable to connect to SQL Server '[local]'; Cannot
start SQLServer Agent

The current setup is
x86 2 CPU
1GB RAM
NT4.0 upgraded to Win2K (No SP set up yet .... could this
be the reason ???)
SQL Server 7.0 EE upgraded to SQL Server 2000 EE witt SP1.
Connection to DB thro' Windows authentication.
Query Analyzer works fine ..... not checked out other apps.

Any ideas ???

 
 
 

SQLServer Agent NOT starting after upgr frm SQL Server 7.0 to SQL Server 2K

Post by Wayne Snyde » Wed, 23 Oct 2002 06:10:52


One reason might be that the SQL Agent is running on a login that does NOT
have SQL admin priviledge.

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org


Quote:> hi,
> SQL Server 7.0 was running fine and was accessible by all
> my applications. After upgrade to SQL Server 2000, the
> SQLServer Agent is refusing to start. Infact, it starts
> and stops IMMEDIATELY as is reflected in the Win2K
> Services (Start from Services and refresh)

> SQLAgent.OUT contains the following entries :
> 1. [298]SQL Server Error: 1311, Cannot generate SSPI
> Context [SQLSTATE HY000]
> 2. [000] Unable to connect to SQL Server '[local]'; Cannot
> start SQLServer Agent

> The current setup is
> x86 2 CPU
> 1GB RAM
> NT4.0 upgraded to Win2K (No SP set up yet .... could this
> be the reason ???)
> SQL Server 7.0 EE upgraded to SQL Server 2000 EE witt SP1.
> Connection to DB thro' Windows authentication.
> Query Analyzer works fine ..... not checked out other apps.

> Any ideas ???


 
 
 

SQLServer Agent NOT starting after upgr frm SQL Server 7.0 to SQL Server 2K

Post by David Row » Wed, 23 Oct 2002 02:48:17


Also,

I assume your server is in a domain. You should
consider giving the service user the "log on as
a service" rights in your domain group policy.
Believe it or not, it is in TWO different places
in active directory users and computers:

open ADU&C
Right click your top domain and choose properties
click on the group policy tab.
edit the top level default group policy
under "computer configuration" drill down to
"windows settings", then "Security Settings" then
"Local Policies". Click on "User rights assignments"
and on the right side, look for "log on as a service"
Double click that and add the account that will run
your SQLServer Agent and your MSSQLServer services.
Without this, your life will be miserable. Although
it already is because you may need to ensure that
it is in the 2nd place as well...

Same instructions as before but this time in ADU&C,
right click the 'Domain Controllers' OU and click
properties. Follow the instructions above and add
your service account to the proper place. I have
no idea why these policies are not the same, but
that is another topic...

Good luck,

David

Quote:>-----Original Message-----
>hi,
>SQL Server 7.0 was running fine and was accessible by all
>my applications. After upgrade to SQL Server 2000, the
>SQLServer Agent is refusing to start. Infact, it starts
>and stops IMMEDIATELY as is reflected in the Win2K
>Services (Start from Services and refresh)

>SQLAgent.OUT contains the following entries :
>1. [298]SQL Server Error: 1311, Cannot generate SSPI
>Context [SQLSTATE HY000]
>2. [000] Unable to connect to SQL Server '[local]';
Cannot
>start SQLServer Agent

>The current setup is
>x86 2 CPU
>1GB RAM
>NT4.0 upgraded to Win2K (No SP set up yet .... could this
>be the reason ???)
>SQL Server 7.0 EE upgraded to SQL Server 2000 EE witt SP1.
>Connection to DB thro' Windows authentication.
>Query Analyzer works fine ..... not checked out other
apps.

>Any ideas ???

>.