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 Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functi...ield-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation
with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

JamesNo, text fields can handle text string up to 2-Gig. The 16 bytes refers to
the space used up by the string pointer inside the record. The problem with
text/ntext is that they're slow, and you can't use them in ORDER BY or GROUP
BY.

The superlame way of retrieving more than 255 chars from a MSSQL varchar is
to retrieve the field in multiple sections, using the SUBSTR() function,
then concatenate them in PHP:

SELECT SUBSTR(msg, 1, 255) AS msg_1, SUBSTR(msg, 256, 255) AS msg_2, ...

$msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

Uzytkownik "James" <jamesstarrittRemovethefollowingtoemailme@.hotmail.c om>
napisal w wiadomosci news:40285e29$1_1@.newspeer2.tds.net...
> I am currently working on a PHP based website that needs to be able to
draw
> from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS.
I
> took a lot of time and care creating a flexible and solid wrapper and am
> deep into coding. The only problem is a noticed VARCHAR fields being
drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to 16
> characters in length, and I am having similar results in terms of
truncation
> with other character based fields that can store more than 255 characters.
> I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
> the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
> What are my options here? Has anybody worked around this or am I missing
> something obvious?
> James|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> The only problem is a noticed VARCHAR fields being drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to
> 16 characters in length, and I am having similar results in terms of
> truncation with other character based fields that can store more than
> 255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

However, text is fairly cumbersome and not really easy to use. I don't
know anything about PHP, but it's apparent that PHP uses DB-Library to
access SQL Server. And while I think this is a very nice API, Microsoft
does not think so, and has not developed DB-Library since the release of
SQL 6.5, which was seven years ago. The next version of SQL Server, slated
for release this year, will accept connections from DB-Library, but will
not come with files needed for development. You may not even get the DB-
Library run-time DLL:s, but have to find them elsewhere.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
> > Note to Win32 Users: Due to a limitation in the underlying API used by
PHP
> > (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> > need to store more data, use a TEXT field instead.
> > (http://www.php.net/manual/en/functi...ield-length.php)
> > The only problem with this advice is Text fields seem to be limited to
> > 16 characters in length, and I am having similar results in terms of
> > truncation with other character based fields that can store more than
> > 255 characters.
> As pointed out by Chung Leong, there is room for 2GB of data in text.
> The 16 bytes you see is just a pointer.
> Thus, there are all reasons to look into alternative means of connections
> for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as it
should have been) -- I'll be testing that later on today.

Retrieving a field in chunks may not be such a big issue, it is rare that
this will happen frequently in the app however the possibility is there on
some 60% of the fields and I do need to be able to Group and Order By on the
majority of these fields. I may just have to not support the MS SQL Server
at this time -- the goal was an app that could run on Linux and Windows
webservers and connect to just about any DB alive - its a shame that one of
the staple db's is so poorly supported by the PHP project given that I am
tied to it. My work is with non-profits and we have to be able to utilize
the licensing they already have so demanding the use of particular software
will only drive the pricing up.

Strangley I had similar issues when working with ASP and SQL Server in the
past - fields would simply not show up sometimes if they where over 255
characters in length and it is a known and documented issue that affects
'certain databases' according to MS but I have only ever seen with the SQL
Server and MSDE - never Oracle, MySQL, Postgre or even Access -- its a shame
I like the SQL Server but it seems that everytime I get contracted to use it
with a web back end I run into problems that make it a royal pain in the ass
to work with.

Thanks for the feedback,

J|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> Strangley I had similar issues when working with ASP and SQL Server in
> the past - fields would simply not show up sometimes if they where over
> 255 characters in length and it is a known and documented issue that
> affects 'certain databases' according to MS but I have only ever seen
> with the SQL Server and MSDE - never Oracle, MySQL, Postgre or even
> Access -- its a shame I like the SQL Server but it seems that everytime
> I get contracted to use it with a web back end I run into problems that
> make it a royal pain in the ass to work with.

I don't know more about ASP than I know about ASP, but I would expect
ASP today have any problems with longer varchar values.

Of course there was a time when SQL Server did not have anything better
than varchar(255) (and text). If you were accessing ASP from a machine
with a version of ODBC that did not support the new and improved types
in SQL7, then you would be in that boat. But that's long ago.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

AdventureWorks Sample Database

Hi guys, I was trying to download the latest version of AdventureWorks database from codeplex website and I got the following error massage during the installation.

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 611 and earlier. A downgrade path is not supported.Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Does anybody has idea on this

Thx

Never mind. I got the answer from another post. Here is the link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2183902&SiteID=1&mode=1

peace

Advance SQL Statement Help

I have all my website access statics logging data into a SQL table with
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:

> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>

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

ADO error creating database

im creating a ecommerce website, and i need to create a database, im using visual studio .net
and when i try to create a new database using VS.net i choose "use sql server authentication" and no matter what i type in for the login id, i get the following ado error

"ADO Error : ' Login failed for user "blank". Reason. Not associated with a trusted SQL server connection.'

thanks in advance.is the sql server on a different machine?|||no its on my machine. im using xp pro. is that ok? or am I trying to do something thats not possible?|||and you're sure you know a valid sql user name and password with decent rights, such as owner?|||just check ur connection string... It must contain clauses: uid=<the_login_to_ur_db>, pwd = <ur_password> and database=<ur_db_name>. If its not (this is really possible... I have encountered smth. like that...) then add it into connection string manualy divided with semicolon of course... It should help.