Login Failure for NT Authority\System user

[dhayanithy.m]

Hi Friends,

We have a production server(SQL 2005) which has configured Snapshot Replication to run once in a week.There is an open transaction which is initiated by "NT Authority\System user" after very few seconds the server started, which is trying to execute a SP called "sp_MSget_last_transaction" against the Distribution database.

I have tried giving the "db_owner" permissions to the NT Authority\System user,but it did not work.

Last eve,we have restarted the server,luckily the open transaction closed.
But to my surprise,the same transaction is started with a different SPID exactly as above mentioned.

The transaction is still open,and repeatedly(once in a minute) trying to execute the SP and a login failure occurs and the error written to the Error log.

Due to this the error log unnecessarily growing.

Any ideas../ Suggestions..?

Thanks in advance.

~Dhayanithy

[rmiao]

Find details of that spid from sys.sysprocesses first.

[dhayanithy.m]

Hi..

Thanks for the reply.

The SPID of that open transaction is 86

[dhayanithy.m]

Please refer the attachment for the SPID(86) details.

[rmiao]

It's log reader agent for replication, check replication settings on your server. Waht's sql service and sql agent service account? Who's job owner?

[dhayanithy.m]

Owner of the Replication jobs is "SA".

Replication setting seems to be normal.How may I know if there is anything abnormal in the settings?

I will post the service accounts details soon here.

Thanks again.

~Dhayanithy

[rmiao]

What's sql agent service account? Did you see login 'NT Authority\System' in sql? What kind of permission does it have?

[dhayanithy.m]

NT Authority\System

Def DB : Master

Roles : Public & Sys Admin

[rmiao]

What's sql agent service account? How did you set permission when config replication? Any error in sql agent error log?

[dhayanithy.m]

There is no errors in the SQL Agent log.

The replication is already configured in our server,by the employees who worked prior to me..

I wil post the sql agent service details soon.

Thanks for the reply

~Dhayanithy

[rmiao]

You still can find out replication security settings in properties page.

[dhayanithy.m]

1)The SQL Server service and SQL Agent service both are running in "Local System" account.

2)In replication : Agent set to run under SQL Agent service account. and to connect the publisher will use the ''SA'' account.

[rmiao]

Local system can't talk to remote server, possible to start sql agent with domain account?

[dhayanithy.m]

Let me check about this with my manager...

Thanks
~Dhayanithy