Post reply

How is SQL server accessing the file system?

  • November 23, 2009 at 4:18 am

    #228163

    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

    #1085172

    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.

    SNM

    Try Free Microsoft SQL Server Data Collector & Performance Monitor.

    http://www.analyticsperformance.com[/url]

    @nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply