XP_CMDSHELL & RS.EXE and access rights

Question

XP_CMDSHELL & RS.EXE and access rights

chris.stuart

SSCarpal Tunnel

Points: 4965
More actions
February 24, 2017 at 3:40 am

#340978

Hi
I'm trying a solution where via a stored procedure I map some drives, the frist to store the SSRS report exported as a Exxcel attachment and the second where I map a drive to where the RS.EXE is sitting on my C drive.
Will testing the parts seperately everything works, I get the report, create the Excel file, and email it. Perfect.
However putting everything into one stored procedure, I keep on running into the following error
The permissions granted to user 'NT SERVICE\MSSQLSERVER' are insufficient for performing this operation.
Im using the following command
set @CmdShell = 'x:\rs.exe -i "Y:\Test.RSS" -s localhost/reportserver -e Exec2005'
EXEC XP_CMDSHELL @CmdShell
Testing this via the command prompt everything is perfect, but not via the cmdshell.
Any suggestions with how I can get the XP_CMDShell to run with elevated access rights?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

When you do it via a command prompt, it runs as you. When you do it through xp_cmdshell, it runs as the SQL Server service account. You need to ensure that the service account has permission to run x:\rs.exe, and also permissions on the share that you mapped to. If the share is on another computer, you'll need to change the service account to a domain account in order for it to have access to the network (NT SERVICE\MSSQLSERVER is a local account).

John

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 2

John Mitchell-245523 - Friday, February 24, 2017 3:50 AM
When you do it via a command prompt, it runs as you. When you do it through xp_cmdshell, it runs as the SQL Server service account. You need to ensure that the service account has permission to run x:\rs.exe, and also permissions on the share that you mapped to. If the share is on another computer, you'll need to change the service account to a domain account in order for it to have access to the network (NT SERVICE\MSSQLSERVER is a local account).
John

Hi John

I cant add the accout NT SERVICE\MSSQLSERVER to have access to the mapped drive. I've tried each and everyway of adding the account NT SERVICE\MSSQLSERVER to the shared directory, but the Windows cant "find" the account. I've tried with quotes, brackets, with the NT service, without it, and each and every other way. but Im not having any luck adding the account to the share. I've almost given up on this and thats why Im looking to run the cmdshell as a different user.

PS, all of this runs on one server and no maps to any other servers.

Thanks

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

OK, if it's all on one computer, you shouldn't need to map any drives.

What do you get if you run this?
SELECT servicename, service_account FROM sys.dm_server_services

John

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 4

John Mitchell-245523 - Friday, February 24, 2017 4:51 AM
OK, if it's all on one computer, you shouldn't need to map any drives.
What do you get if you run this?
SELECT servicename, service_account FROM sys.dm_server_services
John

Hi

Here is the answer, thanks

SQL Server (MSSQLSERVER) NT Service\MSSQLSERVER
SQL Server Agent (MSSQLSERVER) NT Service\SQLSERVERAGENT

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 5

Have you tried running SQL Server under a named Windows account (either domain or local) instead?

John

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

John Mitchell-245523 - Friday, February 24, 2017 5:19 AM
Have you tried running SQL Server under a named Windows account (either domain or local) instead?
John

+1000 to that. For the most part, the SQL Server Login should be assigned as a regular Windows user for reasons just like this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 7

John Mitchell-245523 - Friday, February 24, 2017 5:19 AM
Have you tried running SQL Server under a named Windows account (either domain or local) instead?
John

Hi John

Sorry when it comes down to the security and different accounts on Windows / Sql Server, Im a bit at a lose with it. How would I go about it?

Many thanks

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 8

Also remember I have to automate this report via the SQL Server agent, Im not sure what sort of credential I should use there as well.

Many thanks

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 9

This is how you do it. You need two separate accounts, one for SQL Server and one for SQL Server Agent (you can use the same for each but it's better to separate them). Ask your network admin to create the domain accounts for you, and then make sure you grant both accounts the necessary permissions on the reports and folders that you need to access.

John