September 8, 2003 at 5:39 pm
Does anyone know a way to lock down Enterprise Manager up on the server so NT Administrators can not register with Windows Authentication? My problem is that I removed
the BuiltIn\Administrators account and have the SQL Services running under a Domain Account that is a member of the local Administrators Group on the Server. There are a ton of NT Administrators and developers that have access to Enterprise Manager under this account and can register using Windows Authentication and then gain full sysadmin priviledges. We are on a Active - Passive cluster and having sysadmin role on the Account that SQL rules under if you are in a cluster and have removed the BuiltIn\Administrators login. I recall there
is a way to disable registering with Windows Authentication on the server?
Thanks ahead for any or all information,
Eve
September 8, 2003 at 6:25 pm
My advice would be to cite this a security risk and ask to get the password for this account changed. It should then be put in as few hands as possible. For instance, in my environment only 3-4 people know the cluster account password for any given SQL Server cluster.
Locking down EM isn't really an option unless you can push a group policy (meaning you'll need to be in Active Directory). Otherwise, as you probably guessed, there is nothing stopping from someone who knows the password from logging on to their own workstation as that username/pass and then connecting to SQL Server via Windows auth in that manner. And you can't take sysadmin privs away because you need them for SQL Server to run in the cluster.
What you may want to do is audit logins for that account and then watch the security event log for the server for that user's login. Events with Event ID of 540 from the Logon/Logoff Category is what you'll be looking for. The event should contain the workstation name where the user connected from.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 8, 2003 at 6:39 pm
We already know who the users are but they continue to login and self-administer and register with Windows Authentication. How would you do this using Active Directory? Can you just restrict EM completely from the server level? Changing the account is something we are planning to do in the future, but this will take much planning and implementation will be over a month to completion due to all the dependent objects. I was looking for a quick way to do this until then.
Thanks again,
Eve
September 8, 2003 at 6:44 pm
You can restrict what snap-ins a given user account can view using MMC and you'd have to do that for this particular user. That requires a group policy and Active Directory.
When they are logging in, they are logging in with the Cluster account, correct? If so, are they logging in from the console or across the network?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 8, 2003 at 6:51 pm
I've had the same kinds of probems in other shops. Most of the time it takes a major screw up on a developers part to get management to lock everything down. If Brians sugestion works please let us know.
Wes
September 8, 2003 at 7:52 pm
They are going to run via Terminal Services and logging into not the cluster admin account, but the account SQL Server and SQL Agent run under that just so happens to be in the local Administrator's group on the server. So they are on the console, not using a client EM version.
What I just did and ran some tests and it looks ok for now is to only give permissions on both the sqlew.exe and isqlw.exe to specific DBAs. I removed permissions off these .exes for everyone else. I also made the files hidden. Do you think this is good enough? Or can they get around this?
BTW, do you have an article about setting this up in Active Directory?
Thanks again,
Eve
Edited by - stelze on 09/08/2003 7:57:51 PM
September 9, 2003 at 6:41 am
I'll look for the article. I think I saw it on the SecurityFocus site.
Since you brought up Terminal Services... Another idea that comes to mind is the use of IPSec Policies. With an IPSec policy, you could specify which clients (by IP) could connect and block all others. RDP listens on TCP/3389 by default so that's the source port on an IPSec policy (with source IP being the server). More on RDP and its ports:
http://support.microsoft.com/default.aspx?scid=kb;en-us;187623
If you make use of DHCP (most folks do) and you can't reserve particular IP addresses for particular clients, you can set up such that it only accepts a connection if someone has the equivalent of a passcode. That would also require configuration on the client side. Here is a basic article on setting up an IPSec policy to lockdown communications between a web server and SQL Server. Of course, you'll have to make changes based on port, etc., but it gets you started. It also has links to additional resources for IPSec.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/HTUseIPSec.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
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