Server administrator vs SA
-
Hi there,
We're doing a security review and considering wether giving server administrator permissions to the DBAs or not. (Of course they have SQL server Sa)
Can you tell me where, when working with sql server 2005, a server administrator permission is needed?
Thanks!
-
They don't need Windows administrator privileges to do their work. If they have sysadmin rights in SQL Server then they can do pretty much anything that the account running SQL Server's service can do.
-
Hi Justing,
If you have network communications and/or clustering, and you use domain accounts for SQL Service and SQL Agent, then you might find that they are required to have local admin rights. If you split the domain accounts, on for each service, then usually only SQL Agent requires local admin rights. Whatever way, the DBA will need to know the passwords to these accounts to set up SQL Server, and so could also connect to the box using these accounts. Personally, I make use of certain commands that make use of the OS and networks. Some of these require me to have local admin rights for these boxes. This may be the case for your guys, too.
In our company I'm trusted with local admin rights to all the boxes which hold SQL Server and I use a 'named' domain account that can be audited back to me. I don't use the SQL Service accounts unless absolutely necessary. With regard to the 'sa' account, you're better off focusing on that and ensuring that no-one uses it, including DBAs (except for in emergencies), putting in a very strong password and locking it down (this account has no auditing).
Rgds iwg
-
First of all, thank you for your help.
When I said that DBA had granted sa, I meant that they domain accounts had. They know sa password because they may need (eg: if the domain server fails) We are working too on auditing. 😉
We'll be having strong replication & network traffic between at least 5 or 6 sql servers. Also, they already have access to the folders of the servers.
The sql server and agent account are both runned on local administration accounts. Since we need to use xp_cmdshell in some processes, I accept the DBAs are able to run anything on the servers, so it's ok for me to grant them the server administration privileges. (They'll be able to stop and start sql server services)
But I still, do you know a reason to grant them the same privilege in their local pcs?
Regards,
Justing
-
Just a thaught from a power user point of view.
You trust this guy with maintaining 5-6 servers. On which he is an admin.
Now if you can trust if with that much power on the lifeblood of the compagny. Why the heck can't he be trusted on his own local PC?
I'm not trying to flame any one here... but it just seems a bit strange from my point of view. However I never had to enforce strict security so I might be completely off tracks here.
-
You may be right, but it is not a matter of trusting the person (I surely do).
The problem is that, if a virus (or any security issue) reaches is user/computer, it coud do anything with it. And, if they don´t have the right to install anything, so the virus would not run.
Anyway, I agree with you. It's just a matter of internal policy and it is usually unlogical. 😉
Justing
-
I agree that it is more secure that way... but if the virus can reach is PC, what's stopping it from reaching the other servers?
Also I can't say I'd be able to remove his permissions and look him straight in the eyes after that... but maybe I'm too soft for that job
. -
Hi Justing,
Sorry I haven't got back sooner, but I've been away.
Ok, slight confusion here. Your initial question was concerning what 'Server' permissions your DBA(s) may need. You didn't ask about their local pc's.
So, lets move forward and try and help. First of all, hopefully you have AV running on their local PC's. This should negate some of the risk. We run AV on both our local pc's and on the servers. To run AV on the database servers you will need to make sure that you follow the Microsoft recommendations, ie no scanning of mdf, ndf, ldf and backup files, and a few other bits too. This should mean that you are protected as much as you can - remember, AV is only as good as the last signature file it has. Unknown virii may well get through until the AV vendor has written and deployed an antidote.
When I spoke of the 'sa' account, of course it would be the DBAs who would know it (though not necessarily every DBA, depends on their responsibilities). However, as an example, we, in our company, do not use the 'sa' account, except for absolute emergencies. The password is extremely long, and a mix of alpha (upper and lower case)-numeric and special characters. These passwords are near impossible to remember, unless you are one of those 'memory' people. The passwords for our 'sa' accounts are different on each server. These passwords are held in a secure environment (both electronic and print). The printed copy is in a sealed (and signed) envelope and stored in a safe that only certain people have access to. Once a quarter, and at ad hoc times, a check is made of the envelope to ensure it has not been tamperred with. Any use of this document for emergencies means a change of all passwords for all 'sa' accounts in that document. Further, we do not allow applications to be installed (whether in-house or by 3rd Parties) if they use the 'sa' account. This is an example that may be too extreme for most, but in our environment where there is a potential for money laundering, theft, etc., it is essential. Why do we go to such lengths? The 'sa' account is a throwback from the Sybase days. This account is the equivalent of 'god' in the database server - it is all-powerful and can do anything. It has limited auditing and Mircosoft have been threatening for a long while to get rid of it. Perhaps, because the account is, and has been, abused so much by DBA's, developers, and 3rd Party suppliers that pressure to keep it is too great.
Looking at your reply, you say the DBA '...had granted sa, I mean that they domain accounts had...' The 'sa' account is a stand-alone SQL Server account. It has nothing to do with domain accounts. Anyone who has permissions to log on to server, and knows the 'sa' password will be able to log on to the SQL Server Instance. You will not be able to associate easily which domain/local windows account is using the 'sa' account in any auditing. Remember, this account is powerful and you don't want just anyone using it.
Here are a couple of links you may find useful:
https://www.microsoft.com/sql/prodinfo/previousversions/securingsqlserver.mspx
http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp
Rgds iwg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply