December 14, 2005 at 12:14 am
Hi all,
When i am executing this command, am getting an error
exec master..xp_cmdshell 'BCP Admin.dbo.tbl_abc out "c:\Temp\test.bcp" /Sserv1 /T /n /b1 /m1'
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
NULL
(3 row(s) affected)
The same command when i execute using SQL Server authentication , it is successfull
Any suggestions ?
subban
December 14, 2005 at 7:29 am
Check your password is expired or not. If password expired, you will get the reported error. If not, check the following links.
http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=32&messageid=10251
December 14, 2005 at 3:58 pm
Hi,
It is probably because you are trying to run BCP not from the command prompt but from SQL Server. For example if you logged to SQL Server with SQL authentication from another computer and trying to use Integrated Authentication (Trusted, -T) I am not sure what identity will be used, the one SQL server is running under or proxy or whatever. The message says it can not generate the context, I guess it is trying to get your security context. There are actually 2 sets of credentials when using xp_cmdshell witn BCP: one is running BCP and other one specifies what credentials BCP should use to connect to SQL Server. -T switch just tells BCP to use the same Windows credentials to connect to SQL server as used to run BCP. But who is running BCP? Also check for domain trust.
Regards,Yelena Varsha
December 15, 2005 at 6:10 am
I am not getting an error whn i execute the same command without server name...
exec master..xp_cmdshell 'BCP Admin.dbo.tbl_abc out "c:\Temp\test.bcp" /T /n /b1 /m1'
Expers !!! any suggestion on this ??
subban
December 16, 2005 at 1:17 pm
BOL says:
If no server is specified, bcp connects to the default instance of SQL Server on the local computer. This option is required when executing bcp from a remote computer on the network.
since you use sp_cmdshell I am not sure what is "local" in this case: local to you, local to sp_cmdshell or what. Where are you running the client? on the server itself or from another computer? tell us about the architecture.
Regards,Yelena Varsha
December 19, 2005 at 8:49 pm
I am executing the bcp from QA on the server itself. The server is having only one instance which is local
subban
December 19, 2005 at 8:50 pm
I am getting the same error when i open QA at the server where instance is running
subban
December 20, 2005 at 9:41 am
Hi,
Why do you do it from a QA if you are on the server? Can you do BCP from the command prompt? Are you getting any messages when you run from the command prompt?
The issue may be explained as follows: when you don't specify the server name the server assumed to be local and no trip to DNS server for the name resolution is required. If you specify the server name then a trip to DNS server is required for the name resolution unless you specify an entry in the client network utility for the IP address of your machine (This is my assumption). If you are using QA with SQL Standard login then your identity on the network is probably the one of SQL Server startup account, which is probably Local System. When the trip to the DNS server is required the Local System account from your server can not authenticate with the domain controller.
If you try to run BCP from the command prompt it may be your Windows account who will be a security context and if it is a domain account it is possible you will not have issues.
Yelena
Regards,Yelena Varsha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply