Question

martenrune on Tue, 18 Jun 2013 14:00:55


Hi everyone

I'm in a environment where a number of systems do not hava a test/acceptance environment. For example telephony, or systems thats integrated with *IX systems, or squid weblogs, or ...

You see the issue, these system wont show their weakness until they are under pressure.

Now the delicate problem is that these system are in a majority of systems where the program when upgraded/hotfixed demands sysadmin rights.

I'm investigating a solution that looks like this (we run a consolidated DB plattform).

1. Backup systemDB, and all userDB
2. start a trace, checks sysadmin activities
3. examine trace, if rollback is neccessary, script out accounts created, jobs created and do a backup of the databases created
4. Rollback, or leave system as is.

My problem is the trace part. I should have all events thats in the security section. But what else should I log. XP_cmdshell activities? if so how is this best done. Check all jobs, and what right they run under. Can you give me your thoughts on the subject.

I know this aproach is not ideal, but how can I make it as good as possible? let the discussion begin.

Regards Marten
www.prosql.se


Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008


Sponsored



Replies

Uri Dimant on Wed, 19 Jun 2013 06:42:27


Marten

>>>>allow sysadmin role temporarily, is it possible to do in a secure way

You can create a powerful login  and issue EXECUTE AS LOGIN =.... running your scripts that require permissions....

>>My problem is the trace part. I should have all events thats in the security section. But what else should I log. XP_cmdshell >>>activities?

Have  you looked into a default trace? 


martenrune on Thu, 20 Jun 2013 05:16:17


Hi Uri Diamant

Thanks for your reply. I'm aware of this, and also of the possibilty to create a Login based on a certificate, grant this login high rights, and then sign a stored proc utlizing this right, then grant the proc to a low rights user.

But...
My problem specifically is that I have loads of 3'rd part software, they come with install program, and patches in the form of exefiles. To be run on the application servers, but they also upgrade the SQL database with jobs, and in some case creates users.

These applications are sometimes only possible to use in the production domain. I can give all of these their own instance, but I'm trying to keep them in a consolidated instance. Given these prerequsites the best thing I can Think of goes something like this:

1. Backup systemDB, and all userDB
2. start a trace, checks sysadmin activities
3. examine trace, if rollback is neccessary, script out accounts created, jobs created and do a backup of the databases created
4. Rollback, or leave system as is.

Now what events beside all in security should I include in the trace. How can I make it as small as possible, but still sufficient.

tips, experiences, comments, apreacheated

Regards Marten

Uri Dimant on Thu, 20 Jun 2013 05:46:58


Hi Marten

In this case I would install the separated instance and allow the application perform its things over there , so after completing the patch move the databases/jobs whatever  to the production...Dot not take not necessary risk.

martenrune on Wed, 26 Jun 2013 02:25:24


Thanks Uri

I guess the answer is that it's not possible. but...

I'm trying to get this working, since it would mean a huge difference in how many hours I need to put in yearly on every system. The unknown/new applications will be handeled with move/upgrade/move back. But a simplified upgrade process for the "almost thrustworthy" applications could cut one or perhaps two junior DBA's salary yearly.

Now this is a business case! Were willing to, if auditing and logging is possible in a acceptable way, use a simplified process for vendor patches/upgrades and accept the higher risk of the need to restore the system from the systembackups (thats done Before each of these upgrades). The benefit would be a much cheeper Environment. My superiour wants facts in order to assess whether this is a prefered solution for us or not.

Now given this business case, what can be recommended from the expertese of this forum. I understand that the solid/sound solution is what Uri proposed. Given the "business case" argument, how would you propose I take this further?

Regards Marten