PC Review


Reply
Thread Tools Rate Thread

EXEC sp w/ BULK INSERT - problem w/ security

 
 
NKTower
Guest
Posts: n/a
 
      26th Sep 2008
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.
 
Reply With Quote
 
 
 
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      27th Sep 2008
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
 
NKTower
Guest
Posts: n/a
 
      27th Sep 2008
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
non bulk emails going to bulk file JMarie Microsoft Outlook Discussion 1 3rd Jan 2008 05:58 PM
Opinions wanted: Bulk Insert vs Record Insert =?Utf-8?B?RGFsZSBGeWU=?= Microsoft Access Form Coding 2 8th Mar 2006 04:26 PM
Bulk Insert Problem =?Utf-8?B?U3RldmVI?= Microsoft VB .NET 1 25th Jan 2004 05:24 PM
Dynamic insert/update (ala EXEC SQL DESCRIBE) Tim Nelson Microsoft VB .NET 1 25th Sep 2003 11:44 AM
fastest way to insert data (no bulk insert, no dts) Hakan Eren Microsoft ADO .NET 3 18th Sep 2003 05:57 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:21 PM.