EXEC sp w/ BULK INSERT - problem w/ security

[NKTower]

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]

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.

 

[NKTower]

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.