EXEC sp w/ BULK INSERT - problem w/ security

Discussion in 'Microsoft Access ADP SQL Server' started by NKTower, Sep 26, 2008.

  1. NKTower

    NKTower Guest

    Synopsis: I'm trying to launch an SP that makes use of BULK INSERT and
    getting permissions errors.

    I have a stored proc - dbo.p_Import_Sales_Transactions which takes as its
    single argument the path to where the data resides, having been created by
    another application. (There are 4 tab-delimited files there, ready to load.)
    The data has been placed on a drive local to the server. The stored proc
    truncates a set of intermediate tables, then does a set of four BULK INSERT
    statements. When run from SQL Server Management Studio, it runs fine.

    My need is to launch the proc from an Access application running on a set of
    client mahcines, with a pass-through query containing:

    EXEC dbo.p_Import_Sales_Transactions 'D:\stage\'

    where D:\stage\ is the path to the share from the server's viewpoint.

    The Access application is linked to the database via an ODBC connection,
    with SQL Server account authentication. I've no control over this.

    When I try to execute the stored proc from Access, I get

    You do not have permission to use the bulk load statement (#3834)

    I've Google'd MS and various other places but the few things that I found
    were incomprehensible to me. I'm a developer, not a DBA, and the client has
    no resident SQL Server DBA - Oracle yes, MS no.

    I have tried creating a SQL Server account 'bulk_inserter' and have found
    a 'bulkadmin' role that I have associated with that user. 'bulk_inserter'
    has dbo as default schema. I then added WITH EXECUTE AS 'bulk_inserter' to
    the preamble of the stored proc, which it accepts when I execute the ALTER
    but it doesn't like at run-time.

    So I'm down to either of two errors:

    'bulk_inserter' oesn't have permission in current security context.
    or
    You do not have permission to use the bulk load statement (#3834)

    I feel that I'm close, but not quite there.

    Surely someone else is doing this - in testing via Management Studio
    interface, BULK INSERT does in 4 minutes what DoCmd.TransferText takes 40+
    minutes to do.
     
    NKTower, Sep 26, 2008
    #1
    1. Advertisements

  2. If you have a security problem with the BULK Insert command, your best
    chance of getting an appropriate answer would be to ask in a newsgroup
    dedicated to SQL-Server such as microsoft.public.sqlserver.security or
    m.p.sqlserver.server or m.p.sqlserver.programming.

    Also, this newsgroup is about ADP and SQL-Server and has nothing to do with
    MDB or ACCDB database file and ODBC Linked tables or Passthrough queries.

    In your case, if the account that you are using for connecting with
    SQL-Server Management Studio works then you should use the same for
    executing your passthrough query.

    A second point to consider is that drive letters for shared networking (such
    as D:\) are strongly associated with the login account and the fact that D:\
    has been defined for your login account and/or the account used to run the
    sql-server service doesn't mean that it will still be available under the
    security context that will run your SP. You should never use a driver
    letter for a shared network and always the full network name.

    As an example; the following code with create an anonymous query with the
    login account of your choice and with a full network share name for the
    file:

    Dim strSQL As String
    Dim Db As DAO.Database
    Dim Rst As DAO.Recordset
    Dim Qdf As DAO.QueryDef
    Dim strConnect As String

    strConnect = "ODBC;Driver={SQL Server};Server=YourServer;" & _
    "Database=YourDB;uid=you;pwd=secret;"

    strSQL = "BULK INSERT CSVTest FROM '\\server\share\path\csvtest.txt' " & _
    "WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) "

    Set Qdf = CurrentDb.CreateQueryDef("")
    With Qdf
    .Connect = strConnect
    .ReturnsRecords = False
    .ODBCTimeout = 0
    .SQL = strSQL
    .Execute dbSQLPassThrough + dbSeeChanges
    .Close
    End With
    Set Qdf = Nothing

    In this case, I've used Driver={SQL Server} as the ODBC driver but you
    should take the most recent ODBC for SQL-Server that you can find on your
    system, for example the SQL-Server Native Driver if you have installed
    SQL-Server 2005. For a list of drivers, see:

    http://www.connectionstrings.com/?carrier=sqlserver
    http://www.connectionstrings.com/?carrier=sqlserver2005

    Another way of solving the security problem would be to set up a linked
    server to your CVS file. This way, you can set up the account to open the
    file directly on the SQL-Server.

    Finally, even 4 minutes can still be a long time to load the file if you
    have indexes on this table.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: sylvain aei ca (fill the blanks, no spam please)


    "NKTower" <> wrote in message
    news:...
    > Synopsis: I'm trying to launch an SP that makes use of BULK INSERT and
    > getting permissions errors.
    >
    > I have a stored proc - dbo.p_Import_Sales_Transactions which takes as its
    > single argument the path to where the data resides, having been created by
    > another application. (There are 4 tab-delimited files there, ready to
    > load.)
    > The data has been placed on a drive local to the server. The stored proc
    > truncates a set of intermediate tables, then does a set of four BULK
    > INSERT
    > statements. When run from SQL Server Management Studio, it runs fine.
    >
    > My need is to launch the proc from an Access application running on a set
    > of
    > client mahcines, with a pass-through query containing:
    >
    > EXEC dbo.p_Import_Sales_Transactions 'D:\stage\'
    >
    > where D:\stage\ is the path to the share from the server's viewpoint.
    >
    > The Access application is linked to the database via an ODBC connection,
    > with SQL Server account authentication. I've no control over this.
    >
    > When I try to execute the stored proc from Access, I get
    >
    > You do not have permission to use the bulk load statement (#3834)
    >
    > I've Google'd MS and various other places but the few things that I found
    > were incomprehensible to me. I'm a developer, not a DBA, and the client
    > has
    > no resident SQL Server DBA - Oracle yes, MS no.
    >
    > I have tried creating a SQL Server account 'bulk_inserter' and have found
    > a 'bulkadmin' role that I have associated with that user. 'bulk_inserter'
    > has dbo as default schema. I then added WITH EXECUTE AS 'bulk_inserter'
    > to
    > the preamble of the stored proc, which it accepts when I execute the ALTER
    > but it doesn't like at run-time.
    >
    > So I'm down to either of two errors:
    >
    > 'bulk_inserter' oesn't have permission in current security context.
    > or
    > You do not have permission to use the bulk load statement (#3834)
    >
    > I feel that I'm close, but not quite there.
    >
    > Surely someone else is doing this - in testing via Management Studio
    > interface, BULK INSERT does in 4 minutes what DoCmd.TransferText takes 40+
    > minutes to do.
     
    Sylvain Lafontaine, Sep 27, 2008
    #2
    1. Advertisements

  3. NKTower

    NKTower Guest

    Thank you for the detailed response. Yes, you caught me, it's not an ADP
    project. :) I've recorded those links for the other forums and will make
    use of them for non-ADP things in the future.

    Late last night I found a simple way to do it, I'll record it here so that
    if someone should stumble on it via search they have it. This is all
    implemented via SQL Server Management Studio ---
    a) right-click on the SERVER and select PROPERTIES
    b) Click on PERMISSIONS in the left frame
    c) Select the specific user - in my case a SQL Server login
    d) In the lower Permissions frame, place a check in the GRANT box opposite
    "Administer bulk operations"

    That's it. That particular login now may make use of BULK INSERT. The
    login is restricted at other locations within SQL Server as to being only
    able to access the one particular database.

    As for the share - the SP was originally developed for a situation where
    there was no share - the development machine hosted both SQL Server Express
    and Access. I just hadn't gotten around to changing it. Only when we moved
    to the production environment did we find that if the SQL Server is running
    under a system account that it doesn't have cross-platform access. Rather
    than set up a non-system account and all that implies, we decided to let the
    staging area be on the server and control access via domain authentication.

    As for the performance - the data is collected detailed point-of-sales
    information from 980 stores. It has been pre-procesed into tab-delimited
    files that are normalized to match the tables in the database.
    Unfortunately, some stores upload their data multiple times which means we
    see a repeat of sales from earlier in the day. They do this as they want to
    be able to generate reports with a day's partial feed. So the intermediate
    tables have as the most significant part of their key the store number,
    register number, date/time, transaction number and the 'ignore_dup_key=ON'
    attribute. For 8.4 million lines/250MB coming in (spread across the 4 files)
    we are quite happy with 4 minutes to do the end-of-day cycle.

    Thanks again.

    "Sylvain Lafontaine" wrote:

    > If you have a security problem with the BULK Insert command, your best
    > chance of getting an appropriate answer would be to ask in a newsgroup
    > dedicated to SQL-Server such as microsoft.public.sqlserver.security or
    > m.p.sqlserver.server or m.p.sqlserver.programming.
    >
    > Also, this newsgroup is about ADP and SQL-Server and has nothing to do with
    > MDB or ACCDB database file and ODBC Linked tables or Passthrough queries.
    >
    > In your case, if the account that you are using for connecting with
    > SQL-Server Management Studio works then you should use the same for
    > executing your passthrough query.
    >
    > A second point to consider is that drive letters for shared networking (such
    > as D:\) are strongly associated with the login account and the fact that D:\
    > has been defined for your login account and/or the account used to run the
    > sql-server service doesn't mean that it will still be available under the
    > security context that will run your SP. You should never use a driver
    > letter for a shared network and always the full network name.
    >
    > As an example; the following code with create an anonymous query with the
    > login account of your choice and with a full network share name for the
    > file:
    >
    > Dim strSQL As String
    > Dim Db As DAO.Database
    > Dim Rst As DAO.Recordset
    > Dim Qdf As DAO.QueryDef
    > Dim strConnect As String
    >
    > strConnect = "ODBC;Driver={SQL Server};Server=YourServer;" & _
    > "Database=YourDB;uid=you;pwd=secret;"
    >
    > strSQL = "BULK INSERT CSVTest FROM '\\server\share\path\csvtest.txt' " & _
    > "WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) "
    >
    > Set Qdf = CurrentDb.CreateQueryDef("")
    > With Qdf
    > .Connect = strConnect
    > .ReturnsRecords = False
    > .ODBCTimeout = 0
    > .SQL = strSQL
    > .Execute dbSQLPassThrough + dbSeeChanges
    > .Close
    > End With
    > Set Qdf = Nothing
    >
    > In this case, I've used Driver={SQL Server} as the ODBC driver but you
    > should take the most recent ODBC for SQL-Server that you can find on your
    > system, for example the SQL-Server Native Driver if you have installed
    > SQL-Server 2005. For a list of drivers, see:
    >
    > http://www.connectionstrings.com/?carrier=sqlserver
    > http://www.connectionstrings.com/?carrier=sqlserver2005
    >
    > Another way of solving the security problem would be to set up a linked
    > server to your CVS file. This way, you can set up the account to open the
    > file directly on the SQL-Server.
    >
    > Finally, even 4 minutes can still be a long time to load the file if you
    > have indexes on this table.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    >
    > "NKTower" <> wrote in message
    > news:...
    > > Synopsis: I'm trying to launch an SP that makes use of BULK INSERT and
    > > getting permissions errors.
    > >
    > > I have a stored proc - dbo.p_Import_Sales_Transactions which takes as its
    > > single argument the path to where the data resides, having been created by
    > > another application. (There are 4 tab-delimited files there, ready to
    > > load.)
    > > The data has been placed on a drive local to the server. The stored proc
    > > truncates a set of intermediate tables, then does a set of four BULK
    > > INSERT
    > > statements. When run from SQL Server Management Studio, it runs fine.
    > >
    > > My need is to launch the proc from an Access application running on a set
    > > of
    > > client mahcines, with a pass-through query containing:
    > >
    > > EXEC dbo.p_Import_Sales_Transactions 'D:\stage\'
    > >
    > > where D:\stage\ is the path to the share from the server's viewpoint.
    > >
    > > The Access application is linked to the database via an ODBC connection,
    > > with SQL Server account authentication. I've no control over this.
    > >
    > > When I try to execute the stored proc from Access, I get
    > >
    > > You do not have permission to use the bulk load statement (#3834)
    > >
    > > I've Google'd MS and various other places but the few things that I found
    > > were incomprehensible to me. I'm a developer, not a DBA, and the client
    > > has
    > > no resident SQL Server DBA - Oracle yes, MS no.
    > >
    > > I have tried creating a SQL Server account 'bulk_inserter' and have found
    > > a 'bulkadmin' role that I have associated with that user. 'bulk_inserter'
    > > has dbo as default schema. I then added WITH EXECUTE AS 'bulk_inserter'
    > > to
    > > the preamble of the stored proc, which it accepts when I execute the ALTER
    > > but it doesn't like at run-time.
    > >
    > > So I'm down to either of two errors:
    > >
    > > 'bulk_inserter' oesn't have permission in current security context.
    > > or
    > > You do not have permission to use the bulk load statement (#3834)
    > >
    > > I feel that I'm close, but not quite there.
    > >
    > > Surely someone else is doing this - in testing via Management Studio
    > > interface, BULK INSERT does in 4 minutes what DoCmd.TransferText takes 40+
    > > minutes to do.

    >
    >
    >
     
    NKTower, Sep 27, 2008
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeff Davies

    Exec in Sp and returning recordset

    Jeff Davies, Feb 21, 2005, in forum: Microsoft Access ADP SQL Server
    Replies:
    4
    Views:
    289
    llat.com
    Apr 7, 2005
  2. ADP: Error calling SP with "EXEC sp_helprotect" statement

    , Feb 24, 2006, in forum: Microsoft Access ADP SQL Server
    Replies:
    18
    Views:
    978
    Karen Yarber
    Apr 29, 2006
  3. Guest

    syntax for exec sp w/ varchar parameter as cbo rowsource?

    Guest, Nov 20, 2007, in forum: Microsoft Access ADP SQL Server
    Replies:
    7
    Views:
    405
    Guest
    Nov 20, 2007
  4. Sivamoorthi.S

    Bulk insert

    Sivamoorthi.S, Jan 7, 2008, in forum: Microsoft Access ADP SQL Server
    Replies:
    1
    Views:
    252
    Sylvain Lafontaine
    Jan 7, 2008
  5. Demelash Gobeze

    BULK INSERT Creates 2 rows from 1 row datafile

    Demelash Gobeze, May 15, 2008, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    276
    Demelash Gobeze
    May 15, 2008
Loading...

Share This Page