Bulk insert - sql agent job - ANONYMOUS LOGON

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Bulk insert - sql agent job - ANONYMOUS LOGON

Author

Topic

anxcomp
Starting Member

41 Posts

Posted - 2008-07-23 : 16:22:22

Hello,

I have problem with start bulk insert from job. Sql server and sql agent service works under Domain\SqlSrv account.
When start bulk from job, I receive error

"Executed as user: Domain\SqlSrv. Cannot bulk load because the file
"\\srv\share" could not be opened.
Operating system error code 5(error not found). [SQLSTATE 42000] (Error
4861). The step failed."

I gave full access to Everyone (share + ntfs), I found where is problem but
don't know how resolve it..

SQL Agent instead connect to share \\srv\share as Domain\SqlSrv account,
connect as
NT AUTHORITY\ANONYMOUS LOGON, I don't understand this behaviour?

Could you help, please.

SQL 2005 STD, 2 node cluster.

--
Regards,
anxcomp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 16:54:36

Is your TCP/IP enabled? ALso Domain account should have access.

anxcomp
Starting Member

41 Posts

Posted - 2008-07-23 : 19:01:13

Hello,

Thank you for answer.

What you mean TCP/IP enabled? If you mean SQL server - yes.

User Domain\SqlSrv has access do share and ntfs (everyone has) but in audit logs on server \\srv I see failed connection from SQL with login NT AUTHORITY\ANONYMOUS LOGON, why this account ? (sql should connect to share using sql agent account)

--
Regards,
anxcomp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 19:05:50

Go to SQL server config manager or SQL surface area config and see if it is disabled.

anxcomp
Starting Member

41 Posts

Posted - 2008-07-23 : 19:22:21

It's enabled, I can't disable tcp/ip becouse I connect to that server from remote location, aplication etc.

--
Regards,
anxcomp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 19:50:11

I said not to disable but just check whether it was disabled.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 19:51:31

Which account do you see in SQL config manager for SQL server agent?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-23 : 23:36:54

First of all, it's nothing to do with sql server protocol. And yes, double check sql agent service account. By the way, did you ever try bulk load in query window?

anxcomp
Starting Member

41 Posts

Posted - 2008-07-24 : 08:04:59

As I said SQL Agent works on Domain\SqlSrv (SqlSrv@Domain.local). Yes, when I login as Domain\SqlSrv to Windows Sever and connect to SQL using Windows Authentication and start the same bulk insert from SSMS window work perfectly.

From SQL Agent job still get the same error..strange, question is why sql force connection to the network resource as NT AUTHORITY\ANONYMOUS LOGON instead as Domain\SqlSrv?

--
Regards,
anxcomp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-24 : 08:50:05

You didn't answer my question.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-24 : 13:59:27

>> why sql force connection to the network resource as NT AUTHORITY\ANONYMOUS LOGON instead as Domain\SqlSrv?

Who is job owner then? Is it sysadmin?

anxcomp
Starting Member

41 Posts

Posted - 2008-07-29 : 08:29:08

@sodeep
In SQL Server Configuration Manager:

Name: SQL Server Agent
Log On As: SqlSrv@Domain.local

@rmiao
sa

Any idea, please?

--
Regards,
anxcomp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-29 : 23:13:24

Can you load data from that share in query window?

anxcomp
Starting Member

41 Posts

Posted - 2008-07-30 : 13:41:16

Yes, from SQL Server Management Studio no problem, works great (under Domain\SqlSrv account)

--
Regards,
anxcomp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-30 : 22:57:56

Can you change sql agent service account to Domain\SqlSrv from SqlSrv@Domain.local?

anxcomp
Starting Member

41 Posts

Posted - 2008-08-11 : 16:00:11

Yes, I've changed from SqlSrv@Domain.local to Domain\SqlSrv, restart agent, but nothing, the same error... agent again connects to share as NT AUTHORITY\ANONYMOUS

Additional information, account on which Agent works is member of domain admin group.

I don't understand, really don't understand.... I would be grateful if you somebody could help me.

--
Regards,
anxcomp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-11 : 22:56:28

Did you apply sql2k5 sp2 yet?

anxcomp
Starting Member

41 Posts

Posted - 2008-08-12 : 18:09:04

Yes, SQL 2005 Std SP2

--
Regards,
anxcomp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-12 : 23:04:17

It's stragne if things are as you said. One more option to try, create proxy account for sql agent.