November 23, 2009 at 4:18 am
Hi All,
I am doing a test where I am extracting a table on one server and loading it on another server.
Following are the configuations:
Server1 : Windows server 2008 Enterprise edition running SQL server 2008 enterprise edition
There are 2 Instances of SQL server on Server1 : Server1\Instance1 and Server1\Instance2 both the services are running "Local System"
Server 2 : Windows server 2003 Enterprise edition running SQL server 2005 developer edition.
There is only one (default) instance on Server2. Service is running "Local System".
There is a shared folder (ExtractFiles) on Server1 on which the "Server1$" account and "Server2$" has got Full control. And my active directory account has got full access
There is a SQL server account called bcptest who is bulkadmin on Server2 and Server1\Instance2. This user is also the db_owner of DB1 on Server2 and Server1\Instance2
Scenarios:
1. bcp out the file from DB1 on Server1\Instance1 to a shared folder (ExtractFiles) on Server1 using my AD credentials - works file.
2. BULK INSERT the above extracted file to DB1 on Server2 using a SQL server account called bcptest - works fine
3. BULK INSERT the above extracted file to DB1 on Server1\Instance2 using a SQL server account called bcptest - fails with error "Access is denied". Here is the command used and the error:
BULK INSERT [DB1].dbo.[Table1]
FROM '\\Server1\ExtractFiles\Table1.DAT'
WITH ( FORMATFILE = '\\Server1\ExtractFiles\FormatFiles\Table1.fmt')
Cannot bulk load because the file "\\Server1\ExtractFiles\Table1.DAT" could not be opened. Operating system error code 5(Access is denied.).
I think in scenario 2 it is working because I have given full access to Server2$ account on the shared folder on Server1.
But my question here is that What credentials are used by the SQL server Server1\instance2 to access the shared folder when the service is running under Local System?
As mentioned above, Server1$ account has got full control on the shared server.
-Vikas Bindra
November 29, 2009 at 2:42 pm
You can use linked server to copy one table from one server to another server and setting the security in better way and more simple to transfer the info from one server to another server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply