How to authenticate user and make access to sql database

[Marc Eggenberger]

Hi there ..

I have the following scenario.

I have a Webservice which is running under Win2003/IIS6 with .Net1.1 The
Service itselfs connects to a database which is a SQL 2000 on a Server
in the same domain.

Client is a Windows Form Client (no ASP.NET yet) which connects to the
WebService. Before WebService I would make a SQL Server connection with
the Username & Password specified in the Client and then in the stored
procedure I could get the actual User and limit my sql statements to
this user ...

for example

select jobname from jobs where owner = current_user

It didnt use Windows Authentication so I could change the credentials in
the client without logging into Windows with a different user ...

I though of a similiar approach for the Webservice ...

But here not the client is creating the SQL Server connection. So I
would have to pass the Username & password each time when accessing a
Webservice function?

If I use authentication in IIS (with removing the anonymous access) I
must have the user creating in the Windows Domain, right? But I only
want them in the SQL Server.

How would you solve this? Client which access Webservice which then
access the SQL Database.
--
mfg
Marc Eggenberger

[Dan Rogers]

Hi Marc,

It sounds like you need to do the same thing you used to do from your rich
client applications ... that is pass the credentials to the web service
rather than trying to pick them up from IIS. If you enable SQL
authentication (or both) on your SQL server, then you could take the
credentials passed by the caller, use these to connect to the database, and
then that connection would have a "current user" that meets your
expectations.

I hope this helps

Dan Rogers
Microsoft Corporation
--------------------
From: Marc Eggenberger <ma**************@remove.itc.alstom.com>
Newsgroups: microsoft.public.dotnet.framework.webservices
Subject: How to authenticate user and make access to sql database
Date: Tue, 7 Dec 2004 14:02:19 +0100
Organization: *
Lines: 38
Message-ID: <MP************************@iww.cacti.ch.alstom.co m>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
X-Trace: individual.net EDsyjQX3Ylden+Jgon+shgwH4MEpA3U1bOlvs81VC24lfdVNz8
X-Orig-Path: h02374
X-Newsreader: MicroPlanet Gravity v2.60
X-Original-NNTP-Posting-Host: cwbad10019.ch.power.alstom.com
X-Original-Trace: 7 Dec 2004 13:01:47 +0100, cwbad10019.ch.power.alstom.com
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news-lei1.dfn.de!news-ber1.dfn.de!fu-berlin.de!un
i-berlin.de!individual.net!not-for-mail
Xref: cpmsftngxa10.phx.gbl
microsoft.public.dotnet.framework.webservices:7945
X-Tomcat-NG: microsoft.public.dotnet.framework.webservices

Hi there ..

I have the following scenario.

I have a Webservice which is running under Win2003/IIS6 with .Net1.1 The
Service itselfs connects to a database which is a SQL 2000 on a Server
in the same domain.

Client is a Windows Form Client (no ASP.NET yet) which connects to the
WebService. Before WebService I would make a SQL Server connection with
the Username & Password specified in the Client and then in the stored
procedure I could get the actual User and limit my sql statements to
this user ...

for example

select jobname from jobs where owner = current_user

It didnt use Windows Authentication so I could change the credentials in
the client without logging into Windows with a different user ...

I though of a similiar approach for the Webservice ...

But here not the client is creating the SQL Server connection. So I
would have to pass the Username & password each time when accessing a
Webservice function?

If I use authentication in IIS (with removing the anonymous access) I
must have the user creating in the Windows Domain, right? But I only
want them in the SQL Server.

How would you solve this? Client which access Webservice which then
access the SQL Database.
--
mfg
Marc Eggenberger