March 10, 2005 at 8:34 am
The SQL Server services start-up account belongs to the local administrators group. For Sarbanes, I had to deny access to the builtin/admin group - the reason I had to deny instead of remove was that the application running of SQL Server - the vendor requires that the builtin/admin group be enabled for upgrades. So, when doing upgrades (which are often), I will enable access then disable. We have a SQLMAINT utility that runs and is now erroring out because it's trying to use windows authentication but the start-up account is denied access. The sql server agent uses SQL authentiation. If I were to create a sql account for connection for sqlmain utility - what kind of rights does it need -( I don't want to use sa and put the password in the command script)?
Thanks!!!!
March 10, 2005 at 1:46 pm
As maintenance plans always run under the SQL Server Agent Login and you cannot specify a Login and Password, your options are limited.
Instead of maintenance plans, you could invoke SQLMaint.exe/xp_sqlmaint directly but then you would need to hard code the Login and an un-encrypted password in the source. This creates a security opening as getting the un-encrypted password would be easy for anyone who could get access to either the msdb database or to the msdb backup file.
In either case, for the SQL Maint functionallity (Backup, Check and Reindex), the SQL Server Agent needs to have either Server sysadmin role or db_owner role in every database.
Easiest and secure enough would be:
1. Create a domain account to be used only by the SQL Server Services that has a very strong password and the password never expires.
2. Give the domain account the appropriate OS rights ( local admim)
3. Create a SQL Server login for the domain account.
4. Grant the SQL Server Login system administrator rights.
From Books On Line
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
DBCC CHECKTABLE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or the table owner, and are not transferable.
DBCC DBREINDEX permissions default to members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner, and are not transferable.
SQL = Scarcely Qualifies as a Language
March 10, 2005 at 8:58 pm
You can grant sa access to NT Authority\System account if you are using local system account and want to revoke access to builtin\Administrator group.
March 11, 2005 at 7:41 am
The start-up account is not using the local system account, but a separate domain account. It connects the server using SQL Server authentication. However, I can't just remove builtin/administrators - I have to deny access so I can quickly enable access when doing upgrades. So, the start-up account can't connect to the SQL Server (even though the services do start-up fine)..... So, I'm still unsure on how to work around this issue...
Thanks for your responses
March 11, 2005 at 7:54 am
SQL Server will always start using the account which you specify under services. The SQL Server startup account does not need sa privileges to start sql services. The startup account for SQL Agent services only needs SA privileges to start SQL Agent Services.
Regarding connecting to SQL Server, you can register SQL Server with any account in Enterprise Manager. Please note that this is different to the startup account. This will only restrict your functionality when you are using enterprise manager to perform some work.
With reference to granting priviliges for running xp_sqlmaint, i think you might have to grant atleast db_backupoperator in each and every database you want to backup(i will try to verify if you can have lesser privilege).
March 11, 2005 at 9:43 am
If a permission is denied from a SQL Server role or a Windows NT group, the permission affects all logins that are members of the group or role, regardless of the permissions that have been granted to the members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence.
So by running, sp_denylogin this means that all local administrators are denied login including the SQL Server services start-up account.
Regarding "However, I can't just remove builtin/administrators -", but you must as this is the only supported solution under SQL Server 2000. This will change under 2005.
Your only solution is:
1 Add the SQL Server services start-up account as a SQL Server login and grant the appropriate server roles (sysadmin to start).
2. Drop the builtin\Administrators login by running:
exec sp_revokelogin @loginame = N'BUILTIN\Administrators'
go
When access is needed, run
if not exists (select * from master.dbo.syslogins where loginname = N'BUILTIN\Administrators')
BEGIN
exec sp_grantlogin @loginame = N'BUILTIN\Administrators'
exec sp_defaultdb N'BUILTIN\Administrators', N'master'
exec sp_defaultlanguage N'BUILTIN\Administrators', N'us_english'
exec sp_addsrvrolemember N'BUILTIN\Administrators', sysadmin
END
GO
When access is no longer needed, run
exec sp_revokelogin @loginame = N'BUILTIN\Administrators'
go
SQL = Scarcely Qualifies as a Language
March 11, 2005 at 2:38 pm
Interesting... that is probably the policies that we will have to follow going forward if we have any issues. Thanks for all your help!
March 11, 2005 at 3:26 pm
I've run into similar situations. We use domain user accounts (one for each service) in a domain group which is in the Local Administrators group on each SQL Server. We then use Active direcotry to limit those accounts usage only to those specific SQL Servers. Then vie Group Policy we prohibit interactive logon for those service accounts. Next we added the domain user accounts for the services to each SQL Server and granted them sysadmin role. Then finally revoked builtin/administratiors. This works in most instances. We do have an application that checks for the bultin/administrators account, if it's not there it 'bombs' out. In a like scenario, we have another applicaiton that only 'upgrades' if it is run by the sa. The bottom line is that SQL 2005 will not fix everything. The applications are were the real work is required.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply