Question on how to manage SQL Server connection string

[paulo]

Hello,

I'm developing a web services apllication using SQL Server in mixed mode.
I'm also using WSE 3.0.
For each user in the application there's a corresponding user on the
database. The client always sends the username to the web service for
identification.
I have a connection string stored in the web service for the visual
designed
datasets and for the connection I make manually.
I need that the connection string includes the user information for each
user connecting to the web service, so that when I receive a request
from the
client I can change the connection string for everything done under that
request, including using tableadapters to fill datasets, etc..
What is the best approach to make the web service use the information
received from the client to build a valid connection string that can be
used
only for that specific request, but that is visible to everything in the
web
service, including datasets built using the visual editor?
Thanks in advance for any help given.

Regards,
paulo

[Steven Cheng[MSFT]]

Hi paulo,

Welcome to the MSDN newsgroup.

From your description, I understand you have an ASP.NET 2.0 webservice
which use WSE 3.0 to secure the communication between client and get the
full credential (username/password) from client user. Also, since you'll
need to make connection to backend sqlserver database which accept SQL
Server authentication mode, you're wondering how to dynamiccally make the
connection(in Tableadapter) use the username/password from the webservice
client user's authenticated credential, correct?

Based on my experience, if you've corrected get the clear text
username/password from client authenticated user in webservice, what we
need to do is how to dynamically assign these credential to the Data
connection's connection string (the Tableadapter). I'm not sure whether
you're currently creating the Tableadaper in your ASP.NET project or in a
separate class library project. IMO, I'd prefer put it in a separate class
library project because that'll provide us more options to customize the
TableAdapter(include the connectionstring...). Here are some options you
can consider:

1. Since in winform or class library project, the DataSet/tableAdatper's
designer generated code is available to us in the xxxx.designer.cs file, we
can manually modify some of them or add our own function to customize the
connection string or the sqlconnection member to use the credentials in our
webservice. (e.g extract the main part from the orginal connection string
and concatenate it with the usename/password from webservice client).

2. If you do not want to modify those designer generated code(since they'll
be regenerated), the Tableadapter has a property named
"Connection--Modifier"(you can find it in the TableAdapter's property
grid), we can set this to "Public" so that we can create our own
SqlConnection instance and assign it to the TableAdapter at runtime, this
is also one possible approach.

Hope these help.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[paulo]

Hello Steven,

Just to thank you for your reply. Using your advice I've found the
solution to the problem.
Thanks!

Regards,
paulo

Steven Cheng[MSFT] wrote:

Hi paulo,

Welcome to the MSDN newsgroup.

From your description, I understand you have an ASP.NET 2.0 webservice
which use WSE 3.0 to secure the communication between client and get the
full credential (username/password) from client user. Also, since you'll
need to make connection to backend sqlserver database which accept SQL
Server authentication mode, you're wondering how to dynamiccally make the
connection(in Tableadapter) use the username/password from the webservice
client user's authenticated credential, correct?

Based on my experience, if you've corrected get the clear text
username/password from client authenticated user in webservice, what we
need to do is how to dynamically assign these credential to the Data
connection's connection string (the Tableadapter). I'm not sure whether
you're currently creating the Tableadaper in your ASP.NET project or in a
separate class library project. IMO, I'd prefer put it in a separate class
library project because that'll provide us more options to customize the
TableAdapter(include the connectionstring...). Here are some options you
can consider:

1. Since in winform or class library project, the DataSet/tableAdatper's
designer generated code is available to us in the xxxx.designer.cs file, we
can manually modify some of them or add our own function to customize the
connection string or the sqlconnection member to use the credentials in our
webservice. (e.g extract the main part from the orginal connection string
and concatenate it with the usename/password from webservice client).

2. If you do not want to modify those designer generated code(since they'll
be regenerated), the Tableadapter has a property named
"Connection--Modifier"(you can find it in the TableAdapter's property
grid), we can set this to "Public" so that we can create our own
SqlConnection instance and assign it to the TableAdapter at runtime, this
is also one possible approach.

Hope these help.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Thanks for your followup Paulo,

Glad that my suggestion is of assistance.

Best Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)