Problem: Insert a network file in the DB using BULK Insert
Works for a local sqlcmd connection. Does not work for a remote sqlcmd connection.
SQL Server is running on Machine 'WalRepTest'.
User account for SQL Server Service: 'Domain\vivek_uppal'.
This has admin privilieges on SQL Server machine and the machine hosting the file.
I have 2 scenarios. Scenario 1 behaves as expected, Scenario 2 causes error.
Scenario 1.
Connect using sqlcmd from local machine
Machine: WalRepTest
Windows user: Domain\vivek_uppal
Sql login using the cmd: sqlcmd -S walreptest
File: \\wal1w330\demo\LogoportFilestorage.txt
SQL Statement:
UPDATE FILE_VERSION
SET DATA =
(SELECT * FROM OPENROWSET (BULK '\\wal1w330\demo\LogoportFilestorage.txt', SINGLE_BLOB) AS Document)
WHERE ROW_ID=6
Everything is OK.
Scenario 2.
Connect using sqlcmd from remote machine
Machine: wal1w330
Windows user: Domain\vivek_uppal
Sql login using the cmd: sqlcmd -E -S walreptest
File: \\wal1w330\demo\LogoportFilestorage.txt
Execute the above mentioned SQL statement:
I get an error:
Cannot bulk load because the file "\\wal1w330\demo\LogoportFilestorage.txt" could not be opened. Operating system error code 5(Access is denied.).
Analysis:
In the both scenarios the file to import resides on a different machine than the one running the SQL Server. The only difference is where I am connecting from. In one case I am on the same machine as SQL Server, in the second I am on a different machine.
It seems like the user who is trying to access the file does not have sufficient privileges.
In scenario 1, if I look at the event log on the machine where the file resides, all access requests are made using the user 'Domain\vivek_uppal'
For scenario 2 the access requests are made using NT Authority\Anonymous user.
As I understand it, since I am using integrated windows authentication, the access requests should be made using 'Domain\vivek_uppal' as that is the Windows user I am logged in as.
Apologies for the long post. I am trying to provide all the relevant information.
Feedback will be appreciated.
Thanks,
Vivek
The BOL entry for your bulkload scenario: 'security consideratons for using transact-sql to bulk import data'
Please let me know how it goes. Unfortunately I can't test it locally as our domain does not allow delegation setup.|||Thanks for the reply Sunil.
Adding the links to the 2 sections you have mentioned.
Setting up SQL Server for delegation
http://msdn2.microsoft.com/en-us/library/ms248588
Security considerations for using Transact-SQL to bulk import data
http://msdn2.microsoft.com/en-us/library/ms246113
I will check the things specified with my domain admin and update.|||
Vivek,
I have the same situation like yours. Did you resolve this issue? Please reply me back at the earliest.
Thanks.
|||I did the steps mentioned in the links in my last post. It did not resolve the issue.
I had some other pressing issue, so moved on for now. Will have to get back to it, at some point of time.
|||Sunil: I am experiencing the same problem on my SQL 2005 / Windows 2003 upgraded server as was detailed in the original post of this thread.
My batch job is doing a remote Bulk Insert worked fine under SQL 2000 / Windows 2000. Now, however, after the upgrade, it is failing.
And, yes, my SQL Server Service accounts all have rights to the remote file (referenced using a UNC path) but I still am getting the "access denied" error.
Help! I have Kerberos enabled and it is working. Is there some new security policy or permission in Windows 2003 that is stopping this from working?
Note: If I copy the file locally to the SQL Server and change my code to reference the local file; no problem. It is only when it tried to access the remote file via the BULK INSERT statement that I get the access denied.
|||I am replying to my own note since I was able to resolve this issue and wanted to share it with others just in case they were having issues too.
In my case, the code we were running was shelling out to the OS and using ISQL to call the BULK INSERT. After thinkning about it for a while, I changed the ISQL statement to an OSQL statement and my correct credentials got passed tot he remote server! This resolved my problem.
Apparently, ISQL was trying to log on to the remote server using anonymous access. I was able to see this in the event log. When I changed the code to use OSQL, credentials were passed and authenticated.
|||Has anybody resolved this issue?
I am using OS 2003 with SQL 2005 and am using SQL server Management Studio to Bulk Insert. The SQL service is running under a network login that has Admin privliges on the network, as does my login.
I can browse to this file with both logins.
My script..
bulk insert dbo.OST_DataComm_Log_Data_Load
from '\\myserver\r-drive\DataComm\Logs\Site2Site.Log'
with
(
datafiletype = 'char',
rowterminator = '\n',
TABLOCK
)
and get the message:
Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\\myserver\r-drive\DataComm\Logs\Site2Site.Log" could not be opened. Operating system error code 5(Access is denied.).
HELP!
|||Did anyone ever get this issue resolved? I'm having the same problems in SQL 2005.|||Could you please check the BOL topics mentioned in one of the posts above? The security considerations topic for bulk insert documents the changes made in SQL Server 2005 and it should give you an idea of how to resolve the problem.|||Thanks!!! I never would have thought to look this up in books online.
Unfortunately, as with others who have responded to this thread, I have tried everything mentioned in books online and it did not resolve the problem.
|||Ok. So can you describe your configuration:
1. What is the service account?
2. Where is the file located?
3. What are the ACLS on the file or share or directory for the service account?
4. Who is running the BULK INSERT? Is it a user who is authenticated using SQL login or Windows Login?
5. If user is authenticated using Windows login then can be access the network resource? So check the ACLS on the file or share or directory for the user's Windows login since the SQL Server service will impersonate him/her.
6. If user is authenticated using SQL login then the service account credentials will be used,
7. Is the BULK INSERT being executed as part of SP with an impersonation context specified in SQL Server 2005? This complicates matters slightly so you will have to read more about external access permissions in BOL (start with EXTERNAL_ACCESS database option).
8. Lastly, if the request is distributed one (making hop between multiple SQL Servers) then you will have to check your security delegation settings. If you are using Windows authentication and the credentials are not being delegated properly then you will get errors.
|||I too am experiencing an Access is Denied error when trying to bulk insert a file from a network path. As with previous posters, if I login to the server (Computer1) and execute the bulk insert command (using a query in Sql Server Management Studio):
BULK INSERT Tmp_Peptide_Import FROM '\\Computer2\LTQ\InputFile.txt'
the data successfully loads into the table. However, if I'm using my desktop machine (Computer2, running Windows XP SP2) connect to the server (Computer1, which is running Sql Server 2005 SP1 on Windows 2003 Server) and try to bulk insert the data from the third computer (Computer3), the bulk insert fails (using SSMS or Query Analyzer). I do not encounter this problem with Sql Server 2000 so it is definitely a result of tightening the security.
I've read the BOL entries that discuss delegation and authentication and I don't see what to change on the server. I'm not bulk inserting data from a remote Sql Server instance -- I'm bulk inserting data from a text file that happens to reside on a network share. If I copy the file to the server (Computer1), then I can bulk insert the data when connected from my desktop computer.
The reason this is particularly troubling is that I wanted to single step debug through the T-Sql in a stored procedure. I have Visual Studio 2005 installed on my desktop, but don't have it on the server (and don't want to install it on the server). Thus, unless I can figure out how to fix the access issues, my only choice is to update the SP to copy the file locally, or shell out to the OS and call OSQL (as suggested by Paul Olivieri on 27 May 2006).
Trying to answer Umachandar's questions:
1. --> Sql Server service (on Computer1) is running under a network account with read access to the file (UserA). If Sql Server Agent runs a SP with the Bulk Insert command (running as UserA) then the insert from Computer3 works fine.
2. --> File is located on a Network computer (Computer3)
3. ACLS for the service account are Read/Write access to the file on Computer3
4. I'm connecting to the server (Computer1) from my desktop (Computer2) using a network account (UserB), connected via Windows Login and TCP/IP (confirmed with cliconfg.exe).
5. UserB has read access to the file on Computer3 (and is Sql Server admin on Computer1)
6. -> n/a
7. --> The BULK INSERT is being executed in a SP, but not with any impersonation context that I know of; it's simply a T-Sql stored procedure. Also, simply executing a Bulk Insert command in Query Analyzer fails, so we can't blame the security context of the SP
8. --> Nope, not a distributed query
Matt
In response to your questions:
1. The service account is network windows login.
2. The file is located on a separate server than the database server.
3. The ACLS for the file/share is set to full control-Everyone.
4. The Bulk Insert command is being executed by a user logged in to Management studio using windows authentication.
5. The user that is attempting the Bulk Insert command is an administrator of the network resource (file server)
6. The service account also has administrator rights on the file server (just in case)
7. The Bulk Insert command is being used (& failing) from both a Query Window and from a stored procedure with no impersonation context specified.
8. The request is making hops between multiple machines. From a workstation running Management Studio to a Database Server running SQL 2005 to a file server running Server 2003. Going directly form the Database Server to the File Server works with no problems. My network admins tell me that security delegation is set up correctly. If you know of anything specifically for them to check it would be helpful.
Thanks.
|||You need to check your security delegation setting for your setup to work. The security delegation should be setup for the service user account/port combination and registered. So it is still a configuration issue and you should contact your network admin to verify settings again. See the KB articles for Windows and search for SETSPN utility for more details.
No comments:
Post a Comment