Re: Problem with bulk load security.
- From: Damien <Damien_The_Unbeliever@xxxxxxxxxxx>
- Date: Fri, 6 Feb 2009 00:41:36 -0800 (PST)
On Jan 28, 12:48 pm, RickB <rbiel...@xxxxxx> wrote:
I get a dozen or more files a day that must be loaded into our SQLThis sounds a little like the classic "double hop" issue.
Server 2005 db.
A generic SP loads the data like this:
set @cmd = "insert ...
From Openrowset (Bulk ''' + @fname + ''',
FORMATFILE =''' + @ffile + ''') as raw;"
exec (@cmd)
The file being imported is specified as '\\server\dir\src\file.txt'
The files physically reside on another server in the same domain.
SQL Server runs under a service ID that has access to the other
server.
The command to import the data comes from an script on the server
where the data resides. The ID that the script runs under has access
to the SQL Server and has bulk load rights.
I tried 3 scenerios. One works but I don't like it. Nothing else
does and I don't know why, or more importantly, what to do about it.
1) SQL Server authentication: Specifying an explicit SQL Server ID and
Password *WORKS*.
On the app server the security log shows SQL Server using it's
service ID to get in and read the data.
Obviously what I don't like about this is I must store a password
on the app server that has write access to the DB.
2) Windows authentication:
Cannot bulk load because the file "\\server\dir\src\file.txt"
could not be opened. Operating system error code 5(Access is denied.).
On the app server the security log shows SQL Server trying to get
in as 'Anonomous'
The question is, how do I get it to either use its service ID
like it does above or the ID I'm using to authenticate into SQL Server
(which also would work).
Let's give the two servers names.
ServerA is the server with the file, and is originating the command.
ServerB is the SQL Server with the stored proc.
ServerA is passing a set of credentials to ServerB when it's
connecting. An important fact about those credentials is that
(normally), ServerB can't then use those credentials to authenticate
itself with other servers (including ServerA).
What you need to do is set up trusted delegation. This would typically
be a domain administration task. If there are dedicated separate
admins at your place, you'll need to talk to them. If you're a sole
admin, a google search for "trusted for delegation" should start
finding the right articles.
3) I also tried setting the SP to 'execute as' the SQL Server ID used
in scenerio 1 above.
I get "You do not have permission to use the bulk load statement."
I get that error whether I use Windows authentication or the ID
that the SP should execute as.
I'm guessing in this case that SQL Server is bypassing some checks.
What almost certainly isn't happening is that the associated windows
user account credentials are being generated, and those are what would
be required to make the hop back to ServerA
Damien
.
- Follow-Ups:
- Re: Problem with bulk load security.
- From: rbielaws
- Re: Problem with bulk load security.
- Prev by Date: Re: Finding who has access to db_ltddtsuser
- Next by Date: Re: Finding who has access to db_ltddtsuser
- Previous by thread: Re: Problem with bulk load security.
- Next by thread: Re: Problem with bulk load security.
- Index(es):
Relevant Pages
|
