March 5, 2013 at 7:51 am
Hi All,
Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all objects owned by 'sa', but noted some system packages such as : PerfCountersCollect, PerfCountersUpload, QueryActivityCollect,SqlTraceCollect, TSQLQueryUpload, SqlTraceUpload. Any impact ananticipated post 'sa rename & disable on existing SQL2K8 R2 instances, or during SP1 to SP2 upgrade.
A good workwork-around was posted earlier for SQL2K5 (http://www.sqlservercentral.com/Forums/Topic560965-391-1.aspx) , but not sure if same applies to SQL2K8 R2.
Thanks
Othman.
March 5, 2013 at 10:48 pm
Disabling sa should be enough. I would forego the rename.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 12:25 am
I would also not rename the account.
Renaming it makes the account harder for hackers to locate and try to crack, but if the account's disabled there's no way to log in with it and hence it doesn't matter what the name is. Also there have been upgrade problems in the past with a renamed account. I would hope MS has learnt better, but I won't bet on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2013 at 4:17 am
Thanks Gail & opc for your reply.
March 20, 2013 at 3:51 am
Additionally, and where possible, I would leave the database with Integrated Security only. I know of very few instances where there is an absolute need to have an SQL Server Login ability. By having all your database user and group management controlled through Active Directory there is absolutely no chance of a SQL Server login being compromised.
March 20, 2013 at 7:41 am
kevaburg (3/20/2013)
Additionally, and where possible, I would leave the database with Integrated Security only. I know of very few instances where there is an absolute need to have an SQL Server Login ability. By having all your database user and group management controlled through Active Directory there is absolutely no chance of a SQL Server login being compromised.
That is the exqct opposite to my experience. In fact, having contributed in some really large corporate Enterprises as well as some one-instance shops, and lots in between, I can count on one hand the number instances not running in mixed-mode.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2013 at 7:53 am
The focus for my answer was actually based around the phrase "the absolute need to have SQL Server Logins". Are they truly necessary? In the vast majority of cases I have worked on, an AD-based account was quite capable of doing the job of the SQL Server account. Where it wasn't capable of replacing it, it was only because the software had been hard-coded NOT to accept AD credentials.
One of my first questions to vendors is whether or not an SQL Server login is necessary. If so, why? Normally the answer is one that offers me the ability to create an equivalent AD-based login. An unfortunate fact as well is that a lot of vendors tend to understand their software and their own underlying database but not the server on which it runs and the security models available.
So in answer to my own question "are SQL Server Logins truly necessary"? I would say very rarely.
March 20, 2013 at 8:07 am
That's fair, and you're right to challenge that point in an attempt to keep instances out of mixed-mode, I do the same. The reality is that it just takes one legacy app, or one client that cannot support Windows Auth, to cause it to be enabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2013 at 8:50 am
And that is my argument for at least two instances on each database server.....one with and one without! 🙂
March 20, 2013 at 10:25 am
kevaburg (3/20/2013)
And that is my argument for at least two instances on each database server.....one with and one without! 🙂
Yikes! You must print money over there...can you send me some 🙂 I won;t say never, but I cannot imagine a scenario when I would argue that needing mixed-mode should provoke spinning up a new instance to isolate those databases that have clients that require it. I am thinking memory management becomes harder and less efficient, licensing costs go up, maintenance costs go up for applying SPs and CUs, and you still have an instance with SQL Logins so what do you get in return? I would love to hear your reasoning behind such a position.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2013 at 11:52 am
You would love my environment then! I have 30 instances on a single box!
For the two instance argument it means I can protect the databases that run Integrated Security in an isolated environment. If by some horrible freak of circumstance an SQL Server login (sa for example) becomes compromised I can at least help to minimise the damage.
The amount of transactional databases we have I decided warrant the additional instances. The reason for it is that previously sysadmins paid absolutely no attention to memory management when the databases were installed. That meant (and this happened on two occasions) a runaway query brought the entire instance down (about 130 databases). My train of thought is that although from my side it is far more work and attention to detail, I can reduce the amount of damage that a single developer (that incidentally still insist on developing on production databases) can do to the entire instance. The problem is we have quite a few 24/7 databases and also time sensitive data that needs to be protected by almost any means possible. The only way I can do this is by ensuring the amount of memory available to each of these databases is as guaranteed as I can possibly make it!
If this were Oracle it would be a nightmare situation (Tom Kyte makes a point of saying 1 instance desrves 1 box!) but with SQL Server the management of instance memory is easier to control.
Licensing is a moot point: When you install a SQL Server, you are entitiled to install as many instances as you want without incurring additional costs! SQL Server supports up to 50 instances on a single box so off we trot then....!
Although you are right about patch updates and so on, another problem I have is patch level support for certain applications, differering collations and authentication methods. It is a brain tickler of an environment for sure!
March 20, 2013 at 12:34 pm
kevaburg (3/20/2013)
You would love my environment then! I have 30 instances on a single box!
Love might not be the correct sentiment 😀
For the two instance argument it means I can protect the databases that run Integrated Security in an isolated environment. If by some horrible freak of circumstance an SQL Server login (sa for example) becomes compromised I can at least help to minimise the damage.
It will depend on how diligent you are in terms of managing your SQL Server service accounts and the permissions they have on the server but there is a good chance that if someone get's sa in any of your mixed-mode instances that you're going to be cooked, i.e. they can likely take over the server and all instances on it. There [should] be a way (never had to deal with it but I will venture a guess it is possible) to configure each instance so a server takeover would not be possible if someone got sysadmin-level privileges in an instance, but I'd take the challenge to see if I could break into the other instances on the server if I were handed sa in one of them 😉
The amount of transactional databases we have I decided warrant the additional instances. The reason for it is that previously sysadmins paid absolutely no attention to memory management when the databases were installed. That meant (and this happened on two occasions) a runaway query brought the entire instance down (about 130 databases). My train of thought is that although from my side it is far more work and attention to detail, I can reduce the amount of damage that a single developer (that incidentally still insist on developing on production databases) can do to the entire instance. The problem is we have quite a few 24/7 databases and also time sensitive data that needs to be protected by almost any means possible. The only way I can do this is by ensuring the amount of memory available to each of these databases is as guaranteed as I can possibly make it!
Just my two cents, but I would probably spend my time getting really good at tricking out Resource Governor than with juggling more and more instances.
Licensing is a moot point: When you install a SQL Server, you are entitiled to install as many instances as you want without incurring additional costs! SQL Server supports up to 50 instances on a single box so off we trot then....!
You are focusing only on SQL Server...what if you are faced with the prosepct of using third-party software that is licensed per instance?
Although you are right about patch updates and so on, another problem I have is patch level support for certain applications, differering collations and authentication methods. It is a brain tickler of an environment for sure!
It sounds like you have a lot of vendor-based apps to deal with and I concede that sometimes isolating those to their own instance is going to be necessary, but it should be the exception instead of the rule. As you may know, the general recommendation is to consolidate instances to remove barriers to full utilization because it allows SQL Server to achieve a more complete use of the available server resources.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2013 at 8:12 am
Nice answer! You may not love but it is certainly a challenge!
The 'sa' account as you have mentioned becomes a risk if someone can get hold of the password and that is simply a risk that I want to mitigate as much as possible. The fact is is that if your instance uses Integrated Security then even if the 'sa' account is unlocked, it can't be used. One of the reasons I try and separate those applications that require SQL Server authentication and those that don't. If the 'sa' account on one mixed-mode instance is compromised then it is possible to compromise other instances with a couple of nasty tricks. The fact here is that if someone REALLY wants to hurt you, they are going to find a way regardless. The best we can do is try and mititgate the risks as far as possible. Additionally, our 'sa' accounts (regardless of the authentication mode) have differing and complex passwords.
Your point about the Resource Governor is a valid one and, much as I hate to admit it, a subject that I am far from a master of. To be honest though, although I have received a fair amount of critism (constructive though!) about how I manage memory on my servers, I am happy with the outcome. As I have mentioned several times, teh ability for me to be able to in effect, sandbox activity to within a single instance, has saved me on a couple of occasions. When I consider the amount of time spent setting up, configuring and monitoring this activity in the fist place, I regard it as a good investment. Not only was disruption confined to a single instance but work happening on other instances carried on regardless!
Your point about instance-based software licensing. This isn't one that applies to us as we don't have software that falls into this catagory.
I liked your last statement:
As you may know, the general recommendation is to consolidate instances to remove barriers to full utilization because it allows SQL Server to achieve a more complete use of the available server resources.
Ironically, removing the barriers to provide me with full utilisation offers up (in my situation) the problem of database activity starving other databases of memory resources. This is a situation that could probably be resolved using the Resource Governor but as mentioned, my knowledge in that area is still too thin to provide a solid platform of conformity across the instance. I will look at it but I am loath to fix a running system!
Thanks for your comments!
March 21, 2013 at 9:58 am
I just want to start out by saying that I have enjoyed our dialogue very much and that anything coming across as criticism is not meant to be taken as ill-willed. They are merely comments based on my understanding of best practices and my experience, of which admittedly I have very little in terms of managing more than only a few instances on a server at any one time. So, thanks for the dialogue and kudos to you for working out a system that can handle that many instances, and I presume customers, on one server.
kevaburg (3/21/2013)
The 'sa' account as you have mentioned becomes a risk if someone can get hold of the password and that is simply a risk that I want to mitigate as much as possible. The fact is is that if your instance uses Integrated Security then even if the 'sa' account is unlocked, it can't be used. One of the reasons I try and separate those applications that require SQL Server authentication and those that don't. If the 'sa' account on one mixed-mode instance is compromised then it is possible to compromise other instances with a couple of nasty tricks. The fact here is that if someone REALLY wants to hurt you, they are going to find a way regardless. The best we can do is try and mititgate the risks as far as possible. Additionally, our 'sa' accounts (regardless of the authentication mode) have differing and complex passwords.
Resource management aside and focusing only on security for this one: I am still struggling to see how splitting what could be one instance into two, where one is mixed-mode and one is Windows Auth, helps you maintain a higer level of security. This is highlighted even more in light of your confidence that no one will be able to break into either instance as 'sa', either because it is not possible in the case of the Windows Auth instance or because you have a strong password in the case of the mixed-mode instance.
As a side note, while I do end up running mixed-mode in the overwhelming majority of cases, I choose to disable the sa Login on all instances. Anyone requiring sysadmin-level permissions has their own personal (Windows where possible) Login added as a member of sysadmin. This takes one attack vector, namely brute-force password attack on the sa Login, off the table and increases auditability of sysadmin-level activity a bit.
Your point about the Resource Governor is a valid one and, much as I hate to admit it, a subject that I am far from a master of. To be honest though, although I have received a fair amount of critism (constructive though!) about how I manage memory on my servers, I am happy with the outcome. As I have mentioned several times, teh ability for me to be able to in effect, sandbox activity to within a single instance, has saved me on a couple of occasions. When I consider the amount of time spent setting up, configuring and monitoring this activity in the fist place, I regard it as a good investment. Not only was disruption confined to a single instance but work happening on other instances carried on regardless!
I too am no expert with RG. In my comments above I was leaning far more on my understanding of best practices than my limited amount of experience using RG.
Your point about instance-based software licensing. This isn't one that applies to us as we don't have software that falls into this catagory.
What are you using for monitoring? What about backups? Vendors for those two types of tools notoriously go for 'per instance'-licensing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 7:22 am
Sorry this answer took so long.....our Oracle world has been playing up!
For Production server backups we use BackupExec from Symantec for the databases and system snapshots for the server through ESX, Citrix or VMWare. In that way we can restore every thing at almost any level from the OS right down to the database.
Our test servers have normal SSIS jobs to backup the servers once a day. Nothing complicated but it kepes the developers happy if they make a mistake. Obviously space and licensing is at a premium so the test and development servers enjoy a much less complex environment.
Monitoring is achieved through several means: SCOM, Policy Management and timed scripts are the favourites. We are fortunate enough to be in the position that we know what specifically to watch out for in our environment and so we can target specific aspects. Because of an upcoming migration sizing becomes a recurring theme and the scripts to help us gather the information we need are becoming more and more popular.
You might have noticed that I haven't included SQL Profiler here. The reason is because I made a conscious decision to better my understanding of what goes on in the background and the SQL Profiler, although good at what it does, negates some of that need. The other reason of course is that the SQL Profiler GUI consumes resources like they are going out of fashion and has sometimes proven more trouble than it is worth.
UNLESS......
The Profiler is running as a server-side script. Sometimes I find that running server-side profiler scripts offers more benefits than creating my own scripts and running them on a schedule. As a quick and efficient means to gather information I do find it very worthwhile.....
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply