?
Solved

service account is not sql login, but can backup.

Posted on 2011-03-16
21
Medium Priority
?
983 Views
Last Modified: 2013-11-10

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
Comment
Question by:anushahanna
[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
  • 12
  • 9
21 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35149661
Is that package scheduled to run in windows scheduled tasks or in sql server job?
What service account you mean ?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35150298
sql server job. both sql and agent service account is the same.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35150413
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 real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 6

Author Comment

by:anushahanna
ID: 35150459
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35150584
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35150682
OK- but who is running the package step in the job? service account, right?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35150741
It depend how you are executing package, is this step of SSIS type, CMDExec type or sql with xp_cmdshell query?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35151139
SSIS type
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35151245
You can execute SSIS step type by using either SQL Agent account or SSIS proxy account, if you have created one:

 ssis job
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35153320
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35154154
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35159631
exactly- the above query brings back blank.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35185906
Daniel, what do you infer from the above (service account is not a login by can backup through ssis package)?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35187032
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35189433
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
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35189540
Look on priviledges of those accounts:

EXEC sys.xp_logininfo
@acctname = 'NT SERVICE\MSSQL$POST04'
...
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35189600
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35189638
Admin is the sysadmin :)
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35189671
ah! so that means that both service account (in this case it is the same) are sysadmins and that explains it..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35189674

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
 
LVL 6

Author Comment

by:anushahanna
ID: 35189696
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
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