Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

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
0
Deepak Kumar
Asked:
Deepak Kumar
  • 7
  • 6
  • 2
3 Solutions
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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ãoMSSQL 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ãoMSSQL 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
 
Deepak KumarEngineerAuthor 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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now