Backup domain 2 domain problem...

[garyb]

Hi,
At our DataCentre I have SQL server on the LAN side of the firewall and IIS server on the DMZ side. I have setup the rules to allow traffic from LAN 2 DMZ and 1433 from DMZ to LAN therein safeguarding the data should the IIS box get breached.

I decided to backup the SQL files to the IIS box using NTBACKUP, didnt work so tried normal xcopy batch files again didnt work. However they do work when I run them manually, ie run the batch file from a command prompt or NTBACKUP direct from its console, its only when they are scheduled via task scheduler or NTBACKUPs own scheduler that problems occur with no real indictaion why.

For example if I run "xcopy D:\mydb.bak \\remoteserver\bakupfolder\" it works great. If I schedule this using task scheduler it fails to run. I have tried running using system account on SQL & IIS, creasting an identical account on each box and running that way, still no joy!

SQL box is Server 2000 SP4 & IIS box is Server 2003 SP2..

ANy ideas appreciated..

[NightWalker]

Do your service accounts have the right to 'logon as a batch job'. It’s in group policy, you need to check it’s enabled for the account running the scheduled task on the server the task is set on.

[garyb]

Thanx NightWalker,
Yeh that is already set in local group policy for the required user, still no success. The Task Sceduler returns a status of 4 in the log but not very helpful to diagnose!

When this machine was in the domain it worked fine, not its in its own workgroup it simply wont work, even though it has access to the other servers in other workgroups? Its doing my head in as I keep have to get up in the early hours to run this bloody backup manually!

G

[Sparky]

If you run the backup as a scheduled task but put the backup on the same server does it work ok?

[ThomasMc]

permissions?

[garyb]

Hi,
The batch files run perfectly locally, its only cross domain/workgroup from this server that I have the issues, it worked fine when it was in our office domain..Ob both NTBACKUP & the batch files I am using the SQL servers admin account which has all admoin account rights set correctly but obviously doesnt have NTFS righst on the destination server as no account exists for this server. When I double click the batch file to run successfully what account would it be running under?

Thanx again..

OK, a quick update. On the remote SQL server I have scheduled a XXCOPY to copy only the days SQL backups to a temp directory at 02:00 each day. Then on my workstation on the LAN here I have scheduled an xcopy batch file to copy that remote temp directory to the remote IIS server which works fine.

I know its really messy & I would like to find a simpler method but short term it works & I get some sleep! Must be something to do with the account SQL admi runs under?

[r.h.lee]

Originally Posted by garyb

Hi,
At our DataCentre I have SQL server on the LAN side of the firewall and IIS server on the DMZ side. I have setup the rules to allow traffic from LAN 2 DMZ and 1433 from DMZ to LAN therein safeguarding the data should the IIS box get breached.

I decided to backup the SQL files to the IIS box using NTBACKUP, didnt work so tried normal xcopy batch files again didnt work. However they do work when I run them manually, ie run the batch file from a command prompt or NTBACKUP direct from its console, its only when they are scheduled via task scheduler or NTBACKUPs own scheduler that problems occur with no real indictaion why.

For example if I run "xcopy D:\mydb.bak \\remoteserver\bakupfolder\" it works great. If I schedule this using task scheduler it fails to run. I have tried running using system account on SQL & IIS, creasting an identical account on each box and running that way, still no joy!

SQL box is Server 2000 SP4 & IIS box is Server 2003 SP2..

ANy ideas appreciated..

garyb,

First question off the top of my head is why the heck are you doing a backup of the SQL files, which is on the securest side of the firewall, to the IIS server which is in the LESS secure DMZ?

[garyb]

r.h.lee
Valid point and one I've mulled over for a while now but it came down to 3 options. Either I dont backup at all and hope me RAID5 servers me well, drag 6GB of databases over the WAN back to my office each night or keep on top of the IIS box and simply copy the backup files onto this. I realise its not the best option but I have trust in the Gateway firewall and have disabled all services not required.

In the event of losing SQL data its simply a case of restoring from a local source rather than 6GB over a 2mb pipe!!!

Cheers

G

[Sparky]

Have you tried mapping a drive and connecting with different credentials? Perhaps try that and lock the server (don’t log off) and see if the backup runs ok.

