June 11, 2008 at 9:35 am
Hi Everyone,
What would it be the best while we are creating all databases in a new server, to use 'sa' or N'Domain\Administrator' user credentials or a separate [dbo] sql account?
One approach is because 'sa' is the ultimate powerful account, we should never apply to 'sa' account unless there is a reason.
For instance, we created all databases with N'Domain\Administrator' if the domain goes down, we have still control over databases by using 'sa' account. However, when we create all databases by appyling 'sa' account, if 'sa' password ever gets corrupted, we are out of luck.
Thanks for your opinions,
Regards,
June 11, 2008 at 11:34 am
As a security best practice, you should never use the Administrator account for the domain (and on another note, it should be renamed).
The general recommendation against sa is valid. We've considered having a different SQL Server login per database or per group of databases. This also ensures that if cross-database ownership chaining does get turned on, that dbo objects don't form ownership chains.
As far as the sa password, if you ever lose it, in SQL Server 2005 you can start the SQL Server in command line mode and reset it.
K. Brian Kelley
@kbriankelley
June 11, 2008 at 11:59 am
Regarding you question "When creating databases to use 'sa' or N'Domain\Administrator' user credentials or a separate [dbo] sql account?"
My answer is none of the above.
Security best practices dictate that logins should not be shared by different individuals due to lack of traceability and, that when responsibilities change, the only method to no longer allow personnel access is to change the accounts password. From an administrative point of view, the easiest solution is to create an Active Directory group, add all of the DBA's AD accounts as members, and then add the AD group to SQL Server with system administrators role.
SQL Server requires that the service accounts and the "sa" login have the system administrators role but no other accounts should have that role. You should be removing these privileges or the logins such as BUILTIN\Administrator. The cluster service account only requires a login and does not require the system administrators role.
After a DBA creates a database ( or a job created by a DBA does), execute the system procedure to change the owner to "sa" or, if using cross database ownership, to a login that is secure, is never used and the password should be unknown to anyone.
SQL = Scarcely Qualifies as a Language
June 11, 2008 at 12:03 pm
Thanks Brian,
So, your suggestion is to create a different sql login for DBA not a Windows user when we are creating databases and objects.
We used to use 'BUILTIN\Administrators' credentials to run those kind of DDL jobs. Is there any down side of that approach?
Regards,
June 11, 2008 at 2:07 pm
If you are just talking about possessing sysadmin rights, here's the basic recommendation:
- Use Windows users
- Put all the Windows users in a particular Windows security group at the domain level
- Grant that Windows security group the ability to login to SQL Server
- Make that Windows security group a member of the sysadmin fixed server role.
This ensures that if at some later time the organization decides SQL Server DBAs aren't to be administrators over their servers (at the Windows level), you're in good shape. The reason I pointed out SQL Server users is for who actually owns the database. By default, the creator of the database is the owner. You can set this as you create it, but most folks don't remember to do that. In that case, if you're not having the databases owned by sa (and that's reasonable), you don't want the databases owned by the actual Windows user who created it.
K. Brian Kelley
@kbriankelley
June 12, 2008 at 11:36 am
our databases all get set to SA ownership and SA has a secure password.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 12, 2008 at 11:46 am
Perry Whittle (6/12/2008)
our databases all get set to SA ownership and SA has a secure password.
And that's a standard practice. The reason to do different owners is to prevent ownership chaining of objects owned by dbo in the event cross-database ownership chaining ever gets turned on.
K. Brian Kelley
@kbriankelley
June 18, 2008 at 3:48 pm
exactly, BTW also forgot to mention that sa login is also disabled on our sql servers.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply