advice on hosting website safely

Hello,

For one of my clients I have built 2 applications. The 1st one is a windows based application that is used for production and employee scheduling. The data for this application is stored in a SQL Express database. The 2nd one is a ASP.NET 2.0 site that contains the clients website and a portal page. The portal page uses forms authentication and redirects a customer to a page that shows the production schedule for this customer. So I have 2 kind of users: local (LAN) and web. Both using the same database.

For test purposes the site is currently hosted on the clients SBS 2003 server (this is the only server). However I don't think it is wise to host the public website on this server. What is the best configuration to host the public website safely? I read about putting a webserver in a DMZ. But I need a connection with the SQL Express server to retrieve the data for the portal section. As well for authenticating the customer and for retrieving the schedule data.

I'm currently using Windows Authentication on the SQL server. Can this still be used when the webserver is in the DMZ?

If I understand it correctly the webserver in the DMZ is not part of the local domain? Also could remote hosting be an option at all?

Any help and advice would be highly appreciated.

Remote hosting would be the 'safest' option. If the box is hacked, the hacker is NOT in your network. Siting in a DMZ 'could' be relatively safe -if you are certain that there are no 'holes' in the firewall.

The public website (application) may be best using Application Pooling security on the IIS box. You can connect to a SQL Server using an IP address instead of a serverName. Use the IP address and the Port in the connection string.

Since a server in a DMZ is not part of the local domain, you cannot use Windows Authentication on the SQL Server.

I suggest that if you were to visit the forums and blogs at www.ASP.NET, you will find folks with a lot of experience with this particular issue.

Advice on handling a client's full-text index loss

Hi,
The problem started because the client's hardware malfunctioned and he
needed to move things to a new computer. So, we installed SQL 2000 on the
new computer, ran the SP3 updates, and installed the specific initial
database using our installer.
After that, we restored the database to the data that they had backed up
a while ago. Everything looked good and they ran without full-text searches
for a while. They then decided that they were ready to do the full-text
index populations, so we ran a script designed to get that all squared away.
The problem apparently started when this script attempted to drop the
full-text indexes and relocate them in the appropriate place. I thought I
could work through this, but couldn't find appropriate information for quite
a while. Later, I decided to attempt a manual removal of the full-text
indexes from the sysfulltextindex (sp?) table. There were two rows, with
the first columns having the values of 5 and 6. I removed them, and tried
to clean things up. In that process, I disabled the full-text indexing, and
now it simply will not re-enable.
Reading this newsgroup, I noticed the existence of a script that could
possibly help by manually deleting the traces of the full-text catalogs.
My question is: What is the best next step?
1) Try to make use of the script I just mentioned.
2) Restore the database, copy in the previous full-text data files
to the appropriate directory, and then try to run our script that moves and
re-populates the catalogs?
3) Something else entirely?
Thanks for your help.
John
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 8/24/2004
John,
I've emailed you charter account directly as resolving this type of a
situation is better handled via direct email. I don't publicly post these
scripts because they require SysAdmin permission level of access and modify
both Registry keys & values as well as SQL Server system tables.
Regards,
John
"John" <jsfishr@.charter.net> wrote in message
news:eMb$LO6jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The problem started because the client's hardware malfunctioned and he
> needed to move things to a new computer. So, we installed SQL 2000 on the
> new computer, ran the SP3 updates, and installed the specific initial
> database using our installer.
> After that, we restored the database to the data that they had backed
up
> a while ago. Everything looked good and they ran without full-text
searches
> for a while. They then decided that they were ready to do the full-text
> index populations, so we ran a script designed to get that all squared
away.
> The problem apparently started when this script attempted to drop the
> full-text indexes and relocate them in the appropriate place. I thought I
> could work through this, but couldn't find appropriate information for
quite
> a while. Later, I decided to attempt a manual removal of the full-text
> indexes from the sysfulltextindex (sp?) table. There were two rows, with
> the first columns having the values of 5 and 6. I removed them, and
tried
> to clean things up. In that process, I disabled the full-text indexing,
and
> now it simply will not re-enable.
> Reading this newsgroup, I noticed the existence of a script that could
> possibly help by manually deleting the traces of the full-text catalogs.
> My question is: What is the best next step?
> 1) Try to make use of the script I just mentioned.
> 2) Restore the database, copy in the previous full-text data files
> to the appropriate directory, and then try to run our script that moves
and
> re-populates the catalogs?
> 3) Something else entirely?
> Thanks for your help.
> John
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.744 / Virus Database: 496 - Release Date: 8/24/2004
>

Adobe Reader could not open ...

On a weekly basis I have over 100 subscription reports in pdf format emailed
to clients.
We are using SQL server 2000 and SQL Reporting Services 2000.
Several of the clients are reporting that they are unable to open their
report.
They get "Adobe reader could not open ReportName because it is either not a
supported file type or the file is corrupted... etc"
No custom code involved, all reports are generated in the same manner and
deployed in the same manner.
In some organizations several clients receive the same report and only one
or two of the clients reports a problem.
All these clients are using the same version of Adobe reader.
Is this a reader problem or a Report Server issue?
I am having a problem narrowing it down to the reader or generator!We are having the same issue. It was working for a while and now, user by
user, we are running into the same problem. Does anyone have any suggestions?
"Doug Gifford" wrote:
> On a weekly basis I have over 100 subscription reports in pdf format emailed
> to clients.
> We are using SQL server 2000 and SQL Reporting Services 2000.
> Several of the clients are reporting that they are unable to open their
> report.
> They get "Adobe reader could not open ReportName because it is either not a
> supported file type or the file is corrupted... etc"
> No custom code involved, all reports are generated in the same manner and
> deployed in the same manner.
> In some organizations several clients receive the same report and only one
> or two of the clients reports a problem.
> All these clients are using the same version of Adobe reader.
> Is this a reader problem or a Report Server issue?
> I am having a problem narrowing it down to the reader or generator!
>

ADO.NET parameterized query security

I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.

Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?

From your description, you are relying on ownership chaining to access tables only from SPs; this is possible only because the SP owner is the same as the underlying tables and the security checks are bypassed; but sqlcommand will not be able to use any chaining directly.

In any case, we would like to understand your needs in order to give better advice as well as to understand our customer needs. For example, is the reason behind not granting permissions directly on the table to protect from ad-hoc queries, or to help in managing permissions? If it is for managing permissions, what may be the obstacle from using the existing permission model?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

You don′t need to grant permissions on the tables as long as the owner of the tables are the same as those for the stored procedure. This is called owner ship chaining, there is more to read about that in the BOL. Don′t breaking the ownership chain means that the permission is checked once at the procedure level. SQL Server assumes then lateron during the access of the table that if the grantee has access to the proc and the grantor created the stored procedure accessing the base tables th grantor also wanted the grantee to access the base tables. Permissions are not checked twice then. If the ownership chain is broken (Another owner of the base tables than the stored procedure) permissions are checked for every underlying base object. Using SQL Server 2005 you can also use impersonation within your stored procedures, acessing data / base tables using the WiTH EXECUTE AS syntax.

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thank you all for your quick response and comments!

The reason to avoid granting access directly on the tables is to protect from ad-hoc queries, if an unauthorized user gains access through the login used by the website. To minimize the damage if that were to happen, we want this login to have only minimal rights to the database.

From a wider perspective, I normally prefer using stored procedures, but sometimes we need the flexibility of building a parameterized query in the Web application. I would like to find a way of doing that without giving up the security advantage of using stored procedures. What is the best way to accomplish this?

|||

I would recommend following Jens’ suggestion and use either EXECUTE AS (or digital signatures) in order to change the execution context before accessing the tables. Here are a few good starting points in BOL for this topic:

· Context switching (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

· Module signing (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

If you decide to use this mechanism, I would also like to strongly recommend following the least privilege principle. For example, if for this application the application (impersonated) context only needs to have SELECT on a couple of tables, make sure that the permissions are limited only to the proper tables.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine