Passwordless connection string to SQL Server?

[Simon]

Hi,

Crossing over from the Unix world, I am quite stumped at this: how can
I specify a DB connection string to SQL Server without 1) putting
username/password into the string, and 2) without giving the user the
right to access the database directly?

Looks like there are two ways, basically, to specify a connection
string, either with ODBC or OLEDB: the username and password are
either included into the string itself, or the Windows crendentials
are used to let the browser access the page, and then *the same
credentials* are passed to the SQL Server. But what if I don't want
this user to be able to access the database directly, but only through
the web page?

In Unix, I could do two other things - 1)make the web page readable to
the web server, but not to the user who logs in, so that he could
never look at the file itself. Does not work in Windows, as the file
is by definition readable by the user via file sharing if it's
executable by him through the web. Or 2) hide the connection string in
the ODBC definition and have a *different* account access the
database.

Or am I missing something?

Thanks,
Simon

[Aaron Bertrand [MVP]]

You could set the password to the IUSR_ account yourself, and use windows
authentication to connect to the database... your end users won't be able to
log on as IUSR_machinename and connect. However, if they can create an ASP
page on the file system, they can easily write SQL statements like "TRUNCATE
TABLE x" or "SELECT username, password, salary FROM users"

(Note that executing a file through IIS is not the same as reading/writing
the file in the file system.)





"Simon" <simonf@simonf.com> wrote in message
news:3226c4d3.0308111306.2b9ef850@posting.google.c om...

> Hi,
>
> Crossing over from the Unix world, I am quite stumped at this: how can
> I specify a DB connection string to SQL Server without 1) putting
> username/password into the string, and 2) without giving the user the
> right to access the database directly?
>
> Looks like there are two ways, basically, to specify a connection
> string, either with ODBC or OLEDB: the username and password are
> either included into the string itself, or the Windows crendentials
> are used to let the browser access the page, and then *the same
> credentials* are passed to the SQL Server. But what if I don't want
> this user to be able to access the database directly, but only through
> the web page?
>
> In Unix, I could do two other things - 1)make the web page readable to
> the web server, but not to the user who logs in, so that he could
> never look at the file itself. Does not work in Windows, as the file
> is by definition readable by the user via file sharing if it's
> executable by him through the web. Or 2) hide the connection string in
> the ODBC definition and have a *different* account access the
> database.
>
> Or am I missing something?
>
> Thanks,
> Simon

[Jeff Cochran]

You could also embed the connection in a COM object, use the
global.asa, or just put the username/password combo in the string,
since it's not actually sent to the end user. Stored Procedures,
Views, App Roles and SQL permissions for the connecting account can
all define security restrictions further.

Jeff

On Mon, 11 Aug 2003 18:16:36 -0400, "Aaron Bertrand [MVP]"
<aaron@TRASHaspfaq.com> wrote:

>You could set the password to the IUSR_ account yourself, and use windows
>authentication to connect to the database... your end users won't be able to
>log on as IUSR_machinename and connect. However, if they can create an ASP
>page on the file system, they can easily write SQL statements like "TRUNCATE
>TABLE x" or "SELECT username, password, salary FROM users"
>
>(Note that executing a file through IIS is not the same as reading/writing
>the file in the file system.)
>
>
>
>
>
>"Simon" <simonf@simonf.com> wrote in message
>news:3226c4d3.0308111306.2b9ef850@posting.google. com...

>> Hi,
>>
>> Crossing over from the Unix world, I am quite stumped at this: how can
>> I specify a DB connection string to SQL Server without 1) putting
>> username/password into the string, and 2) without giving the user the
>> right to access the database directly?
>>
>> Looks like there are two ways, basically, to specify a connection
>> string, either with ODBC or OLEDB: the username and password are
>> either included into the string itself, or the Windows crendentials
>> are used to let the browser access the page, and then *the same
>> credentials* are passed to the SQL Server. But what if I don't want
>> this user to be able to access the database directly, but only through
>> the web page?
>>
>> In Unix, I could do two other things - 1)make the web page readable to
>> the web server, but not to the user who logs in, so that he could
>> never look at the file itself. Does not work in Windows, as the file
>> is by definition readable by the user via file sharing if it's
>> executable by him through the web. Or 2) hide the connection string in
>> the ODBC definition and have a *different* account access the
>> database.
>>
>> Or am I missing something?
>>
>> Thanks,
>> Simon

>

[Manohar Kamath [MVP]]

Simon,

You could potentially use a COM+, dummy object, that takes queries from ASP
page and merely executes them on the database server. You could run this
COM+ object under a certain user (impersonate, basically), and use a trusted
connection to SQL server. That way, you:

1. Hide password to SQL Server (in fact, you don't use it anywhere)
2. Execute statements on the web user's behalf

You can get very creative in terms of security.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Simon" <simonf@simonf.com> wrote in message
news:3226c4d3.0308111306.2b9ef850@posting.google.c om...

> Hi,
>
> Crossing over from the Unix world, I am quite stumped at this: how can
> I specify a DB connection string to SQL Server without 1) putting
> username/password into the string, and 2) without giving the user the
> right to access the database directly?
>
> Looks like there are two ways, basically, to specify a connection
> string, either with ODBC or OLEDB: the username and password are
> either included into the string itself, or the Windows crendentials
> are used to let the browser access the page, and then *the same
> credentials* are passed to the SQL Server. But what if I don't want
> this user to be able to access the database directly, but only through
> the web page?
>
> In Unix, I could do two other things - 1)make the web page readable to
> the web server, but not to the user who logs in, so that he could
> never look at the file itself. Does not work in Windows, as the file
> is by definition readable by the user via file sharing if it's
> executable by him through the web. Or 2) hide the connection string in
> the ODBC definition and have a *different* account access the
> database.
>
> Or am I missing something?
>
> Thanks,
> Simon