BCP Out Failure About Login Failure

Question

BCP Out Failure About Login Failure

rjoseph

Ten Centuries

Points: 1087
More actions
December 13, 2013 at 8:30 am

#285952

I am having this issue about a command line bcp out. I use that same command line on another SQL 2008 server and it is working fine. I even force it to use a SQL authentication user. Here is the command line
EXEC MASTER.dbo.xp_cmdshell 'bcp Compsee_Asset_Tracker.dbo.CompseeEmployeeUpdate out \\MIANOTES2\attach\CompseeEmployeeUpdate.txt -c -T -t"|"'
I got this error message below
SQLState = 37000, NativeError = 4060
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "Compsee_Asset_Tracker" requested by the login. The login failed.
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'MIADOM\MIAISOASVC'.
Please help. I don't know what else to do.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Check the user permission on the on the server which you are getting this error message. Also check if the user is not orphan.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Lowell SSC Guru Points: 323518 More actions · Answer 2

my first guess is permissions; xp_cmdshell doesn't typically use the network permissions you expect.

run this command, and it can help you see if the user SQL uses for disk access returns is a domain user or not, and whether that account has access to the share

DECLARE @Results table(

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

insert into @Results (TheOutput)

exec master..xp_cmdshell 'whoami' --nt authority\system for example

insert into @Results (TheOutput)

exec master..xp_cmdshell 'cd %userprofile%' --If this is NULL it because account(nt authority\system?) is not a user...command fails.

--can this user actually see the share?

exec master..xp_cmdshell '\\MIANOTES2\attach\'

select * from @Results

my results:

ID TheOutput

1 nt service\mssqlserver

2 NULL

3 NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rjoseph Ten Centuries Points: 1087 More actions · Answer 3

Hi Lowell,

Once again, thanks for your wonderful help. I have asked the developer to re-write the code by declaring variables and this suggestion fixes the problem. You were and are a big help.

Have a wonderful holiday seasons.

Regards,

Reynold