August 14, 2008 at 7:36 am
We use a transactional publication with a push subscription to replicate data. We're trying to optimize the transmission of a large snapshot (~20GB) over a small link (< T1). We've identified the -UseInProcLoader as a more efficient means to push the initial snapshot (as it uses BULK INSERT instead of the BCP utility). However when we utilize the "-UseInProcLoader" switch in the Job Step Properties for the Distribution Agent, we receive the following error:
[font="Arial Black"]Cannot bulk load because the file "F:\ReplData\unc\FolderName\DateTimeCode\Table.bcp"
could not be opened. Operating system error code 5(Access is denied.).
(Source: MSSQLServer, Error number: 4861)
Get help: http://help/4861 [/font]
We have a separate Publisher and Distributor (all local on the same VLAN) and the Subscriber is remote (we replicate across the Internet over a VPN).
The Distribution Agent is running under the local SQL Agent account on the Distributor. When I log on to the Distributor with the SQL Agent local account, I can access that path and file. It's a local drive (Direct Attached Storage) on the Distributor.
Does this error mean that the remote subscriber needs access to that path or does the Distributor read the BCP file directly to perform the BULK INSERTs?
August 15, 2008 at 10:48 am
It is the "distributor" who does not have access to the File.
* Noel
August 15, 2008 at 11:00 am
noeld (8/15/2008)
It is the "distributor" who does not have access to the File.
Thanks Noel.
That's exactly what I thought. The odd thing though is that the BCP files are in a local folder path on the Distributor, and the SQL Agent Service Account (which the Distribution Agent is running as) has access to that path.
Am I missing a setting in the Surface Area Configuration or something?
August 15, 2008 at 1:58 pm
Matt Slocum (8/15/2008)
noeld (8/15/2008)
It is the "distributor" who does not have access to the File.Thanks Noel.
That's exactly what I thought. The odd thing though is that the BCP files are in a local folder path on the Distributor, and the SQL Agent Service Account (which the Distribution Agent is running as) has access to that path.
Am I missing a setting in the Surface Area Configuration or something?
the BCP files are created by the "SNAPSHOT" agent that runs on From that same machine. Please verify that the "DISTRIBUTION" agent is actually using the *same* account that the snapshot does. I have been bitten by this before!!
* Noel
August 18, 2008 at 6:02 am
noeld (8/15/2008)
The BCP files are created by the "SNAPSHOT" agent that runs on From that same machine. Please verify that the "DISTRIBUTION" agent is actually using the *same* account that the snapshot does. I have been bitten by this before!!
Hey Noel,
Yeah, both the Snapshot Agent and the Distribution Agent are running under the SQL Agent Service Account, and when I log on as that user (it's a local account) I can access the file.
I'm running SQL 2005 SP2 & CU8 (I also got the same error before I applied CU8).
OK, at some point I must have resolved the access denied error, but I'm still getting this error message (genericized for security):
Cannot bulk load because the file "F:\ReplData\unc\FolderName\20080818074219\TableName_775.bcp" could not be opened. Operating system error code 3(The system cannot find the path specified.). (Source: MSSQLServer, Error number: 4861)
Get help: http://help/4861%5B/b%5D
BTW, the subscriber is running SQL 2005 RTM (we're working on them to get SP2 & CU8 installed). Would that make a difference?
August 18, 2008 at 1:46 pm
Matt Slocum (8/18/2008)
noeld (8/15/2008)
The BCP files are created by the "SNAPSHOT" agent that runs on From that same machine. Please verify that the "DISTRIBUTION" agent is actually using the *same* account that the snapshot does. I have been bitten by this before!!Hey Noel,
Yeah, both the Snapshot Agent and the Distribution Agent are running under the SQL Agent Service Account, and when I log on as that user (it's a local account) I can access the file.
I'm running SQL 2005 SP2 & CU8 (I also got the same error before I applied CU8).
OK, at some point I must have resolved the access denied error, but I'm still getting this error message (genericized for security):
Cannot bulk load because the file "F:\ReplData\unc\FolderName\20080818074219\TableName_775.bcp" could not be opened. Operating system error code 3(The system cannot find the path specified.). (Source: MSSQLServer, Error number: 4861)
Get help: http://help/4861%5B/b%5D
BTW, the subscriber is running SQL 2005 RTM (we're working on them to get SP2 & CU8 installed). Would that make a difference?
Ok Iam not entirely sure here but I believe Distrib uses a "Share" to access the file.
I can suggest you do these things:
1. Ensure you have TCP/IP protocol enabled
2. Create a "share" that points to that location give the account access to that share.
* Noel
October 17, 2008 at 6:30 am
FYI, I found this in the SQL Server 2005 BOL (note bolded statement):
-UseInprocLoader
Improves the performance of the initial snapshot by causing the Distribution Agent to use the BULK INSERT command when applying snapshot files to the Subscriber. This parameter is deprecated because it is not compatible with the XML data type. If you are not replicating XML data, this parameter can be used. This parameter cannot be used with character mode snapshots or non-SQL Server Subscribers. If you use this parameter, the SQL Server service account at the Subscriber must have read permissions on the directory where the snapshot .bcp data files are located. When this parameter is not used, the agent (for non-SQL Server Subscribers) or the ODBC driver loaded by the agent (for SQL Server Subscribers) reads from the files, so the security context of the SQL Server service account is not used.
So in my specific case, I cannot use this option because the Distributor and Subscriber and on disparate networks (connected through the public Internet) and because of security/auditing purposes, we're not going to open file access from the Subscriber back to our Distributor.
October 17, 2008 at 11:31 am
NICE catch!
* Noel
October 17, 2008 at 1:00 pm
It's funny how you find the answer to something when you stop looking for it. I was looking into another replication issue when I found it.
Thanks for all your help on this Noel. I really appreciate it!
Matt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply