It is a problem of access permission on the file which is on the shared network drive. I think that the person who puts this file should at least give the read access to everybody. It is true that the security for files is treated differently in a workgroup and in a Windows domain. In a Windows domain. SSIS Error: Cannot bulk load. The file 'c:myfile.csv' does not exist. You start by adding a Bulk Insert task to your package and fiddle with it locally until you can get it to execute. Then you deploy the package to a testing environment that looks like production (separate SSIS and db servers).
My Dear Folks,If you are encountering any error while doing bulk load operation in sql server ,please read the error carefully,It's self explanatory.
Root Causes : 1) OS level
2) Sql Server level
3) Uses universal naming convention[UNC] name
Pre Checks :
Sqlserver Level :
a) Check you logged in user have right permission or not,You need sysadmin or bulkadmin privileges to perform this operation.
b) You can perform this operation with windows authentication or sqlserver authentication good time.if you are unable to do with sqlserver authentication use windows authentication bcz any way you are going to read os file :)
Os Level :
a) Make sure the file is not corrupted, Please test this file in you dev database if it is working fine,Copy file to target server with secure copy.
b) If u r successfully do this in our local database and not able to do in prod or in other environment please check the access to that file and fire wall to that server.
Check this for windows firewall settings. Ref: http://technet.microsoft.com/en-us/library/cc875811.aspx
If it is not posibel to disable firewalls, Copy the file to that specific server has access or on to that server.
UNC : The data file must be shared between the two computers .
To specify a shared data file,use its universal naming convention name, which takes the generation from of servernamesharenamepathfilename
Still you are encountering the same exception, Try as below :)
Sol 1 :
While there are various forms of bulk copy this blog specifically deals with copying data from a file into SQL Server. It deals about the specific error “Operating system error code 5(Access is denied.)” which might crop up under certain circumstances when doing a bulk copy. For a while now I worked with a lot of DBAs and Developers bewildered with the problem and most of them complaining about the lack of good documentation about it and hours spent diagnosing in the wrong direction. If you are looking for details about bulk copy visit http://msdn.microsoft.com/en-us/library/ms130809(SQL.90).aspx
You run the following query
BEGIN
BULKINSERT ENVPOT_R
FROM'advdev64BulkTesttest_bulk_insert.txt'
WITH(FIELDTERMINATOR=';',ROWTERMINATOR='n')
END
And end up getting the following error
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file
'advdev64BulkTesttest_bulk_insert.txt' could not be opened.
Operating system error code 5(Access is denied.).
The usual troubleshooting that DBAs do is to chase the “Access Denied” error from a file/folder access perspective. Some of them are as follows.
a)Added “full” access to “everyone” (just temporary to test this) on the BulkTest folder and still getting the same error.
b)Added “full” access to the SQL server service account on the BulkTest folder and still get the same error.
c)The user is made a member of thebulkadminfixed server role.
Many DBAs come with prior experience on SQL Server 2000 where the following was true.
Once a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of thebulkadminfixed server role, the user could import the file by using BULK INSERT and access the contents of the file.
But that had a security issue and the way SQL Server 2005 and later versions handle access to external files is different.
The following are the salient points you need to keep in consideration and are also mentioned in detail here http://msdn.microsoft.com/en-us/library/ms175915(SQL.90).aspx
a)The data file must be shared between the two computers
b)To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form of ServernameSharenamePathFilename.
c)The user account that is used by SQL Server must have been granted the permissions that are required for reading the file on the remote disk.
But there is another important consideration that needs to be taken care of when we have a setting as below and Windows authentication is being used.
Client application from client machine à SQL Server (SQL service account impersonating client account) File Server (impersonated client credentials)
a)We need to have CIFs SPN for the file server.
Whenever a computer is joined to a domain, it is assigned 2 SPN's by default: HOST/netbiosName, and HOST/FQDN.com. netbiosName being the machine name of the computer being joined to the domain, and FQDN.com being the fully qualified machine name. These two SPN's use the generic 'HOST' service type which includes all the various services that *come*be default with Windows. Therefore, if we connect tohttp://machineName or http://machineName.company.com, we will already have SPN's set that will handle Kerberos when using those names. When trying to connect to machineNameSomeShareNamewe would be all set for Kerberos(UNC's need a 'CIFS' SPN which is included under 'HOST' also).
Complete list of the different service types included in HOST of can be found in this technet article.
b)Configuring Kerberos delegation on the SQL Server box.
The requirements are as follows.
i)A domain user running the query from management studio must not have the Account is sensitive and cannot be delegated selected option.
ii) SPNs must be registered for the SQL Server service if the service account is a domain account.
iii)The client must be connecting to the SQL using TCP. http://support.microsoft.com/default.aspx?scid=kb;EN-US;811889
The service’s SPN must be registered by a domain administrator if the service account is a domain user account. If the service account uses the computer’s account, then the process can register by itself or the local administrator can register it by using Setspn.
At a command prompt, type:
setspn –L Account domainsqlServiceAccount
These two SPNs for SQL service account must come up for delegation to properly function:
•One for MSSQLSvc/Host:1433, where Host is the name of the host computer.
•One for MSSQLSvc/FQDN:1433 where FQDN is the fully qualified domain name of the computer running SQL Server .
The port number at the end may vary depending on the actual port the SQL Server is listening on. For the sake of brevity I have assumed the default port 1433.
If there are no MSSQLSvc SPNs listed or there is an SPN missing, then we need to add the appropriate SPN using the setspn –A command for delegation to work properly.
At a command prompt, type:
setspn -A MSSQLSvc/< Host >:<Port> <SQL_Service_Account>
setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>
A sample is as below
setspn -A MSSQLSvc/MySQLServer:1433 domainsqlServiceAccount
setspn -A MSSQLSvc/MySQLServer.MyDomain.com:1433 domainsqlServiceAccount
iv)The SQL Server service account must be trusted for delegation.
Once the delegation is set properly the bulk copy should work fine and we shouldn’t get any errors.
I am not delving deep into Kerberos troubleshooting as the same is well documented in another blog by my colleague here. The same can be referred if we still continue getting Kerberos related errors.
Sol 2 :
PROBLEM DECRIPTION
While executing a BULK INSERT command from a remote connection the following error is reported:
Msg 4861, Level 16, State 1, Line 6
Cannot bulk load because the file 'sqlboxsharemyText.txt' could not be opened. Operating system error code 5(Access is denied.).
Cannot bulk load because the file 'sqlboxsharemyText.txt' could not be opened. Operating system error code 5(Access is denied.).
Checklist to follow to resolve this issue
1. Add the login you are using to the Bulk Insert Administrators Server Role.
[Refer: http://msdn.microsoft.com/en-us/library/ms189934(SQL.90).aspx]
2. Make sure that you are using TCP/Kerberos (for delegation to work Kerberos must be used) - a possible workaround is to use SQL authentication instead:
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
3. Check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here.
After installation on your workstation run the following from command prompt:
C:Program FilesSupport Tools> setspn -L serviceaccount
There should be at least two SPNs listed, because the following two SPNs for the service account must be present for delegation to properly function:
ServiceClass/Host:Port, where ServiceClass is the appropriate service class, Host is the name of the host computer, and Port is the port the service is running on.
ServiceClass/FQDN, where FQDN is the fully qualified domain name of the host computer.
ServiceClass/Host:Port, where ServiceClass is the appropriate service class, Host is the name of the host computer, and Port is the port the service is running on.
ServiceClass/FQDN, where FQDN is the fully qualified domain name of the host computer.
4. If there is no SPN for the MSSQLSvc, create one. All we do is to use SETSPN to add the service by executing the following:
Setspn -A MSSQLSvc/Sqlbox.def.wxy.com:1433 DomainsqlSvrSvcAccount
Setspn -A MSSQLSvc/Sqlbox:1433 DomainsqlSvrSvcAccount
5. Make sure you don’t have duplicate SPNs. Refer http://support.microsoft.com/kb/321044.
6. Now that we created/verified that the SPN for the SQL Service, we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under. In the Delegation tab select 'Trust this user for delegation to any service (Kerberos only)
7. Also make sure machine which hosts SQL Server is trusted for delegation.
Interestingly, even after verifying everything the BULK INSERT query was failing with the same error. I later discovered that there is a DNS alias configured.
8. If you have configured DNS alias (or, CNAME record) Make sure that you have separate SPNs for DNS alias.
Note: You can check for DNS Alias or CNAME records using “nslookup” http://support.microsoft.com/kb/200525.
ROOT CAUSE AND FIX
If you have configured a DNS alias (or, CNAME record) ‘SQLBox.abc.xyz.com’ for SQLBox for which there is no corresponding SPN.
You need to add the following SPN(One corresponding to alias) to resolve 'Msg 4861, Level 16, State 1, Line 6' error:
Setspn -A MSSQLSvc/ SQLBox.abc.xyz.com:1433 domainsqlSvrSvcAccount
Sol 3:Environment
Here is a brief layout of the environment that I am going to use in this post as a reference.
This is a basic 3 machine architecture that is very common. Here are the various parties involved:-
- Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domainappadmin
- SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domainsqladmin
- File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domainappadmin has Full Control on the shared folder.
Scenario/Requirement
In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domainappadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.
1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.
2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.
BULKINSERTappdb.dbo.bulktbl
FROM‘morpheus1sharedata.txt’
If not configured correctly then you will get this error when running the above bulk insert command.
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file 'morpheus1sharedata.txt ' could not be opened. Operating system error code 5(Access is denied.).
Configuring Unconstrained Delegation
1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).
2. I am assuming that the SQL Server is running under the service account Domainsqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.
a. Make sure that the following is NOT checked.
b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.
We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.
Setspn -A MSSQLSvc/neosql thematrixsqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrixsqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrixsqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrixsqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrixsqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrixsqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrixsqladmin
Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.
c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.
d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.
e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.
This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.
3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer ConfigurationWindows SettingsSecurity SettingsLocal PoliciesUser Rights Assignment, and add the sql account Domainsqladmin to the following policies:
- Act as part of the operating system
- Impersonate a client after authentication
So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.
Testing
Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.
select b.spid, b.hostname, b.program_name, a.auth_scheme
fromsys.dm_exec_connections a
innerjoinsys.sysprocesses b
on a.session_id = b.spid
The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.
Configuring Constrained Delegation
So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….
1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domainsqladmin account and switch to the Delegation Tab.
2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.
In the dialog to select the sqladmin account (or your account which runs the SQL service).
This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.
Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.
Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.
After adding all of these, this is how your final configuration for the sqladmin account will look like.
We need the SQLSvc account (Domainsqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domainappadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITYANONYMOUS LOGON and this will not have access and fail with 0×5 (Access is Denied).
I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not
When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),
Log Name: Security
Source: Microsoft-Windows-Security-Auditing
Date: 12/20/2011 11:17:33 PM
Event ID: 4624
Task Category: Logon
Level: Information
Keywords: Audit Success
User: N/A
Computer: MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.
Source: Microsoft-Windows-Security-Auditing
Date: 12/20/2011 11:17:33 PM
Event ID: 4624
Task Category: Logon
Level: Information
Keywords: Audit Success
User: N/A
Computer: MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.
Subject:
Security ID: NULL SID
Account Name: –
Account Domain: –
Logon ID: 0×0
Security ID: NULL SID
Account Name: –
Account Domain: –
Logon ID: 0×0
Logon Type: 3
New Logon:
Security ID: ANONYMOUS LOGON
Account Name: ANONYMOUS LOGON
Account Domain: NT AUTHORITY
Security ID: ANONYMOUS LOGON
Account Name: ANONYMOUS LOGON
Account Domain: NT AUTHORITY
That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!
We have a user who get this error message every time bulk load using network location it throws error below.
'Cannot bulk load because the file could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).'
- If users logs on the remote server and runs same query it works fine
- FQDN is specified for the location of the file ( servernamesharenamefilename.txt)
- When file is copied on the server ( C: drive) , query runs fine via SSMS
- We are using SQL server 2008 R2
I have checked all NTFS permissions for the shared folder.I have gone through numerous posts suggesting solution but no luck.
Solomon Rutzky51.3k55 gold badges9090 silver badges195195 bronze badges
Jatin PatelJatin Patel
2 Answers
winerror.h lists Operating System Error Code 3 as
ERROR_PATH_NOT_FOUND
. This likely indicates that servernamesharenamefilename.txt
either doesn't exist or the SQL Server Service Account does not have access to the file via the UNC. Use SQL Server Configuration Manager to determine the name of the SQL Server Service Account:
Check the Fileshare Security for the UNC to ensure that account has access to the share. Check the Filesystem Security for the folder underlying the UNC to ensure the account has access to the file.
T-SQL
BULK INSERT
requires an understanding of the security architecture. Taken from that page:If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
Max VernonMax Vernon53.9k1313 gold badges117117 silver badges242242 bronze badges
Given that:
- The error occurs when using a file on the network
- The error does not occur when using a local file (local to the server running SQL Server)
- The error does not occur when using a file on the network IF the user logs onto the remote server
I would say:
- Your user is connecting with a Windows LoginIf the user was connecting with a SQL Server Login, then the behavior would be the same whether they logged into SQL Server from a remote server or from directly on the server via Remote Desktop. SQL Server Logins do not exist outside of SQL Server (i.e. in Windows / Active Directory) and so have no security context that could be impersonated. In that case, the
BULK INSERT
/OPENROWSET(BULK...
process will use the existing security context of the service account that is running the SQL Server process.When connecting with a Windows Login, there is a security context that can be impersonated and so the bulk operation attempts to do just that. The problem here is that forwarded security tokens cannot, by default, be re-forwarded. This is why the user gets the error when connecting from their desktop (they logged into their desktop to get the security token, and then logged into SQL Server remotely, using a forwarded security token). And it is why the user, when they log in directly to the remote server via Remote Desktop, they get a full security token from that server and connect directly to SQL Server with it, and hence can use it to forward to a networked service, such as a shared folder. - You either need to:
- open up the permissions on the share for
Everyone
orDomain User
or whatever - Go to Active Directory and configure that Windows Login (for that user) for Delegation
- (maybe) set up a SQL Server User that can be impersonated via the
EXECUTE AS
clause of aCREATE PROCEDURE
statement and create a Stored Procedure to do the bulk operation. I have not tried this so am not entirely sure if it would do the trick, but the idea is to switch the security context to that of a SQL Server account such that the bulk operation won't both with impersonation and will just use the current security context of the SQL Server service account. Worth a shot, perhaps.
51.3k55 gold badges9090 silver badges195195 bronze badges