Installing on SQL 2008
wayneph
Posts: 29
I have my first new SQL 2008 server, and I am working on getting SQL Backup installed. I'm running into a couple issues (quite possibly with my thought process) so I wanted to throw out what I was wanting to do...
1. When I set up SQL 2008, I used a Domain Account as the Service Account for both SQL and the SQL Agent. It does not have any direct permissions in SQL. (NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT have appropriate permissions.)
2. I was planning on using the same user for running the SQL Backup service.
3. I only want this account to be able to backup databases. (other than the permissions from the NT SERVICES.)
4. When I'm trying to install, it's asking for the Service Account, so I provided the credentials, but when it goes to install it can't because the account can't create the Extended Stored Procedures. (Completely Expected.)
5. Can I install the Backup Service using the Service Account, but create the Stored Procedures using my credentials? Or do I need to install it using my credentials, and then just change the service to the service account? (I think the second option will work...)
6. Once I get this installed what permissions do I need to give the account? I'm assuming it will need access to the sqlbackup extended stored procedure, and "BACKUP DATABASE" or db_backupoperator in each database it's going to backup. Is there a way to configure this to so that it will automatically get this permission in all databases? I know I can add it to Model, but that doesn't get restored or attached databases.
I'm trying to listen to Microsoft and limit the permissions for the service account, but it's seeming like more hassle than it's worth. (Obviously it's not, but ignorance is bliss...)
Thoughts?
1. When I set up SQL 2008, I used a Domain Account as the Service Account for both SQL and the SQL Agent. It does not have any direct permissions in SQL. (NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT have appropriate permissions.)
2. I was planning on using the same user for running the SQL Backup service.
3. I only want this account to be able to backup databases. (other than the permissions from the NT SERVICES.)
4. When I'm trying to install, it's asking for the Service Account, so I provided the credentials, but when it goes to install it can't because the account can't create the Extended Stored Procedures. (Completely Expected.)
5. Can I install the Backup Service using the Service Account, but create the Stored Procedures using my credentials? Or do I need to install it using my credentials, and then just change the service to the service account? (I think the second option will work...)
6. Once I get this installed what permissions do I need to give the account? I'm assuming it will need access to the sqlbackup extended stored procedure, and "BACKUP DATABASE" or db_backupoperator in each database it's going to backup. Is there a way to configure this to so that it will automatically get this permission in all databases? I know I can add it to Model, but that doesn't get restored or attached databases.
I'm trying to listen to Microsoft and limit the permissions for the service account, but it's seeming like more hassle than it's worth. (Obviously it's not, but ignorance is bliss...)
Thoughts?
--wayne
Comments
See here for further details.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8