• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

service account is not sql login, but can backup.


within a ssis package, there is a backup task. then this package is scheduled and run by the service account. even though the service account does not have a login, how is it successfully able to do the backup?

thanks
0
anushahanna
Asked:
anushahanna
  • 12
  • 9
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
Is that package scheduled to run in windows scheduled tasks or in sql server job?
What service account you mean ?
0
 
anushahannaAuthor Commented:
sql server job. both sql and agent service account is the same.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Who is the owner of a job? It is executed in owners context, in the filesystem SQL server service account permissions are used.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
anushahannaAuthor Commented:
owner is srbsv\jimwe
but this package is uploaded to integration server and then it is part of a step in the sql job. the only option in that step is that the service account will run it.

can you explain what is meant by "in the filesystem SQL server service account permissions are used."
0
 
Daniel_PLDB Expert/ArchitectCommented:
I mean when you are creating backup in the filesystem SQL Server needs to create a file, and it's permissions are used for that.
0
 
anushahannaAuthor Commented:
OK- but who is running the package step in the job? service account, right?
0
 
Daniel_PLDB Expert/ArchitectCommented:
It depend how you are executing package, is this step of SSIS type, CMDExec type or sql with xp_cmdshell query?
0
 
anushahannaAuthor Commented:
SSIS type
0
 
Daniel_PLDB Expert/ArchitectCommented:
You can execute SSIS step type by using either SQL Agent account or SSIS proxy account, if you have created one:

 ssis job
0
 
anushahannaAuthor Commented:
thanks for showing the option other than service account.

but in my case, i am using service account only. he is not a login but how is the backup happening fine?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Can you verifz whether SQL Server Agent account has no access? (put there it's service account):
 
EXEC sys.xp_logininfo
@acctname = 'Domain\User'

Open in new window

0
 
anushahannaAuthor Commented:
exactly- the above query brings back blank.
0
 
anushahannaAuthor Commented:
Daniel, what do you infer from the above (service account is not a login by can backup through ssis package)?
0
 
Daniel_PLDB Expert/ArchitectCommented:
We've determined so far that SQL Agent and SQL Server service accounts is the same account. You're saying that Agent account isn't login in SQL Server, are you 100% sure of it? By default SQL Server and Agent service accounts are added as logins in SQL Server RDBMS with sysadmin server roles. You can verify which service has which account in Sql Server Configuration Manager (start->run->(type) sqlservermanager10.msc).
0
 
anushahannaAuthor Commented:
yes, i confirmed the service accounts and that they are the same for sql and agent. now, you gave me a clue:

EXEC master..xp_logininfo 'NT SERVICE\MSSQL$POST04','members'
EXEC master..xp_logininfo 'NT SERVICE\ClusSvc','members'
EXEC master..xp_logininfo 'NT AUTHORITY\SYSTEM','members'
EXEC master..xp_logininfo 'NT SERVICE\SQLAgent$POST04','members'

are the ones i am not sure about in the logins- could one of these have the service account inside of it.

all of them return the error "Could not obtain information about Windows NT group/user, error code 0x8ac."
0
 
Daniel_PLDB Expert/ArchitectCommented:
Look on priviledges of those accounts:

EXEC sys.xp_logininfo
@acctname = 'NT SERVICE\MSSQL$POST04'
...
0
 
anushahannaAuthor Commented:
i get

account name      type      privilege      mapped login name      permission path
NT SERVICE\MSSQL$POST04      group      admin      NT SERVICE\MSSQL$POST04      NULL

account name      type      privilege      mapped login name      permission path
NT SERVICE\SQLAgent$POST04      group      admin      NT SERVICE\SQLAgent$POST04      NULL

what is the admin here mean?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Admin is the sysadmin :)
0
 
anushahannaAuthor Commented:
ah! so that means that both service account (in this case it is the same) are sysadmins and that explains it..
0
 
anushahannaAuthor Commented:

thanks a lot Daniel. I see you are active in EE for past 2 weeks with lot of insight and experience.. hope you enjoy the experience...
0
 
anushahannaAuthor Commented:
Daniel, could you please give me your feedback on

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26826520.html 

if the question interests you..

thanks again...
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now