January 12, 2006 at 6:34 pm
hi everyone! i'm not very familiar with the SQL Server Security function. I'd like to ask if there's another way of denying an administrator's login access other than by clicking the Deny Access option under the Windows Certification (e.g. through program code or some other way)?
Thank you very much! It would mean a lot if there's anyone who could help.
January 12, 2006 at 7:52 pm
Yes. You can remove the BUILTIN\Administrators as a login. However, before you do, make sure the SQL Server service account and the SQL Server Agent service account have logins and are members of the sysadmin fixed server role. Also, if you're using Full Text search, make sure the NT Authority\System account has access and is a member of the sysadmin fixed server role. If you're on a cluster, make sure the cluster service account has a valid login to SQL Server. Also, make sure the DBAs have the needed access.
For a more fleshed out what to watch out for, look at the articles by myself and Kathi Kellenberger. Kathi's article is here:
Removing the Builtin Administrators - Some Pitfalls to Avoid
K. Brian Kelley
@kbriankelley
January 13, 2006 at 7:02 am
You've got to get rid of the BUILTIN\Administrator login to stop admins from logging in. DENYing access to that account is not the same (as I found out - thanks to Brian Kelly for helping me with that).
I am a sysadmin on the domain. My SQL Server login uses Windows Authentication through my domain account and has SQL Server sysadmin privileges. I DENYed BUILTIN\Administrators and could no longer log in. Brian reminded me about the fact that privileges are 'least privileges'. That means that even though I had sysadmin on SQL Server, by denying BUILTIN\Administrators access I was also denying myself access. However, REMOVING BUILTIN\Administrators stops Windows sysadmins from accessing SQL Server while still allowing me to access it.
But one other thing I learned by DENYing access....other things 'broke'. So I turned the access back on while I changed the logins for the things that 'broke'. (SQL Server Agent service login had sysadmin privileges). Once I fixed everything that 'broke', I was able to remove that login.
-SQLBill
January 27, 2006 at 7:19 am
I've removed the Builtin\Administrator account from my named SQL Server instance, and the services now start with a domain account, with SA privs in the DB. The Full Text Search service starts up OK, although I don't belive we even use it. The Distributed Transaction Coordinator is not starting, and it looks like that uses the LocalSystem account to start up.
Do I need to be concerned with this? I see this message in the SQL log - Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
I've read where this is the TransactionDispenser for MTS, and they say I should make sure that DTC is running???
Any help would be appreciated.
Thanks,
Steve
January 27, 2006 at 7:37 am
This error probably isn't related to your BUILTIN\Administrators change. MS DTC doesn't log in to SQL Server. However, this error is familiar to many of us who have dealt with DTC, especially on clusters. Do you run distributed queries to/from this servers through linked servers, OPENQUERY, OPENROWSET, or the like?
K. Brian Kelley
@kbriankelley
January 27, 2006 at 8:01 am
I do have a linked server defined, that gets an error message "Login Failed for user "NT Authority\Anonymous Logon"
but
I have the same setup in other instances, and the DTC service starts OK??? How can that be if it uses LocalSystem and I've removed the Builtin\Admin account?
January 27, 2006 at 8:06 am
Those two things aren't generally related. The error you're getting is SQL Server can't get connect to DTC, not the other way around. This is an error we started seeing a lot of on Windows Server 2003, especially on clusters. Windows Server 2003 SP1 contains numerous fixes for DTC.
K. Brian Kelley
@kbriankelley
January 27, 2006 at 8:13 am
You are correct. I looked at the SQL log from September, before I removed the Builtin\Administrator account, and we were getting that message back then. This is the only instance out of 10 where I see this message. We are W2K, SQL 2000 EE.
Thanks for your help.
January 27, 2006 at 8:15 am
This is nothing to do with DTC or builtin admins.
Check out "Security Account Delegation" in BOL - your linked srv is trying to pass through your NT credentials
Edit for 2 posts at same time
If only one box has the issue, then this box most likely is not "trusted for delegation" in AD.
January 29, 2006 at 9:33 am
I have remove the Builtin\Admin from my production server, and have a couple of problems. I see this message in the Windows Event viewer - SuperSocket info: (SpnRegister) : Error 8344.
I also see this message in the SQL Server log - "Performance monitor shared memory setup failed: -1" - and I no longer see the SQL Server objects in the Performance Monitor tool.
What am I doing wrong???
Thanks,
Steve
January 30, 2006 at 2:50 am
There is a post SP3a hotfix to fix this. It may 818, but best really to install SP4...
January 30, 2006 at 1:18 pm
We are already at SP3a, with hotfix 818. Does it have to do with registering a new SPN for the SQL Server service with AD? DO I need to be concerned with this message - Supersocket info: Error 8384???
Thanks,
Steve
January 31, 2006 at 1:03 am
You don't get this error if you use local system - it's to do with the domain user account not having rights to register SPNs in AD. It happens on all our servers (hundreds of them). Nothing to worry about. It still happens if you have registered an SPN manually as well.
The performance counter error is different and shoudlk have been fixed in http://support.microsoft.com/kb/812915/en-us . Hotfix 765.
Before I hotfixed, I used to have to reboot or stop SNMP and WMI services before restarting MSSQL... (IIRC - been a while)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply