Solved

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

Posted on 2016-09-01
15
30 Views
Last Modified: 2016-10-22
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
Comment
Question by:Deepak Kumar
  • 7
  • 6
  • 2
15 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
 

Author Comment

by:Deepak Kumar
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:Deepak Kumar
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Deepak, any news on this issue?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Deepak Kumar
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
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
 

Author Comment

by:Deepak Kumar
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Deepak, please let us know if you still need more help with this question.
0
 

Accepted Solution

by:
Deepak Kumar earned 0 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Closing Comment

by:Deepak Kumar
Comment Utility
Have done the deep analysis based on few artifacts and suggestions by Vitor
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now