Discussion between Facebook and Robert

4:56 AM

0

Q: Unable to access/create files on another machine (same domain, Security permissions set)

I have two computers: Computer1 and Computer2. They're on the same domain. Computer2 has a directory I want Computer1 to have access to (well, mssqlserver account). So I: Computer2: navigate to C:\...\Repository properties -> Share Add Domain\Computer1$ (had to add Computers to the search area)...

Robert

oh geez, that last part was a typo on my part. it is \\Computer2\Repository... sorry, I'll edit. As far as NTFS/share, I did the right click, properties, and Sharing and add permissions there. Not sure which one that is of the two (nor how to set the other). The specific user account may be the impossible for me (specifically) to fix as I can't affect AD or the like. I tried to do Domain\Computer1$\nt service\mssqlserver and it couldn't locate that. I may have to do a temp repository until a sys admin can address

Facebook

Sounds like you need to EXECUTE AS LOGIN = '<domain>\<service>';, then built your SQL command, and then exec master..xp_cmdshell @cmd to ensure that the command in SQL and the UNC paths which it needs to access is the security context in which it is executed. You cannot grant access to a local computer account to a server share so that's probably your problem. I used to do this all the time and probably have some answers out there lingering around. Tag me back if you need more if this isn't enough and I'll dig up some old scripts for you or something.

Robert

SQL Server is listed as NT Service\MSSQLSERVER and SQL Server Agent as NT Service\SQLSERVERAGENT (oddly, they're not local accounts, but under This account). Security tab is setup now. Same result unfortunately, but still using Computer1$

Facebook

Execute as an SQL proxy account, read my comment above but if you're launching as SQL Agent job, use the domain credential as the proxy account; I can dig up more detail if needed just trying to give you pointers based on my experience with this same issue man.

Robert

@Facebook if it is NT Service\MSSQLSERVER, would it be Domain\NT Service\MSSQLSERVER for that call? I know when trying to set up permissions I attempted NT Service\MSSQLSERVER to no avail. By that, I mean on Computer1 for Repository

I have admin rights on Computer1, but I'm just a developer. I am limited-elevated (I guess would be the term. Received individual permissions like being able to see agent for jobs and the like) on the SQL DBA side and was trying to get around the domain user part because that's only in the sys admin area (understandably)

Attempted as a check the EXECUTE AS LOGIN with my domain credentials (admin on Computer1, full access to Repository) and it errors out when trying to read from it unfortunately

Facebook

4:56 AM

Rob - Can you grant your domain user account access to the SQL DB instance and grant it applicable DB level permissions needed for whatever DB objects the query will access? You could then grant that same domain account access to the UNC path on the other machine, and then execute as that login to then execute the xp_cmdshell. Just saying, I always done this with security groups or user account object and not computer level objects for the security.

That account may need the SQL level permissions and the UNC level permissions as well, yes that is correct, so set it up and try again. If you want to access a network resource, just do that with a domain user account and/or security group. Done and next

Robert

UNC level permissions? Where do I find that? As far as SQL server, I can read/write/drop tables/etc with Windows login (pretty much everything short of administrate other users)

Facebook

Yes, both... including the UNC NTFS and the SHARE level permissions for your domain user account that you used with the EXECUTE AS LOGIN

Rob - Grant your domain user account the permissions to the share and the folder the share points to on \\Computer2\Repository... Give it modify permission in share and properties of the folder permission wise too. The NTFS ACL permissions are just the folder permissions, the share level permissions are the share permissions of the shared folder you setup in its properties.... does that help?

Robert

my user account does have share/security permissions on that location. But it seems EXECUTE AS LOGIN = 'Domain\MyUserAccount' didn't change the behavior.

Facebook

Sorry man, I used to do this all the time with multiple instances of SQL Server using BCP and other commands with xp_cmdshell and using UNC paths for various things too but I'd have to likely see if to help and start ringing some bells in my head.

I'll poke thru some of my saved scripts to see if anything rings a bell such as putting select user_name(), suser_name(); at the end of the query to confirm the security principals those return.

Rob - One other thing that just stuck out to me, where you list this :\...\email.exe for this executable email file, be sure that the domain account has at least read and execute to that directory if it simply just executes that exe file.

I just realized you have the SE DBA correlated question.

Robert

6:09 AM

gotcha. The 1st stored procedure in the job is EXECUTE AS LOGIN = 'Domain\User' -- per what you told me. / declare @x varchar(300) set @x='c:\Projects\...\email.exe "Function"' / exec master.dbo.xp_cmdshell @x. The email.exe is where it returns back that it can't access that remote repository. If I log into the SQL Server box as Domain\user and then run email.exe "Function" from command prompt, it does everything as expected.

the second just iterates over and looks for new items to email the user who submitted and myself that the error happened. It functions (where I get the message about …

Transcript for

Discussion between Facebook and Robert