SQL Agent Security

[stonetown]

Backup Server:
Windows NT4 SP5, AS 2000 Advanced SP4

SQL Server:
Windows 2000 SP3, SQL 2000 SP2, SQL Agent installed under the sa account using named pipes, BAOF, Client agent, AS SP4.

Error Msg: Backup Agent Error (28) "Connection Failed"

The error msg occurs when attempting to connect to the SQL agent from the backup server using AS Manager and the BUILTIN\Administrators has deny access on the SQL server. Users with sysadmin rights have been created in SQL and we have tried to connect using these but with the same error msg. Even the sa account won't work with the BUILTIN\Administrators disabled! The only time that we can connect to the SQL agent is when the BUILTIN\Administrators is enabled. We need to disable the BUILTIN\Administrators for security reasons.

Has anyone managed to get around this problem??

PS The AS documentation is garbage and doesn't explain this enough.

 

[fritz4000]

Utilize an NT user with full administrative rights over SQL and ensure
that it is added to the SQL users. Also, ensure that the Backup Agent
RPC Server service utilizes this account.

I have not tried myself but heard this may work.

 

[databasebaba]

Looks like a agent problem...

Do the following,
Uninstall the agent

1) Download Sp3
2) Extract Sp3 using winzip
3) You will have a folder SQL.DBA
4) Run setup from the folder..
5) Apply Sp4 after the process
6) Use ODBC database sources in the control panel
7) In the system DSN tab choose ASNT and then configure it using the SA account. In the last window run TEST DATA source..

If its successful the you should be run the job.

DatabaseBaba