June 23, 2011 at 3:01 pm
Hello Everybody
i have a doubt, i need to use database mirroring in 2 servers, one of production, i decide to use my domain account with admin privileges in the SQL Server Service to guarantee connectivity between 2 server (they have System Account right now)... so i put my account and my password in the log on
the doubt is than when shutdown and restart, sometimes i remember in other server than the password go miss and the service doesnt start, so i need to put my password again to start the service
this problem always appears? or maybe some problem with the server i see this?
thank everybody
June 23, 2011 at 3:09 pm
Did you grant "log on as a service" to the account?
I might set up a new domain account (base user, no rights), then use Config Manager to assign this to each SQL Service.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 25, 2011 at 10:27 am
humm, admin privileges doesnt have ALL privileges? the Network Admin said that when i ask him....
June 25, 2011 at 11:41 am
For a service account, you never want to use a personal login. You need to create a separate account just for SQL Server, and depending on your specific requirements you may want to create separate accounts for each role (e.g. Agent, SSIS, SSRS, Full Text, Mirroring, etc...).
This account should NOT be added as a login to the server or added to any groups. It should only be used to run SQL Server. The password should be set to a very strong password and the account set to never expire and user cannot change password.
Here are the issues with using a personal account. First, personal accounts passwords need to be changed on a regular basis (every 90 days at least). Change the password and the next time SQL Server tries to restart it will fail. Every job SQL Server Agent tries to run after the password change will also fail. Second, if you fat finger the password too many times trying to login to a system - the password is locked out which will cause agent jobs to fail until the account is unlocked.
You really need to create a separate account just for SQL Server - and, if you are on Windows Server 2008 when you install SQL Server you would use SID security which further isolates the service account.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2011 at 8:47 am
one question:
this 2 post guarantee than, when i restart the server, doen't give me an error in the SQL service because of the password? (when i use domain account, when restart, usually i need to enter in services and put again the password, seems like the service doesn't save passwords or something, this can give me trouble in production database, in 2am pc restart)
Steve Jones - SSC Editor (6/23/2011)
Did you grant "log on as a service" to the account?I might set up a new domain account (base user, no rights), then use Config Manager to assign this to each SQL Service.
Jeffrey Williams-493691 (6/25/2011)
For a service account, you never want to use a personal login. You need to create a separate account just for SQL Server, and depending on your specific requirements you may want to create separate accounts for each role (e.g. Agent, SSIS, SSRS, Full Text, Mirroring, etc...).This account should NOT be added as a login to the server or added to any groups. It should only be used to run SQL Server. The password should be set to a very strong password and the account set to never expire and user cannot change password.
Here are the issues with using a personal account. First, personal accounts passwords need to be changed on a regular basis (every 90 days at least). Change the password and the next time SQL Server tries to restart it will fail. Every job SQL Server Agent tries to run after the password change will also fail. Second, if you fat finger the password too many times trying to login to a system - the password is locked out which will cause agent jobs to fail until the account is unlocked.
You really need to create a separate account just for SQL Server - and, if you are on Windows Server 2008 when you install SQL Server you would use SID security which further isolates the service account.
July 8, 2011 at 9:08 am
I've not had an issue with service accounts and saving passwords, except when I have shared accounts.
Make sure that you pick a domain account that DOES NOT have "change password on login" checked. The password really should be a long, one time use password for this account that is entered only twice. Once to create the account, and once to assign the account to the service.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 8, 2011 at 1:53 pm
thanks a lot man, i ll try to convince this annoy and stubborn network manager, who doesn't get it...
one more thing... what kind of right the Domain Account for SQL Server Service need?
i see "Log on as a service", it's all?? i want it to put a log shipping between servers...
but this network manager sure wants to get all the specifications needed...(and blame me if i get an error)
Thanks a lot for the effort:cool:
Steve Jones - SSC Editor (7/8/2011)
I've not had an issue with service accounts and saving passwords, except when I have shared accounts.Make sure that you pick a domain account that DOES NOT have "change password on login" checked. The password really should be a long, one time use password for this account that is entered only twice. Once to create the account, and once to assign the account to the service.
July 8, 2011 at 2:52 pm
If you use Configuration Manager to assign the account, it should add the rights.
If you use log shipping, the account will need read/write access to the folder where it stores the log backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 8, 2011 at 3:20 pm
This link details permissions required for a domain user account. It's important to remember that as well as local security policy assignments the account needs ACls across various parts of the Windows NTFS file system. SQL Server configuration manager must always be used to change the account or its password. This SQL Server utility automatically grants the required permissions.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply