December 5, 2013 at 3:06 pm
I have an SSIS package (Stored in MSDB) that is retrieving a file from a server via UNC path \\Servername\Folder\File.txt. This is a W2K domain environment. I'm currently executing this package from SSMS using DTExec via xp_cmdshell. If I give the Authenticated Users group permission on the Folder it will process the file. If I don't it fails:
Description: The file name "\\\Servername\Folder\File.txt" specified in the connection was not valid.
Obviously this is a permission issue however I can't just give the Authenticated Users group access to this folder so I'm trying to find out what account is being used. I'm running this from my PC in SSMS and I have full Domain Admin rights. I've explicitly given my domain account access to this folder and file and it doesn't work. I can remove my account and give Authenticated Users access and it will work.
Anyone know which SQL "service" is being used to access file shares over the domain? Is it the SQL Server account?
TIA!
December 5, 2013 at 3:11 pm
run this to see what user is being used in xp_cmdshell. This should be the service account of the instance you are connected to. Give this account permissions to the file/share
xp_cmdshell 'echo %username%'
December 6, 2013 at 5:43 am
Robert,
Thanks for the reply.. Back in the office so I just got a chance to give it a shot.
It's returning my actual server name that SQL runs on: servername$. I'm executing xp_cmdshell from SSMS on my PC. There are no accounts by that name specifically. Maybe a network service account it's referring to?
December 6, 2013 at 6:13 am
Bob,
Yeah that was it. I updated the SQL Server account to a domain account and it now reflects that. Before it was running as Network Service.
December 6, 2013 at 8:00 am
I've run into this issue before and the way I was able to get around this was to create a batch command file w/i the job that starts by mapping a drive. When you map a drive you can use what ever credentials necessary to perform the task and limiting the access to a dedicated UserID. Then instead of using the UNC reference use the mapped drive. I typically end the process by disconnecting the attached drive.
Might not be the best way, but it gets the job done. 😀
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 6, 2013 at 8:14 am
AVB (12/6/2013)
Yeah that was it. I updated the SQL Server account to a domain account and it now reflects that. Before it was running as Network Service.
you could also have given the computer account (DOMAIN\computername$) access to the share although changing service account to a domain account is preferable in my opinion.
December 6, 2013 at 8:15 am
Kurt,
Thank you for the reply.
I read about a solution like that yesterday. They would map it and execute xp_cmdshell under a different user context. In some cases they would map the drive with a persistent connection. If I use the domain account I can just give that access to the drive but now I'm totally rethinking how I might handle the import of these files from within our application. I think I'm going to have to start a new topic.
December 6, 2013 at 8:52 am
Bob,
Yeah I agree. The issue I'm facing now is what is the best way to go about having our VB6 app process these files.
The scenario is this:
User downloads file from any source(email, ftp, web etc.) and save the file locally or on their mapped drive. They access an import utility from within the app and then navigate to the file(s) and initiate the import process.
Option 1: I could easily make the new SQL Domain account a domain admin so it can access files on any pc/share. The SQL server then could grab the file anywhere on the network. This would be the easiest but I think this is a bad option overall due to the elevate privileges.
Option 2: The other option is to give all domain users write access to a dedicate share on the SQL server or another server and have the application use the domain credentials of the person running the application to first copy the file from wherever it's located to that share. Question here is do I want to give ALL users access to a share on the SQL server or do I do it on a "file server". Again I'm not thrilled to give all users access but to make the application work among any computer without having to get a domain admin involved to assign privileges specifically then this is probably the best route.
Option 3. During the application install the app creates a domain user in the local admin group of that PC. This domain account would be specific to this app and could be used to execute the package and would be able to grab the file from the PC from the SQL server. Caveat of this is they wouldn't be able to save this file to their mapped drive.
December 6, 2013 at 9:36 am
I would go with option 2 utilizing a file server.
Option 1 goes against best practice of having your service accounts with limited permissions. If you sql server got compromised then your entire network would be as well.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply