Re: Problem with bulk load security.
- From: rbielaws@xxxxxxxxx
- Date: Wed, 4 Feb 2009 05:28:46 -0800 (PST)
On Feb 2, 4:57 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
(rbiel...@xxxxxxxxx) writes:
Thanks for pointing me to that 'trustworthy' setting.
Unfortunately it only changed the error I'm getting.
It fixed the error saying I don't have bulk load access but didn't
provide access. It just switched to the error saying the file can't
be opened. On the app server I see the SQL Server trying to get in as
anonymous -- similar to when I use Windows authentication.
So it seems that setting a function to 'execute as' some other user
(even with the trustworthy option on) doesn't give that function
entirely the same abilities as executing the function while actually
logged on as that same user.
It seems our only choice is to hard-code passwords to an SQL Server
account. Very dissappointing.
I think it's too early to give up. Try this:
EXECUTE AS LOGIN = '<service account>'
go
BULK INSERT ....
go
REVERT
If this works, go back read my previous post about using EXECUTE AS
LOGIN as a statement in the stored procedure and signing it with a
procedure.
It is quite obvious why it did not help to set the database to TRUSTWORTHY:
you are only impersonating a *database user*. To be able to do this,
you need to impersonate a *server login*. I don't know for sure whether
that is a sufficient condition, but it's definitely required.
--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
- Show quoted text -
I was trying to impersonate a user that worked.
But I tried what you suggested and as expected got a syntax error.
create PROCEDURE Import_File
( @param ...
) with execute as login = 'dom\svc_id'
as
begin ...
..Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure
Import_File, Line 10
Incorrect syntax near 'login'.
So I tried this too.
create PROCEDURE Import_File
( @param ...
) with execute as 'dom\svc_id'
as
begin ...
The create worked but execution got the same error as all the others.
Cannot bulk load because the file "\\sys\dir\file.txt" could not be
opened. Operating system error code 5(Access is denied.).
Like other times, the app system showed an anonomous login being
attempted.
From all this testing I'm more and more surprised that it actuallyworks when I hard-code a password and I'm guessing that it must be a
legacy feature from before integrated security was supported. It
certainly seems that what I want is impossible without either hard-
coding a password or being trusted in the domain (which is not likely
to happen since Oracle is the standard here).
.
- Follow-Ups:
- Re: Problem with bulk load security.
- From: Erland Sommarskog
- Re: Problem with bulk load security.
- References:
- Re: Problem with bulk load security.
- From: rbielaws
- Re: Problem with bulk load security.
- From: Erland Sommarskog
- Re: Problem with bulk load security.
- Prev by Date: Re: Where is the connection attempt coming from?
- Next by Date: Re: Where is the connection attempt coming from?
- Previous by thread: Re: Problem with bulk load security.
- Next by thread: Re: Problem with bulk load security.
- Index(es):
Relevant Pages
|
