Q:
Environment: SQLEXPRESS with start account of Network Service, SQLCMD tool.
When run following BCP utility with XP_cmdshell environment, it will report login failure:
--1, turn on 'xp_cmdshell'
use master
go
exec sp_configure 'show advanced options','1'
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
-- 2, use AdventureWorksDW
go
CREATE TABLE [dbo].[talltypes](
[rec] [smallint] NOT NULL,
[cbinary] [binary](255) NULL,
[cvbinary] [varbinary](255) NULL,
[cchar] [char](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cvchar] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdatetime] [datetime] NULL,
[csmalldatetime] [smalldatetime] NULL,
[cdecimal17] [decimal](38, 8) NULL,
[cdecimal172] [decimal](38, 38) NULL,
[cnumeric5] [numeric](8, 2) NULL,
[cnumeric10] [numeric](20, 4) NULL,
[cfloat8] [float] NULL,
[creal] [real] NULL,
[cint] [int] NULL,
[csmallint] [smallint] NULL,
[ctinyint] [tinyint] NULL,
[cmoney] [money] NULL,
[csmallmoney] [smallmoney] NULL,
[ctext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cimage] [image] NULL,
[cbit] [bit] NOT NULL,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
exec master..xp_cmdshell 'bcp AdventureWorksDW..talltypes in E:\MYAUTO~1\INSTAL~1\MSSQL1~1.SQL\MSSQL\Data\talltypes.bcp -n -T -E -SSQLPOD065-15\SQLEXPRESS'
go
A:
When you run BCP from SQL Env, using XP_CMDShell , its run in the context of Startup Account, which was Network Service and have very limited prvilage . When you run from OS ENV, its run in the context of windows user which i assumed that a member of Administrator Group. If the server was configured mixed mode you could have passed -Usa -Ppassword instead of -T. -T is for trusted connection
Refer:
Service Account (SQL Server Express): http://msdn2.microsoft.com/en-us/library/ms143170.aspx
Security Considerations for a SQL Server Installation: http://msdn.microsoft.com/en-us/library/ms144228.aspx
