Re: Problem with bulk load security.
- From: Damien <Damien_The_Unbeliever@xxxxxxxxxxx>
- Date: Wed, 11 Feb 2009 01:02:26 -0800 (PST)
On Feb 10, 2:40 pm, rbiel...@xxxxxxxxx wrote:
On Feb 6, 2:41 am, Damien <Damien_The_Unbelie...@xxxxxxxxxxx> wrote:
This sounds a little like the classic "double hop" issue.
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- Hide quoted text -
- Show quoted text -
Lots of people have called my question 'the double hop problem'
so I suppose that must be the correct term for it. Knowing it's
name doesn't really help me get around hard-coding a password
that has update access to the production server though.
The fact that an ID and password that doesn't even exist on
ServerA can be used to access files on ServerA while one that
actually does exist can't is just outrageous.
It's just a fact of the way Windows security works. Getting annoyed or
upset about the issue will not help you to resolve it. Basically, when
you connect to something across the network and use windows
credentials to authenticate yourself, what is passed across the
network is a ticket that says "here, ServerB, I'll guarantee that this
person is LoginA". The ticket that is passed across is specifically
targetted at ServerB. If ServerB could then use that ticket and pass
it onto any other server it felt like, that would be a huge security
issue.
OTOH, If ServerB is trusted, at the domain level, for delegation, then
the ticket it is passed is in a slightly different form, and says,
essentially "here, ServerB, I'll guarantee that this person is LoginA,
and if you ask the domain controllers nicely, I'll issue you tickets
to pass on to other servers"
If you use SQL Server authentication, on the third hand, then when
ServerB is reaching across the network back to ServerA, it's got to
use some windows credentials of some kind (since that's how network
shares work), and so it will be using the account under which the
service is running on ServerB. Which presumably does have permissions
to the share on ServerA.
The business about getting the domain administrator involved
is the same as saying "switch to Oracle". They will simply
ask 'why aren't you using unix and Oracle'. Frankly I'm
starting to wonder that very thing. Until recently I've
always worked on Tandem/NSK so maybe that's why I was naive
enough to take the job. I've never heard anyone speek highly
of any Microsoft product unless they never worked on anything
else and I always wondered why....
The business about getting the domain administrator involved is... how
you resolve this problem if you want to use windows authentication.
Damien
.
- References:
- Re: Problem with bulk load security.
- From: Damien
- Re: Problem with bulk load security.
- From: rbielaws
- Re: Problem with bulk load security.
- Prev by Date: Re: Error message when trying to attach Northwind?
- Next by Date: Re: Replication over WAN Setup - Need Inputs
- Previous by thread: Re: Problem with bulk load security.
- Next by thread: Where is the connection attempt coming from?
- Index(es):
Relevant Pages
|
