September 6, 2002 at 1:56 am
What are the user permissions required on an NT server in order to connect with Enterprise Manager and have full SQL Server and database access?
We just had a security change yank our access from the NT server, now we cannot connect with EM. The issue is the classic SA vs DBA division. The SAs do not want the DBAs to have admin rights on the server. They removed us from the Administrators group now EM doesnt work.
Thanks.
September 6, 2002 at 5:52 am
Cant think of any permissions required. SQL manages the files, as long as you can get a connection to the box and authenticate with SQL, EM should work.
Andy
September 6, 2002 at 7:06 am
They yanked you out of the local administrators group and you not can't connect?
Were your NT user accounts granted logon rights to SQL Server through another group or by individual user accounts (bad, groups are better) other than through BUILTIN\Administrators? If not, that explains why you suddenly don't have access.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 6, 2002 at 7:38 am
Sounds like Brian is right on there with why you now don't have access. Have the SA's logon to SQL Server and add you NT account as a login and place you in the sysadmin role. This should allow you to have all the SQL Server access you need.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 9, 2002 at 2:18 pm
Had the same problem.
Went to plug up all the security "holes" left by the previous admin and shut myself out. Deep in some manual we found that Enterprise Manager uses "Use Trusted Connection" in the "Register Server" dialog of EM. Even if you select "Use Standard Security". We were performing maintenance from another server with the same original admin password. When I changed passwords, access was denied. Even when typed actual SQL SA Id/Password and selected "Use Standard Security".
Had to set myself up in the "Administrators" group to get it working again - and that's what your NT administrator just broke. I'm sure there should be a way to set up an SQLAdmin group in the domain & configure SQL for it, but I didn't know how or have the time to fix it.
-- Sorry about the length - David
September 10, 2002 at 3:45 am
I haven't seen the issue with SA not working when the account was pulled unless your EM was cnfigured to use the trusted connection. Usually you can get around by re-registering the server.
Now for the sake of this conversation as suggested by Brain and David have the SA create a domain group called SQLAdmins and add the DBA accounts to it. Also, for the sake of being a pain you could have him setup several other types of accounts to make sure you have groups mapped already for ServerBackup, AccessControl and a few others just to show them what resposibility they now must accept and to make sure they do their job which is to fully support you. Anyway take these groups and grant them access like the BUILTIN\Administrators account in SQL Logins but seperate them into proper funtional roles. Also, due to this change there will not be a lot of things you may have issues doing depending on the permissions they provide. Make sure you have rights to stop and start services otherwise you need to set a clear definition of what you do and what they do in your group and make sure if you are a 24hr shop that you have an SA available for all troubleshooting and rebooting needs. If they want to bark let them it is their job but make sure they give you proper support and appropriate SLAs for you environment.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 10, 2002 at 7:27 am
I also haven't seen the sa not being able to connect in EM, but I believe there is a thread where someone else is seeing it. I can't reproduce the results, so I'm not sure as to the cause.
As far as yanking you guys out of the local administrators group, unless you've locked down your SQL Server where the services aren't running under accounts with local administrator rights, they haven't accomplished much. SQL Server by default runs under the localsystem account unless you tell it differently. That account has administrator rights on the box.
I don't say this so the DBAs can thumb their noses at the sysadmins. After all, I was the senior DBA and now I'm the server team lead where I work, so I have sat on both sides of the fence. Fighting about isn't going to do any good. But if they are serious about locking everything down, they need to know your use of commands such as xp_cmdshell give you administrative privileges unless SQL Server and its agent is set to run in less than administrator rights roles. There's a checklist of things which have to be done to ensure SQL Server and SQL Server agent can run without having full administrative rights, but when you do this you lose some features (such as SQL Agent being able to auto-start on failure).
We practice a mixed security model where I work. There are some systems which due to their nature, DBAs don't have local administrator group rights. But these are considered exceptions. We're talking servers like SMS, Altiris, and a particular cluster which serves a dual role. But in the other cases, we have granted them such rights because we have a measure of trust for the group. Not every organization works this way, but it's the most efficient way for us to get the job done and we've taken into account the risk. But let me warn you, there is some. We've had a DBA almost break a cluster by tinkering where he shouldn't. So even through procedures may be in place, you are always taking that chance.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply