?
Solved

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

Posted on 2016-09-01
15
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41779642
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
ID: 41779693
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
ID: 41779701
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41779738
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
ID: 41779748
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 51

Expert Comment

by:Vitor Montalvão
ID: 41779781
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 51

Expert Comment

by:Vitor Montalvão
ID: 41799816
Deepak, any news on this issue?
0
 

Author Comment

by:Deepak Kumar
ID: 41806082
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 41806088
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
ID: 41806682
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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 41806717
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 51

Expert Comment

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

Accepted Solution

by:
Deepak Kumar earned 0 total points
ID: 41837927
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 51

Expert Comment

by:Vitor Montalvão
ID: 41847794
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
ID: 41854993
Have done the deep analysis based on few artifacts and suggestions by Vitor
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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