February 5, 2009 at 6:24 am
Hi folks!
When logged in as DOMAIN\user, running the command below:
RESTORE FILELISTONLY FROM DISK = N'\\backupshare\backupfolder\database.bak\'
... the service account for SQL Server is the account that really connects to our backup share and gets the filelist.
Is there a way to check when a command is executed/performed as the service account, instead of the DOMAIN\user account that is logged in, and executes the statement?
Hope you understand my question 🙂
Thanks in advance!
February 22, 2009 at 7:46 pm
Hello Gordon,
Do you want to be able to see who is running the SQL statement? Run SQL Profiler against the server in question and look at the "NTUserName" column for the statement you're interested in. Backups and restores run as the SQL Server service account.
Regards,
Jacob
February 23, 2009 at 1:31 am
Thanks for your answer Jacob! I had pretty much given up on getting a reply on my question 🙂
We had some security issues with a backup share, and I executed some backup/restore commands to try to find out which user is executing the statements. In my Profiler trace it was MyUser for the NTUserName and DOMAIN\MyUser for the LoginName, and not my SQL Service Account (SSA)... but according to our backup shares log, the statements were executed as my SSA. We added the SSA user to the backup share, and everything worked just fine!
I ran it again, and it's still the MyUser and not SSA that is shown in Profiler.
What is it that I'm missing and doing wrong here?
Thank you very much!
February 23, 2009 at 2:42 am
I guess you are loged on with your domain\yourusername and running the backup so it's giving your info in profiler. Now in schedular you are using SSA so you just need to wait till scheduler execute the the job. You will get SSA.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 23, 2009 at 4:58 am
Thank you for your answer!
That's correct. I'm logged in with my DOMAIN\MyUser account when running the backup/restores in SMSS ... Which eventually shows in Profiler.
The problem is, that when I ran the same query against our backup share, the logs said that the query was executed with the SSA. It wasn't a scheduled job...
I'm confused 😉
February 23, 2009 at 3:50 pm
Sorry Gordon, re-reading my original post I see it wasn't especially clear. You will only see the Windows login of the user actually running the BACKUP/RESTORE statement (you in this case) in the Profiler. Irrespective of who actually runs it, it will use the SQL Server service account to access any network resources (like your backup target UNC path).
So the identity of the SQL instance as it "reaches out and touches" remote network resources is the service account in a BACKUP/RESTORE scenario, which is one of:
- a local Windows account on the SQL server (no access unless coincidental match of username/password)
- LOCALSYSTEM (permissions as per the SQL server computer account's access to other computers on the domain)
- a domain account (permissions as per this account's access to the target resource)
The best way to verify this is to turn on Windows auditing for object access (local or group policy on the target computer) and do your BACKUP/RESTORE. You will see the SQL Server service account accessing the files in the Security log - you've obviously already done something similar.
Regards,
Jacob
February 25, 2009 at 12:03 am
Thank you very much Jacob!
Now it makes alot more sense, when you explain how it really is.
As you suggest, and what we've already done, was to turn on logging on the backup share. That's when we found out it really was the SSA account that "touched" the backup share.
I'm very thankful for your answer, and not confused anymore! 😉
Also thanks for your detailed explanation on which accounts are possible as SSA.
Take care!
February 25, 2009 at 12:30 am
Happy to help!
Regards,
Jacob
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply