Problem with bulk load security.
RickB
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).
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.
Erland Sommarskog
> 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.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
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
rbie...@gmail.com
I guess I'm confused.
The only user that didn't have the bulkadmin roll was the ID that SQL
Server itself runs under.
That one wasn't even in the ACL anywhere.
So I assumed that was the 'service account' you were talking about.
But after adding the login and giving it bulkadmin nothing changed.
I still get "You do not have permission to use the bulk load
statement."
On one level I thought it made sense but since it didn't work I'm not
completely sure I understood what you wanted me to try.
Erland Sommarskog
> I guess I'm confused.
> The only user that didn't have the bulkadmin roll was the ID that SQL
> Server itself runs under.
> That one wasn't even in the ACL anywhere.
> So I assumed that was the 'service account' you were talking about.
>
> But after adding the login and giving it bulkadmin nothing changed.
> I still get "You do not have permission to use the bulk load
> statement."
> On one level I thought it made sense but since it didn't work I'm not
> completely sure I understood what you wanted me to try.
You said that you did EXECUTE AS. Did you to EXECUTE AS USER or EXECUTE
AS LOGIN? You need to do the latter, or else you will be sandboxed into
the current database and can't do things outside it, unless the database
is marked as trustworthy.
rbie...@gmail.com
I think that describes what I did.
I logged in with the ID that worked.
Then I defined the procedure using 'with execute as self'.
Which should be the same as 'with execute as <working ID>'
It's the only ID that seemed to have any hope of working.
As for user vs login, I don't see a syntax difference.
I only see this which indicates 'user_name' is my only option.
DDL Triggers with Server Scope and logon triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }
' login_name '
Specifies the statements inside the module execute in the context of
the SQL Server login specified in login_name. Permissions for any
objects within the module are verified against login_name. login_name
can be specified only for DDL triggers with server scope or logon
triggers.
Functions (except inline table-valued functions), Stored Procedures,
and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }
' user_name '
Specifies the statements inside the module execute in the context of
the user specified in user_name. Permissions for any objects within
the module are verified against user_name. user_name cannot be
specified for DDL triggers with server scope or logon triggers. Use
login_name instead.
Erland Sommarskog
> I think that describes what I did.
> I logged in with the ID that worked.
> Then I defined the procedure using 'with execute as self'.
> Which should be the same as 'with execute as <working ID>'
> It's the only ID that seemed to have any hope of working.
>
> As for user vs login, I don't see a syntax difference.
> I only see this which indicates 'user_name' is my only option.
OK, sorry I forgot that you were using the EXECUTE AS clause in a
store procedure.
There is also a *statement* EXECUTE AS, and this statement has both a
user and a login option. For a stored procedure you only have the
option of impersonating a database user, which means that unless the
database is trustworthy, you cannot get rights outside the database. And
ADMINISTER BULK OPERATIONS is a server-level permission.
I can see two ways to go.
One is to make the database trustworthy, ALTER DATABASE db SET TRUSTWORTHY
ON. As I recall, this should be enough. Now, making a datbase trustworhty
has some consequences that may be perfectly acceptable or entirely
unacceptable depending on the situation. If you are the DBA, and there
is no other person who have db_owner rights in this database without
also having sysadmin rights, making the database trustworthy is not a
big deal. But if there are persons who have privileges within this database,
but who not should be permitted to fiddle on server level, making the
database trustworthy is not adviseable.
The other option is a little more complicated. You remove the
EXCECUTE AS clause on procedure level. Instead you add EXECUTE AS LOGIN
before the BULK INSERT command and REVERT after it. If the Windows
login that runs the procedure is a privileged account this is enough.
Else you need to create a certificate in master, and create a login
from that certificate, and grant that login IMPERSONATE permission on
the Windows user for the service account. Then you need import the
certificate into the database and sign the procedure with the certificate.
Now, if that went over your head, don't be alarmed. I have an article
on my web site that describes the certificate business in more detail.
http://www.sommarskog.se/grantperm.html.
rbie...@gmail.com
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> 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
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.
Erland Sommarskog
> 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.
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.
rbie...@gmail.com
>
> - 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 actually
works 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).
Erland Sommarskog
> 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.
No, I did not suggest that. I suggested that you should try:
EXECUTE AS LOGIN = '<service account>'
go
BULK INSERT ....
go
REVERT
There is no CREATE PROCEDURE there. Just a plain EXECUTE AS statement.
The EXECUTE AS *statement* is the only way to impersonate a login.
Try the above from a query window. If this works, review the thread
and look at my earliers posts, how you could use this in a stored
procedure.
Damien
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
rbie...@gmail.com
I've been very sick and away from work until today.
Perhaps I can lean on that as an excuse for not seeing what you
said.
All the same I don't see anything immediately useful here.
1) go is illegal inside an SP
2) the bulk load can't be outside the SP since the SP also records
information about the file being loaded inside a transaction.
I tried executing the SP itself from the client in the manner you
suggested but of course it didn't work.
/*------------------------
EXECUTE AS LOGIN = 'ID'
------------------------*/
.Net SqlClient Data Provider: Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "ID" does
not exist, this type of principal cannot be impersonated, or you do
not have permission.
Frankly I'd have been appalled if it would work since it would
represent an enormous security hole, much bigger than allowing a
specific SP to be configured to run-as some ID.
rbie...@gmail.com
>
> - 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.
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....
Erland Sommarskog
> I've been very sick and away from work until today.
> Perhaps I can lean on that as an excuse for not seeing what you
> said.
>
> All the same I don't see anything immediately useful here.
> 1) go is illegal inside an SP
> 2) the bulk load can't be outside the SP since the SP also records
> information about the file being loaded inside a transaction.
No, I did not suggest that you should put this in a stored procedure.
No, I did not suggest this is the final solution. What I want you to is
*test*
EXECUTE AS LOGIN = '<service account>'
go
BULK INSERT ....
go
REVERT
Run this from a query window. If this works, there is a solution, that
I outlined a couple of posts back. But since that solution is a little
complex, I want to spend time on detailing it, if it does not work out.
Damien
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