[r.h.lee]

Originally Posted by garyb

r.h.lee
Valid point and one I've mulled over for a while now but it came down to 3 options. Either I dont backup at all and hope me RAID5 servers me well, drag 6GB of databases over the WAN back to my office each night or keep on top of the IIS box and simply copy the backup files onto this. I realise its not the best option but I have trust in the Gateway firewall and have disabled all services not required.

In the event of losing SQL data its simply a case of restoring from a local source rather than 6GB over a 2mb pipe!!!

Cheers

G

garyb,


Did you know that some security attacks come from known ENABLED services? Case in point, using web traffic, which by definition MUST be enabled for the outside world to reach the IIS server, is the method of attack. The "gateway firewall" is NOT going to help defend your network from a web attack that's designed to look like routine web traffic. Once your IIS server is compromised, and the attacker finds the SQL backup on the computer, why even bother trying to crack from the DMZ to the inner network? The more frequently dilligent you are on the backups, is how frequently the attacker may obtain updates to your SQL server's data. If the IIS server is compromised, then your "routine backups" is basically like giving the attacker the data that you're trying to protect by placing the server in the inner network to the attacker on a silver platter.

I have to ask but do you have any disaster recovery procedures in place for fixing the SQL server, restoring data from backup to the SQL server, verify if things are back to normal, and the estimated timeline for the entire process? Compare that overall down time (which translates into money) with the cost in lost revenues from the "system unable to process any transactions due to SQL server failure" then you have to consider a more available option such as either a second SQL server for load balancing and real time fault tolerance. If that solution would outweigh the costs of fixing the SQL server, restoring data from backup to the SQL server, verify if things are back to normal, then maybe have a dedicated backup server in the same network as the SQL server with a RAID 5 array. Budget permitting, maybe have a hot swappable hardware RAID 5 array.

So in other words, I believe that your relative band-aid solution is itself creating another problem that may be worse than the perceived problem of the problem associated with no backup.

[Stoney]

R.H.Lee: Is there a name for this attack that exploits IIS or is it just a generic vulnerability?

[NightWalker]

Originally Posted by r.h.lee

garyb,


Did you know that some security attacks come from known ENABLED services? Case in point, using web traffic, which by definition MUST be enabled for the outside world to reach the IIS server, is the method of attack. The "gateway firewall" is NOT going to help defend your network from a web attack that's designed to look like routine web traffic. Once your IIS server is compromised, and the attacker finds the SQL backup on the computer, why even bother trying to crack from the DMZ to the inner network? The more frequently dilligent you are on the backups, is how frequently the attacker may obtain updates to your SQL server's data. If the IIS server is compromised, then your "routine backups" is basically like giving the attacker the data that you're trying to protect by placing the server in the inner network to the attacker on a silver platter.

I have to ask but do you have any disaster recovery procedures in place for fixing the SQL server, restoring data from backup to the SQL server, verify if things are back to normal, and the estimated timeline for the entire process? Compare that overall down time (which translates into money) with the cost in lost revenues from the "system unable to process any transactions due to SQL server failure" then you have to consider a more available option such as either a second SQL server for load balancing and real time fault tolerance. If that solution would outweigh the costs of fixing the SQL server, restoring data from backup to the SQL server, verify if things are back to normal, then maybe have a dedicated backup server in the same network as the SQL server with a RAID 5 array. Budget permitting, maybe have a hot swappable hardware RAID 5 array.

So in other words, I believe that your relative band-aid solution is itself creating another problem that may be worse than the perceived problem of the problem associated with no backup.

Good post, some good points to consider.

[garyb]

All valid points and much appreciated, budget is a big issue for us as our industry is in turmoil at the moment and although this is important to me it seems the powers above do noty deem it importamnt enough to stretch that but further for "local LAN server" to store backups which is my preferred option. Taken your points onboard though & I have cancelled the plan to backup to DMZ, instead I am using replication software to mirror SQL back to base.

On the hacking point though, even if someone did breeze in on on port 80 what could they do as SQL isnt installed on the DMZ server, obtaining the actual data from the backup files would be difficult wouldnt it?

Cheers

G