Differential backup Job executed as 'SA' login Name internally as per the SQL Profiler trace

Hi there,
I am running Profiler trace for tracking login attempts and strangely found that the differential backup job which is called from maintenance plan has been logged as 'SA' in Login Name column of profiler trace but however the NTusername is still the service account which is configured to be used by SQL Agent.

Could you help me out why the Login Name was called as SA rather than the service account name in Login Name column?

Thanks in advance!

Regards
Deepak
Deepak KumarEngineerAsked:
Who is Participating?
 
Deepak KumarConnect With a Mentor EngineerAuthor Commented:
Hi Vitor,

I am digging much more on the SQL Agent Job Login Name issue and found something interesting. I have made two tests with different settings of an SQL Agent Job-1 as below and my findings are

1)Test One-
 SQL Agent Job-1 - Owner : 'USERXXX' and in the Job step "Run as " different user 'UserYYY'
   Output: Still found that the Login Name was logged as 'SA'

2)Test Two-
  SQL Agent Job-1- owner:' USERXXX' and in the Job Step "Run as" different User 'UserYYY' but disabled the 'SA' login.
 Output: Still found the internal call are made via 'SA'.

then here comes our last test
3)Test Three-
  SQL Agent Job-1 Owner:'USERXXX' and the job step "Run as " different user 'UserYYY' but this time i have changed the database owner of MSDB from 'SA' to 'UserYYY' (Just for testing purpose i have changed the DB owner of MSDB)
Output: found that there were no login of 'SA' made by internal calls.

Now my question is "Is 'SA' should be the database owner for all the system databases? Is it a recommended practise or something else to consider ?

thanks
Deepak
0
 
Lee SavidgeCommented:
The SQL Agent may run under a given users Windows credentials, but when you create a new job to run a maintenance plan, you can choose the owner. It is reasonable to choose SA or create a specific user for them to run under.
1
 
Deepak KumarEngineerAuthor Commented:
Hi Lee,
   
 Just want to be sure, you are talking about the owner details given in the Differential Job->properties->owner right? yes there it is mentioned as 'SA'. I have few questions on this?

1)will there be any impact if i change the owner of the Job to the same as the service account on which the SQL agent is running?

2)After i change the owner to other name(preferably the service account of sql agent), will it create any issue in future if the SA login is disabled?

thanks
Deepak
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Lee SavidgeCommented:
I'd be careful about disabling the sa user. Sometimes it's worthwhile just giving it a decent password and making sure nothing uses it.

Can you be sure nothing relies on the sa account?

If you change the owner, it won't make any difference as long as the owner has the required permissions and security settings to allow the job to run, and access to the right databases.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
1)will there be any impact if i change the owner of the Job to the same as the service account on which the SQL agent is running?
Depends. What kind of permission the SQL Agent account has? It has access and it's able to create files in the backup location?

2)After i change the owner to other name(preferably the service account of sql agent), will it create any issue in future if the SA login is disabled?
Why do you need to disable the SA account? Do you have any other user with similar permission?
Also if you don't have any other SQL Server login then you can change the SQL Server authentication to Windows Authentication so SA won't be able to login anymore even if it's enabled.
1
 
Deepak KumarEngineerAuthor Commented:
Hi Virto,
 
  1)What kind of permission the SQL Agent account has? It has access and it's able to create files in the backup location?
            Yes the SQL server Agent account has all the required permissions.

2)Why do you need to disable the SA account?
    There was a debate on this as the SA account details were shared with all the team folks which shouldn't have happened. Also client and security team is much concerned about the SA logins as its a big threat for the hackers(as per their views).

thanks
Deepak
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes the SQL server Agent account has all the required permissions.
Then you should not have any issue by changing the job owner.

Also client and security team is much concerned about the SA logins as its a big threat for the hackers(as per their views).
Change the password to a strong one or permit only Windows Authentication to the SQL Server instance.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Deepak, any news on this issue?
0
 
Deepak KumarEngineerAuthor Commented:
Hi Vitor\Lee,
      I have been questioned by the client on few columns of Profiler trace data.

Why did the sessionLoginName of profiler trace shows as "service account" on which the sql agent is running is different from the LoginName column which shows 'SA'.

Could you also explain me on which account permissions the SQL agent Job is actually executed? SA or service account.

Your help would be much appreciated!

Thanks
Deepak
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Why did the sessionLoginName of profiler trace shows as "service account" on which the sql agent is running is different from the LoginName column which shows 'SA'.
That depends on which user is the job owner. Is the SA in this case?

Could you also explain me on which account permissions the SQL agent Job is actually executed? SA or service account.
It depends on which user you defined as the job owner.
0
 
Deepak KumarEngineerAuthor Commented:
Hi Vitor,

   Thank you for your reply, Yes currently the Job owner is 'SA'.

Could you explain me, what would be the best practice to change the job owner from 'SA' to ?

Regards
Deepak
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Best practice for security is always the same. Do not give more than the task will need.
Using SA means that if SQL Server needs to access the system files (it's the case for a backup job) then you need to assure that the SQL Server account (not SQLAgent but the engine itself) needs to have read and write permissions in the destination folder.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Deepak, please let us know if you still need more help with this question.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Now my question is "Is 'SA' should be the database owner for all the system databases? Is it a recommended practise or something else to consider ?
Yes if you have SQL Server Mixed Authentication enabled.
0
 
Deepak KumarEngineerAuthor Commented:
Have done the deep analysis based on few artifacts and suggestions by Vitor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.