0

I have a SQL Server with SSRS installed. Note that SQL runs as NT Service\MSSQLSERVER SSRS runs as NT SERVICE\SQLServerReportingServices Version: SQL Server 2017

I am trying to automate the backing up of the encryption. I have a batch file called rskey.bat that contains the following:

enter image description here

There is a file called "answer.yes" which contains only a "Y" as input for the "Are you sure you want to extract the key from the report server? Yes (Y)/ No (N): " question.

When I try and execute this batch file from SSMS with this:

exec xp_cmdshell 'C:\Backups\rskey.bat'

I get this error:

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

When I run the batch file manually, it works. I just cannot get it to work through SSMS even when running SSMS as administrator

I have made NT Service\MSSQLSERVER & NT SERVICE\SQLServerReportingServices admin on the server already and has necessary permissions on the folder.

For some context: I want this to work so that I can schedule the encryption key backups. I have this exact method running on all other SSRS servers without any issue. The only difference is that all other SSRS servers run the services as a domain account and not the virtual account NT Service\MSSQLSERVER

Could this be the reason? How can I get this to work without having the services running as a domain account?

Any ideas?

I ran Process Monitor and dug this out:

enter image description here

Thanks

Improve this question
2
  • "I have made NT Service\MSSQLSERVER & NT SERVICE\SQLServerReportingServices admin on the server" bad idea and not necessary. Why are you trying to run this batch file from SSMS anyway, why not run it from Scheduled Tasks or something? I'd assume the actual issue is that MSSQLSERVER has no permissions on the keys belonging to SQLServerReportingServices, which is another reason to run it from Scheduled Tasks.
    – Charlieface
    Mar 23 at 10:13
  • I tested it with Task Scheduler and that works 100%. I set it to run as Builtin\Administrators. This helps confirm that it is a permission issue for the NT Service\MSSQLSERVER account
    – Sicilian-Najdorf
    Mar 23 at 10:56

1 Answer 1

Reset to default
0

The only difference is that all other SSRS servers run the services as a domain account and not the virtual account NT Service\MSSQLSERVER

I know virtual account provisioning can get a little weird sometimes. Admittedly, I'm also not very knowledgeable on the administrative side of Windows security.

But I'm curious if provisioning the appropriate permissions to the folder for the domain account that represents the machine itself (sorry I can't recall the name of this type of account) might work here. I.e. if the machine is called MySqlServerPC then I believe there should be an account called TheDomainThatPCIsJoinedTo\MySqlServerPC$ (or it may be TheDomainThatPCIsJoinedTo\$MySqlServerPC - I can't recall the proper format).

Again, bit of a stretch here, with my super limited knowledge, but let me know if that helps.

The other thing worth checking, and honestly may more likely be your issue here, is the proxy account that your xp_cmdshell runs under. This can be a Windows account that differs from the virtual account your SQL Server service runs under, and may be where your issue lies with provisioning folder security.

Improve this answer
1
  • I added domain\computername$ as admin on the server and still does not work. Also, I checked and xp_cmdshell does not run under a proxy - Nothing returned by SELECT * FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##'
    – Sicilian-Najdorf
    Mar 23 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.