July 11, 2016 at 4:00 am
Hi All,
I have a need to copy some files into a filetable during a deploy process to a nightly environment, using Bulkinsert (openrowset...). I have written a stored proc that does this nicely and works well, until I run it as the build account (or any other account not on the server). At this point it fails (unless I am running it directly on the server). I get the following error..
Cannot bulk load because the file "\\UNC SHARE PATH\FileName.etc" could not be opened. Operating system error code 5(Access is denied.)
The share has permissions set up for the windows users for both the share and the security on the folder.
This made me think that is was a kerberos 'double hop' issue. The server has a registered SPN for the SQL process account so that should not be an issue so I shrugged and tried using a SQL Login. I added the SQL process account to the share and the folder security and the SQL login now works from my local SSMS connected into the server using a share on my local machine...However, if I try running under another account and running the proc as the SQL login, I get the same error. I have checked that the proc is running in the context of the SQL Login by printing the SUSER_SNAME() and it retunrs the same from when it works (connected to the server as that login) and when it doesn't (connected to the server as another login and executing the proc as the SQL Login).
I have tried running the calling tsql as login (i.e EXECUTE ('Exec SProc .....) as Login = [SQL LOGIN] and I have also tried changing the context in the sproc itself and then reverting before it returns - both have the same failed result. I have tried granting impersonation on to the user as well as trying under my own sysadmin based account (neither worked).
I was under the impression that as long as you ran it in the context of a SQL Login it should use the SQL process account, which in this case has perms on the share and the folder (as demonstrated successfully when I connect as that user)
This also occurs if I copy the files locally to the SQL server and try to remove the share from the scenario.
Has anyone had any joy with using Bulk insert from a share and using permissions from another account that could shed some light on this?
Any thoughts much appreciated.
Thanks in advance.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 11, 2016 at 9:32 am
I've used OpenRowset for exactly that. But the automation executes as an AD credential that's in a group with the needed permissions. Simpler that way, in my opinion.
Are you automating using SQL Agent? Have you checked the job-owner credential? The job won't execute as the SQL process owner if it's a job with a different credential.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2016 at 9:49 am
Many thanks for the response; Much appreciated. [EDIT]...Sorry - I missed the 'credential' part of your post. I will give that a go (although not sure if that works if not called from the SQL Agent)
I agree it would be easier as an AD group but for some reason, it is not passing the credentials of the AD user running the command through to the share from SQL. I assume the dreaded kerberos double hop issue.:crying: This is why I went down the SQL Login route. It's supposed to use the SQL Process account in that instance and would avoid the doubnle hop, but for some reason it is not.
It's being called by a 3rd party app that runs our deploys rather than SQL Agent but I reckon that side if not really relevant as I cannot even get it to work via SSMS connected to the server.
Do you have anything special set up - SPN's on the SQL server and any other server?..anything else you can think of?
Thanks in advance.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 12, 2016 at 8:39 am
UPDATE - Solved! Pesky kerberos
Thanks for those of you that responded. Your ideas put me on the right track. I went back to grass routes and re-checked everything and it looks like there was a mistake in the manual registering of the SPN for the SQL instance and missing an additional entry as it was using a CNAME
Many thanks
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 12, 2016 at 8:51 am
Ness (7/12/2016)
UPDATE - Solved! Pesky kerberosThanks for those of you that responded. Your ideas put me on the right track. I went back to grass routes and re-checked everything and it looks like there was a mistake in the manual registering of the SPN for the SQL instance and missing an additional entry as it was using a CNAME
Many thanks
Ironically, this is one of the reasons why I use xp_CmdShell. It allows me to make well protected stored procedures that users (that includes Developers, applications, and all manner of non-DBA logins) can use but cannot change nor even see and doesn't require me to overcome the Kerberos security features. And, yeah... I call them "features" because they help keep an attacker from jumping machine to machine.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply