| Author |
Topic |
|
dummat01
Starting Member
7 Posts |
 Posted - 2008-10-17 : 13:29:42
|
| Hey there, i've been going through this for several hours now and haven't found where the problem lies.We have a domain at work and on one of the servers in that domain, i have an SQL Server 2005/8 installed with latest service packs and patches.When i try this:UPDATE VFSFileVersions SET data = (SELECT * FROM OPENROWSET(BULK '\\cdgi00213\CentreAssistanceHTML\Share\Temp\file001.xls', SINGLE_BLOB) AS a) WHERE owner_key = 1 AND fileid = 27 AND version = 1I get the following error:Msg 4861, Level 16, State 1, Line 1Cannot bulk load because the file "\\cdgi00213\CentreAssistanceHTML\Share\Temp\file001.xls" could not be opened. Operating system error code 5(error not found).Setup:CDGIV0022 (Server + SQL Server)CDGI00213 (My machine)Both machine are in a domain and are working in perfect state.This is the same for all tests i may be doing so far. I tried :1. Giving the BULKADMIN permission to all domain users or sql server users2. I have set full control permission to all folders/files decending from the \\cdgi00213\CentreAssistanceHTML path.3. I have changed the user running SQL server to a domain user and set that domain user to local administrators of the server4. I have made certain the the feature BULK is active in the surface configuration5. I have made certain the connection features are set to TCP/IP and Named Pipes6. The file does exist at that location and if i logon to the cdgiv0022 server and try to run that file it works and is foundTest cases:1. File on SERVER can always be read should the path be UNC or Drive:2. File on MACHINE can never be read should the path be UNC or Drive:Anything you think of i could have forgotten? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-17 : 14:06:06
|
| The file has to be on the server. |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-17 : 14:08:58
|
| Or if you have the UNC set up for the network location where the file is at , then you should specify the drive letter.BULK 'd:\cdgi00213\CentreAssistanceHTML....' |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 14:17:09
|
| Why does it HAVE to be on the server, it doesn't make sense that i can use a UNC path to reach a file on the same server but not anywhere else on the network...PS, i just tried mounting a drive T:\ on \\CDGI00213\CentreAssistanceHTML and it still doesn't work. So it's got nothing to do with drive letter or UNC paths |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-10-17 : 14:22:37
|
| You can reach the file on any other server by setting it up as a UNC on the server machine.Try mounting your drive on the machine. So your T: drive on server would be directed to "\\cdgi00213\". You would then bulk copy it from "T:\CentreAssistanceHTML\Share\Temp\file001.xls" |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 14:28:58
|
| Thats what i just did... i mounted a drive T:\ directly on the Share called \\CDGI00213\CentreAssistanceHTML and it failed accessing it, same permission problem.Anywhere, this would have caused more problem later on because a mounted drive doesn't stay mounted at all time and even doubt that services have access to mounted drives as they are part of the user session but in this case it's an impersonation and there can be no session open on the server. We'd have to discard that method. |
|
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2008-10-17 : 15:08:13
|
| Are you Connecting using Windows Authentication or SQL in SQL Management Studio? |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 15:19:54
|
| Both, in my VB6 code i'm using an ADO 2.8 connection and i tried both in SQL auth mode or in Windows Auth Mode and in MSSMS2005 i'm using exclusively a Windows Auth Connection. |
|
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2008-10-17 : 15:28:32
|
| Did you run your script "Update ..." in MSSMS2005 and did you get the same error? |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 15:30:14
|
| Originally run in VB6 code but then i tried it in MSSMS2005 and both do the same error... |
|
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2008-10-17 : 15:35:42
|
| Have you tried bulk insert a file from your local machine to see if this works? |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 15:51:35
|
| You mean from my machine to the server (CDGIV0022)? or my machine to my local server?I'll give you an inside on what i am doing:I'm creating a virtual filesystem for my application and i store the directories and files in the database so i don't have security issues relative to files being kept on a harddrive somewhere. With this system it simplifies a lot the management of attachments in the different objects of my application, plus simplifies backups, security monitoring, access control and so on.My app must be able to import/attach files to objects. To do this, i have an API that calls the BULK insert. At best, i'd like my app to be able to BULK insert from the USERMACHINE to the SQL SERVER without a problem. So far, i understood that security settings will force me to actually copy the files the user wants to attach to a TEMP SHARED DRIVE. Sadly, i have not be able to BULK insert something from the TEMP drive on a domain server. The only way i am able to BULK insert is if the file is on the same machine as the SQL SERVER... |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-10-17 : 15:59:31
|
| It's probably this. It usually is.From Books Online "Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)", Security Considerations, paragraph 3."SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server 2005 handles security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C. For more information, see Impersonation Overview."http://msdn.microsoft.com/en-us/library/ms175915.aspxI've never seriously tried to work out what one needs to do to which of the 3 machines in question to get such a delegation of credentials established. |
|
|
|
dummat01
Starting Member
7 Posts |
Posted - 2008-10-17 : 16:10:30
|
| Already been checked...This pertains only to 3 party operations where SQL SERVER A tries to write to SQL SERVER B where a file is available on SERVER C.My problem is not a delegation problem since SQL Server runs as a domain account that has full priviledges on all machines. The user i'm using to connect to SQL Server (My domain account) has full credentials on all machines involved in the process... |
|
|
|
|
Bulk insert madness