Cannot bulk load because the file could not be opened. Operating system error code 1326(Logon failure: unknown user name or bad password.)

bulk upload from csv test file

"\servername\wwwroot\Upload\LDSAgentsMap.txt"

     SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_CSVTest_BulkInsert] ( @Path NVARCHAR(128) )
AS 
    DECLARE @Sql NVARCHAR(2开发者_如何转开发56)
    SET @Sql = 'BULK
INSERT CSVTest 
FROM ''' + @Path + ''' WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'
--PRINT @Sql
EXEC(@Sql)

GO

path is "\servername\wwwroot\Upload\LDSAgentsMap.txt"

note

this is in shared hosting and database user have blukadmin and public service role

This can occur when the Windows user account that SQL runs under (e.g. SqlServerAccount) doesn't have permissions to access the shared file (\servername\wwwroot\Upload\LDSAgentsMap.txt).

One way we've worked through this is to go to the machine where shared file resides. On that machine, add a Windows user account with the same user name and password as the account that SQL runs under.

For example,

If your database resides on MyDatabaseServer, and runs under a Windows user account SqlServerAccount with password Foo, then go to the machine where your shared file resides, e.g. MyFileServer, and create a Windows user account named SqlServerAccount with password Foo.

Last step: go into the folder share on MyFileServer and go into sharing properties for that folder. (On Win7, right-click the folder->Properties->Sharing->Advanced Sharing). Then add read permission for SqlServerAccount that you created.

I was getting this error from SSIS when trying to execute a stored procedure which performed a bulk insert.

I fixed it by adding the SQL Server port number to the connection string in SSIS, forcing SSIS to access SQL Server through TCP/IP instead of Named Pipes.