Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Disable & Rename 'SA' Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
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!
Post #1433382
Posted Wednesday, March 20, 2013 12:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
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
Post #1433411
Posted Thursday, March 21, 2013 8:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
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!

Post #1433809
Posted Thursday, March 21, 2013 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
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
Post #1433882
Posted Tuesday, March 26, 2013 7:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
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.....
Post #1435469
Posted Wednesday, March 27, 2013 8:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:20 PM
Points: 356, Visits: 1,971
kevaburg (3/20/2013)
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.


If I had a developer who insisted on using production for development, my management would get an ultimatum - him or me, with an explanation of the hazards associated with developer access to production. Doing development on a production database is stupid to the extreme, and possibly illegal if your company is subject to US SOX regulations. I can't think of a reason to ever allow a developer anything other than read access to production data.
Post #1435938
Posted Wednesday, March 27, 2013 10:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
Ross McMicken (3/27/2013)
kevaburg (3/20/2013)
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.


If I had a developer who insisted on using production for development, my management would get an ultimatum - him or me, with an explanation of the hazards associated with developer access to production. Doing development on a production database is stupid to the extreme, and possibly illegal if your company is subject to US SOX regulations. I can't think of a reason to ever allow a developer anything other than read access to production data.


Believe me, I am with you all the way except the part that reads him or me! Jobs are hard enough to find! We have this Problem with both the SQL Server and Oracle Environments but additionally, our department head (only one down from the CEO) is also from a development Background. He has determined that they shuold have this ability, as stupid as it is.

The only Thing I have really been able to do is Audit who does what and when. Even that he wanted turned off due to certain restrictions we have here in Europe but to that end my Ultimatum was that auditing would be used or their accounts would be locked and I would have to account for my Actions. That is something in this case I would be happy to do.
Post #1436020
Posted Tuesday, April 2, 2013 1:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 559, Visits: 1,206
That department head should be persuaded that his career would benefit from an ITIL qualification or two! At least Foundation but achieving the full Managing across the Lifecycle would keep him out of your hair for a while and he'd learn how it should be done!
http://www.itil-officialsite.com/
Post #1437729
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse