| Author |
Topic |
|
poncho
Yak Posting Veteran
62 Posts |
 Posted - 2004-05-19 : 11:27:27
|
| We had an issue that was posted on this site awhile back. We were concerned with local admins having access to our DB on SQL Server(we are a development house). I won't go into the details of why....but you can read it here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34032Basically all we need to do is install a new Instance of SQL server, have them provide us with the SA password and we take care of the rest. We remove any unwanted user/groups from this instance, reset the SA password and create the appropriate user/groups for our apps. Simple enough and it won't interfere with other instances of SQL already installed....What puzzles me is that after 8 pages of responses from you DBAs, all of you failed to mention this was an option!Just thought I'd let you know. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-19 : 11:33:00
|
| Well from the initial question>> Not even the SYSADMIN of the SQL server in question would be able to view that database in Enterprise AdminWhat you have isn't a solution.Haven't read the thread but sounds like the fault was not finding out what you really wanted - which is often the main problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-05-19 : 13:20:12
|
| actually we did ind a solution. We simply reset the SA password to something else, remove "built in\admins" ...therefore they will have no access to the database unless they have the new SA password, yet will still have complete access to all their instances of SQL server. Looking at "instances" in SQL server, seems to me the main reason is to be able to share physical resources without sharing security resources. 3 different depts. can have their databases on the same physical machine and each dept. can have their own DBA limited to their database...sounds simple enough to me...is there something I am missing? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-19 : 13:27:27
|
| They can still have access if they have sysadmin fixed server role, which is what the DBAs will need in order to perform maintenance routines and other DBA related activities. So if you haven't granted sysadmin to the DBA accounts, then the DBAs can't do thier jobs in that instance. So without them having this, you'll have to do this work on that instance. This would not fly with us here.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-19 : 14:16:38
|
| Instances do provide seperation of security and resources. They are also nice in that theoretically one instance going down does not affect the other instances. You have to be very careful with how you allocate resources though for this to be the case. If you don't set it up right, you just end up with a big cluster. Like Tara said though, you will still need the ability to have the DBA's administer the server for performance monitoring, backups, etc. Also, you still have not taken into account full administrators of the domain.What puzzles me is that after 8 pages of responses from you DBAs, all of you failed to mention this was an option!Just thought I'd let you know.Also, this shouldn't surprise you. You don't get the greatest help when you antagonize people. You might want to consider this in the future.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-05-19 : 14:23:44
|
| "This would not fly with us here." Well it would have to, simple as that....They will not have access to the sysadmin role, or any other role for that matter. All maintenance on the DB will be done via our application as a front end, they do not need to have direct access to the SQL server instance to do so. Simply calling these functions from our app will suffice....Tara, you still seem to lack the perspective needed to understand this. Our clients are not the DBAs, they are the Administration. They do accept these very logical and necessary conditions. Thier local DBAs, if they even have one, have no reason to be putsing around with a 3rd party app that their administration is paying thousands of dollars annually for support and maintenance...they don't want them messing things up and then paying us ontop of their support contract for db corruption or dataloss caused by their internal staff....You got to look at this from the side of management, not IT. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-19 : 14:31:04
|
| I understand your point. But you have to understand that you have come to an IT board, so...You say your application will handle the admin routines. So does that mean that a user will click button to launch them? What happens when they rebuild the indexes during the day which causes the tables to become unavailable temporarily? Even if your app just launches them on a scheduled basis, it means that your application must connect with sysadmin. That is so wrong for security reasons. Who is going to react when DBCC CHECKDB fails? Will the user get a message saying the database maintenance routine has failed and the ugly error that comes with it? Will your company get paged? IMO, the best option is encrypting the data.Tara |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-05-19 : 14:45:15
|
| "So does that mean that a user will click button to launch them? What happens when they rebuild the indexes during the day which causes the tables to become unavailable temporarily?"We already monitor who's logged into our app and provide the necessary measures to to db maintenance, it works fine. The person tasked to do this verifies the availability of the DB, and does the work necessary when necessary. These is available to us for MS SQL as well. Here: http://msdn.microsoft.com/msdnmag/issues/01/05/sqldmo/default.aspx We will not connect with sysadmin, we will create SQL users with different roles and security accesses, and uses these roles to perform the tasks necessary. We will offer all maintenance procedures available in MS SQL via SQL-DMO with our app as a front end. This way we maintain control and a secure environment."You don't get the greatest help when you antagonize people"It didn't start that way....it was the ridiculous replies I received to my original question that put me on the attack! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-19 : 14:50:40
|
| So will the application use db_owner then? That's the only way you can run DBCC commands. db_owner is not recommended for applications to use either. Here's one very important DBCC commands permissions:DBCC CHECKDB permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.Tara |
|
|
|
The Enigma
Posting Yak Master
179 Posts |
Posted - 2004-05-19 : 15:09:52
|
| The Argument Continues ... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-19 : 15:12:01
|
| OK, I gotta....What's your Disaster plan look like?Brett8-)EDIT: Damn, no self control whatsoever |
|
|
|
The Enigma
Posting Yak Master
179 Posts |
Posted - 2004-05-19 : 15:16:57
|
| Well ... i believe there will be no DRP ... the application takes care of that :) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-05-19 : 15:33:24
|
quote:
Originally posted by poncho
What puzzles me is that after 8 pages of responses from you DBAs, all of you failed to mention this was an option!
Oh really? It looks to me like you brought up the idea on Page 1 of the "discussion", but apparently didn't like what you heard. So, on page 2, we changed our recommendation to you getting a separate server that you could administer and installing a new instance on that server. But you didn't like that idea either. And now you're saying that the separate instance on an existing server is the Holy Grail solution you've been looking for all along?I don't want to get the whole argument/trolling expedition going again, but I think I figured out the problem in that previous conversation. It appears that you are marketing your product to companies/people who are not used to running large, mission-critical database systems, and are happy to let you handle all of the database administration tasks. Typically, these people buy into the marketing buzz of "no administration necessary". But unfortunately for you and for us, you came to a site populated with people who have spent (and many still do spend) their days cleaning up the messes left behind by "no admin" systems that hit critical mass and collapsed under their own weight and lack of admin options. And we really, really, would rather that companies hire knowledgeable DBAs to build and administer robust systems with security (including background checks and bonding of the DBA if necessary), checks and balances, and backups rather than gamble the company's future on a vendor's assurances that "everything will be all right, we'll take care of that for you, no need for you to worry your poor little head over it".Give us a little credit. Many of us have been in the IT industry for many, many years and have seen systems come and go, and have seen companies come and go (both our employers and vendors). If you want to sell a "no admin" system, go right ahead. But if you get some push-back from an experienced IT person, try not to jump to the conclusion that they are arrogant or just trying to protect their job. Maybe they're actually trying to look out for their company the best they can, and what you're offering falls on their radar in the "too good to be true, I don't trust it" category.In my 16 years in the professional IT sector, with 12 years as an independent contractor, business consultant, and owner of multiple businesses, I have seen a thing or two. And I would not allow one of my own companies to agree to your terms. I suspect that's a similar backing to Tara's comment that "This would not fly with us here". And all that really means is that either a different arrangement would be negotiated, or we wouldn't do business together. Nothing personal, just business.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-05-19 : 16:17:13
|
| "If you install a named instance, the DBA will still need access to the server if they are the DBA for that system. The DBA will usually have local admin privileges which means sysadmin on all SQL instances."Is the answer I got to using a new instance....I now know that the sa account is different for every instance installed, allowing us to do what we wanted...all through that first posting noone mentioned that you can isolate the SA account from instance to instance...everyone jumped down my throat and told me it can't be done....the attitude flowed towards me first...i just defended myself.And we are not touting an admin free software, we are simply delivering a complete solution, not some half ass product you deal with normally. We have been in this business for over 20 years, sucessfully I might add, and are now migrating to MS SQL server for a number of reasons. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-19 : 16:21:24
|
| The statement is still true. By removing the BUILTIN administrators group, that means local admins don't have the access. The DBA though would still need access to the additional instance in any large corporation. If you are talking ma and pa shops, then they won't have a DBA and this discussion is pointless. But larger corporations will and the DBA needs to protect all instances by running normal maintenance routines. We didn't mention isolating sa as sa isn't really used anymore, in fact it's not recommended. People use sysadmin on their Windows account. This is preferred as everyone has their own account. But isolating sa doesn't solve your problem. Why isn't encryption being considered? Have you tested the performance of it?Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-05-19 : 17:38:20
|
| Poncho, obviously there were several breakdowns in communication. You felt attacked and "defended" yourself by attacking others, just as you have now done with me by stating that I "normally" deal with "half ass product[s]". And this attack/counter-attack approach doesn't lead to clearing up communication. There is still a breakdown on the terminology for sysadmin and SA. As Tara points out, if and when you implement your solution, we would recommend that you not use the SA login. Instead, create individual logins and assing appropriate roles. At least one login will need to have the System Administrator role with full permissions. This is generically referred to as being a "sysadmin" without specifically being SA or Built-In\Administrators. Any login (either Windows or SQL) can be added to the System Administrator role. Whether you implement this on a separate server or just a separate instance of an existing server is completely up to you. I'd suggest you look into potential performance issues of running on a shared server.As for your final paragraph, I (apparently mistakenly) interpreted your description of what you wanted to do as being an admin-free system (at least from the customer's perspective). Also I apparently confused a comment by maggie thinking it was from you where she touts a SQL engine that eliminates the need for a DBA. Sorry about the confusion there. I am a bit curious however where you state that "We have been in this business for over 20 years...". Are you saying that you, personally, have been in the business of developing secure database systems for 20 years? Or that you have been in the business of selling a software product for 20 years? Or that the company you work for has been in business for 20 years? See how un-clear communications can be? When I was stating my credentials in my previous post, I was talking about my own personal experience. Looking back at it now, I see how it could have come across as arrogant, but it was intended to just be factual.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-20 : 11:38:32
|
| I think one of the biggest issues here Poncho was that you were unlucky enough to stumble into the forum the same time Maggie did. She was extremely antagonistic and combined with your posts, it was just extremely annoying for all of use. We're here to help people, and we do it for free. People like Maggie just don't belong on forums designed to help people.You do need to know the upsides/downsides to what you are doing. You can restrict the access on the particular instance you are looking at. You need to make sure you never do this on a critical production server though. If you do, you are going to be hurting your own business in the long run. For mom and pop shops, this would be fine. Make sure you look at the following:--Your application needs to password protect the backups.--Your application needs to have a batch process that does integrity checks, reindexes, etc.--It needs to send an email to your organization if any errors are found that would require for example going to single user mode to fix. This does happen even in the best designed system.--You need to not use the sa username and password. Set up roles like you were talking about earlier and have users for these.--To protect the username/password combinations, you should look at network encryption also.---I'm not sure how crazy you want to get on this one.For corporation such as the ones Tara and I work for:--You really should develop an appliance to do this kind of thing.--Provide the option of instances and make sure you VERY CLEARLY spell out the downsides.---If your application has an issue, they are screwed until you come out and fix it for them.---You could also provide an ability to remotely trigger access for administration purposes.There are still a host of issues that need to be addressed. I believe I suggested to both you and Maggie that you look at www.sqlsecurity.com where you would have found the answer you listed in this post and several others. You really should look at them.Also, don't attack people on these boards. They really can help you and want to help you. They won't though if you're an ass to them though. Try to remember that. This applies to any of the SQL Server sites out there. These people don't get rich by running these websites.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-20 : 14:54:30
|
| Not wanting to sound nasty but if you are offering a full solution (not half-assed I think was your expression) including dministration of the server you might wnat to invest in some sql server expertise - especially if you are doing it remotely which has some different requirements from a local adminstrator (e.g. you probably don't want to use enterprise manager).Also if you are migrating from other products you should look at the design as sql server is a lot less forgiving than some other databases.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
poncho
Yak Posting Veteran
62 Posts |
Posted - 2004-05-21 : 11:24:36
|
| thanks for your help guys...BTW we are not going to use the actual sa account, our plan is to have various roles and use these roles to perform the tasks necessary. For instance we do allow ODBC access to the data via other apps, we simply restrict it to read only, requiring a specific user account for that.We will try to maintain all sysadmin functionality but through our interface, permitting local admins to perform maintenance tasks without them know the actual user/password to the db and allowing us to control what and when they perform these tasks."I am a bit curious however where you state that "We have been in this business for over 20 years...". "We have been in the business of developing software solutions (for a specific sector) for over 20 years. They have always been database applications. In the last 10 years we have migrated from a flat file to Novell + Btrieve and lastly to Windows/Novell and Pervasive SQL. We are now migrating to Windows/MSSQL. Our expertise in developing is pretty extensive, however we are new to the MSSQL world. I personally have tinkered with MSSQL for a little while now, and we will consult experts if needed, but until now have been able to solve any obstacle we have been confronted with.We develop and test our applications thouroughly, including customer test sites. We take all feedback from customers and analyse it, resulting in a superior product. In our current market, we have the best solution available, as told to us by our customers and independant consultants mandated to evaluate all offerings available to our customers. Obviously there is room for improvement, its a never ending cycle, but we feel we provide a strong product.Our MSSQL migration will be a long process, we estimate 12-18 months, allowing us to correct any securuty, performance and stability issues that may arise.As it stands now, a seperate instance for our apps seems to be the most viable solution. It allows us to maintain access control on our particular stuff without interfering with other dbs on the server. That's all we wanted to do from the beginning.thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-21 : 12:17:24
|
quote:
Originally posted by poncho
We will try to maintain all sysadmin functionality but through our interface, permitting local admins to perform maintenance tasks without them know the actual user/password to the db and allowing us to control what and when they perform these tasks.
What happens when a problem arises such as a disk problem that causes a corrupted database? How will the DBA fix the problem? They won't be able to do it from your application as you would pretty much have to build Query Analyzer in your application in order to cover everything that the DBA will need to do? I guess you could give the sa password to the DBA when it happens. But what if it happens in the middle of the night. Will the DBA call your 24x7 customer support line to get the sa password when he/she is paged about a critical problem in the middle of the night?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-21 : 14:38:27
|
| Also, how is your application going to insure the instance does not interfere with the requirements for the other instances? You need to worry about the memory pool, which is critical when you set up instances. You also have to consider the processor pool. This is in addition to the critical support functions Tara mentioned. It's a lot to consider. For any major corporation, you really need to deliver an appliance. There are good reasons for this. You do need to consult Microsoft and a good SQL Server/Harware/Network group to work through these issues. If you don't, your company will hold liability for the damage it causes. If you have resolved these and pushed the responsibility off to your client, you endanger the relationship there.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Next Page
|
Found solution to my problem...