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 SP3a Upgrade problem

I just upgraded my desktop engine MSDE 1.0 to MSDE 2000 SP3a. Everything seemed to go well, MSSQLSERVER AND SQLSERVER Agent Services are running, problem is, I can no longer see the particular instance on the network from Enterprise Manager.

The installation instructions specified that if the Instance was to accept connections from applications on other computers not to specify the DISABLENETWORKPROTOCOLS parameter, so I didn't.

I'm a little green with this, can anyone suggest a place to start troubleshooting?Can you pull it up in Query Analyzer. Just type in (local) or whatever your instance name is for the connection server and see if it connects.

MSDE Replication issue

I am trying merge replication between two instances of MSDE on two
different machines on LAN.
I have setup the snapshot agent . when I try running the agent using
sp_start_job @.job_name='MergeSnapshot'
I get an error message saying job name ('MergeSnapshot') does not exist
I am new to replication and trying this for the first time...
Any help is appreciated.
Hello,
Please check out this article:
http://support.microsoft.com/default...b;en-us;324992
regards,
Henk Steunenberg
<prabhusangeeta@.yahoo.com> wrote in message
news:1133288220.042242.206490@.g44g2000cwa.googlegr oups.com...
>I am trying merge replication between two instances of MSDE on two
> different machines on LAN.
> I have setup the snapshot agent . when I try running the agent using
> sp_start_job @.job_name='MergeSnapshot'
> I get an error message saying job name ('MergeSnapshot') does not exist
> I am new to replication and trying this for the first time...
> Any help is appreciated.
>

MSDE Problem

I am running MSDE (SQL Agent) and I am having the following problem:

When I setup my connection string to my table, it says that the connection was succesful. I have a asp.net page that has a text box and a button (trying to learn how to add data to a SQL table), anyways, when I input data into the text box and hit the button for it to add the data to the table I get the following error:

Error:
Login failed for user 'Discovery3\ASPNET'
Exception Details: System.Data.SqlCLient.SqlException: Login failed for user 'Discovery3\ASPNET'.

The line that is highlighted is
myConnection.Open()

Here is the code that I am using:
<>
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnectionString As String

If myConnectionString = "" Then
myConnectionString = "server=Discovery3;database=dbDVD;trusted_Connection=true"
End If

Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')"
Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()
End Sub

I don't understand how the connection can be succesfull, yet it errors out at the myConnection.Open() code. My table contains one field TITLE with nothing special.

Any ideas?The ASPNet user must be a user/login on the database - -|||There are a couple of solutions.

1. Go the the SQL Server and grant the ASPNET user Windows Integrated access to the server and the database "dbDVD". More general info on ASP.NET with MSDE can be found onKB 872905.
2. Use SQL authentication instead (less secure): add a SQL account to the database server, grant that account (read/write) access to the database. Change the connection string by omitting the trusted_connection flag and add a uid and pwd flag containing the user name (SQL account) and the password. To do this, you'll have to tweak the MSDE installation to allow SQL auth (you can MSN for this to find more info). However, I don't recommend this second solution.

Make sure only to grant the required access to the database that is needed from the web app; in this case likely only read/write to the tables (lowest privileges access).|||Thanks, this worked...

I got passed the permission error, but now I am getting one for the tables.

Here is the error I get now:

INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'

how do I give read/write access?

Thanks|||As explained in the aforementioned KB article, you'll need to grant access for the ASPNET login to the database (in this case "master" - side tip: I recommend to put your own private data in another database than the master!).

osql -E -S COMPUTER_NAME\VSDOTNET -Q "sp_grantlogin 'COMPUTER_NAME\ASPNET'"
osql -E -S COMPUTER_NAME\VSDOTNET -d Pubs -Q "sp_grantdbaccess 'COMPUTER_NAME\ASPNET'"
osql -E -S COMPUTER_NAME\VSDOTNET -d Pubs -Q "sp_addrolemember 'db_owner' 'COMPUTER_NAME\ASPNET'"

In here, replace the COMPUTER_NAME with the name of your computer and VSDOTNET with the name of the MSDE instance. In the second command, replace Pubs with the tables where you want to grant the ASPNET account access to.|||Thanks, I got the first to perfect it was the 'db_owner' that corrected the issue.

I appreciate the help.