MSDE SQL Agent Wont Start

Hi,
I have installed the MSDE and use mixed mode authenticaton.
The SQL Server Agent will not start due to login failures.
Normally, I would use the Enterprise Manager Managment wizard to
correct the System Account and Connection Credentials.

Can anyone tell me how to achieve this using OSQL?

Thanksthat'll be a long and very painful road...are you sure you want to go there?|||The other way to fix the SQLAgent account is through Windows Control Panel. You need "Services", one way to get to it is: Control Panel -> Administrative Tools -> Computer Management ... Services and Applications .. Services. There you can change the Properties of MSSQLAgent .|||Originally posted by ms_sql_dba
that'll be a long and very painful road...are you sure you want to go there?

We essentially have 2 systems in place. One system is a full blown installation of SQL Server 2000 (standard edition) on a server pc running windows 2000 server. This implementation includes the enterprise manager and query analyzer etc.

The second system (due to cost concern) uses MSDE with the same database and files. We do this so we can run our control system on a windows 2000 professional OS and hence keep the costs down.

I have no problem getting the SQLAgent running on a full system because I have the enterprise manager and its wizards to configure security and credentials for it. On the MSDE win2000 pro setup I do not have the tools and hence am forced to manipulate the configuration via OSQL - I will try using the control panel and services to configure it, but I am looking for a way to send the configuration via an OSQL (T-SQL script) that sets the services security to system account (Administrator) and the database login security to the SA account.

Thanks again|||Originally posted by ClayG
The other way to fix the SQLAgent account is through Windows Control Panel. You need "Services", one way to get to it is: Control Panel -> Administrative Tools -> Computer Management ... Services and Applications .. Services. There you can change the Properties of MSSQLAgent .

If I go to Administrative tools and services under SQLSERVERAGENT and properties, I can configure the security under the LOG ON tab to Local System Account. I have done this to no avail. I believe the problem exists with the fact that I use mixed mode authentication for SQL Server 2000. Using the Management Wizard under the Connection Tab, I need to specify the log in to the SQL database instance under the SA account with its respective password.
The service properties do not provide a means of specifying the credentials for the SQL Server Instance and database connection.

Regards and Thanks|||Enterprise manager should be able to talk to an MSDE instance. Is the MSDE instance not available to you, so you need to write a script?|||Originally posted by MCrowley
Enterprise manager should be able to talk to an MSDE instance. Is the MSDE instance not available to you, so you need to write a script?

Enterprise Manager is not installed- Since we are using Windows 2000 professional for this machine - which is stand alone and a completely different project. We chose to install MSDE with our standard database loaded in. I have no tools on the machine like enterprise manager to fix the issues. I need a means of deploying the security when no tools exist.

I did a search and found the following on a google group search - to which my response was as follows:

Good Afternoon,

I found your email through google_groups where you indicated the following to a user:
SQLServerAgent needs to be able to connect to SQL Server upon starting. See any errors in the sqlagent.out file. When you look in the Logins folder in Enterprise Manager, the BUILTIN\Administrators group must be granted access to the server. If the group has been removed or denied access, SQLServerAgent won't start
Sade Fashokun
SQL Server Support

My question if you don't mind answering it is as follows:

I am using MSDE on a windows 2000 professional OS installation with a default instance. SQL Server Agent will not start with the following error in the log:
Login Failed for User 'SCADA\Administrator' --

I am using mixed mode authentication and normally I would use the management wizard in Enterprise Manager under the Connection Tab to specify the SQL Agent Login
as the SA account and respective password. I do not want the Agent to Attempt a login through the windows Adminstrator account but instead through the SA or
other Database Security account.

Can you tell me how to change the Agent using OSQL etc. as I do not have the enterprise manager with MSDE -- to force it to use the SA account for the Sql Server Instance instead of the Window System Account the same as I would using the Management Wizard.|||You stated you have a full-blown installation of SQL Server (including tools). Just register your MSDE instance in the Enterprise Manager on that system. All you need is a network connection between the two boxes.|||Originally posted by MCrowley
You stated you have a full-blown installation of SQL Server (including tools). Just register your MSDE instance in the Enterprise Manager on that system. All you need is a network connection between the two boxes.

Okay, I understand that, I will try it out.
However, assume that I do not have such a system.
Is there a way to accomplish it without making such a connection
altering the login information for the agent via osql.

MSDE Installation Failures

We have a custom client/server application that runs against MSDE. This
application is distributed to the field through a Setup CD. We used the
Installshield to create the setup program. Most of the times when the user
tries to install the application it fails after MSDE install saying that
"Your SQL Server is either corrupt or has been tampered with.". We try to
install a named instance. I haven't been able to find a solution to this
persistent and annoying problem. Most of my users use xp sp2 os.
Sometimes i have noticed that there is already a default instance of MSDE on
their laptops that came with some other application or the laptop itself. If
I remove that installation and then re-run our installation process it goes
smoothly.
Any help is appreciated.
Thanks
Maan
hi Maan,
Maan Thakur wrote:
> We have a custom client/server application that runs against MSDE.
> This application is distributed to the field through a Setup CD. We
> used the Installshield to create the setup program. Most of the times
> when the user tries to install the application it fails after MSDE
> install saying that "Your SQL Server is either corrupt or has been
> tampered with.". We try to install a named instance. I haven't been
> able to find a solution to this persistent and annoying problem. Most
> of my users use xp sp2 os.
> Sometimes i have noticed that there is already a default instance of
> MSDE on their laptops that came with some other application or the
> laptop itself. If I remove that installation and then re-run our
> installation process it goes smoothly.
> Any help is appreciated.
> Thanks
> Maan
you usually get this kind of exception when machine name has been changed,
like reported in
http://www.karaszi.com/sqlserver/inf...erver_name.asp
do you perform some "strange" operation at MSDE install time?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Nothing "Strange". We actually use the installation package provided by
microsoft and ship it with our CD and it is called from inside of the
installation program.
"Andrea Montanari" wrote:

> hi Maan,
> Maan Thakur wrote:
> you usually get this kind of exception when machine name has been changed,
> like reported in
> http://www.karaszi.com/sqlserver/inf...erver_name.asp
> do you perform some "strange" operation at MSDE install time?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>