SQL connection with custom authentication

[Kirsten]

I'm trying to implement custom authentication in ASP.NET and then use this
user for making the sql connection (SQL Authentication, not Integrated
security).

When the user logins, I have the sql_username and sql_password stored in the
Session variable.
My question are:

- When should I set the connection string?
- What about connection pooling?

Thanks a lot!

 

[Kirsten]

How many different / concurrent users will there be?
30-50

Are you intending to have a different connection string for each user?

The connection string is the same, it defers in the
"user=xxxx;password=xxxx"

AAMOI, once the user has successfully logged in, why do you need to
persist their password as a Session variable...?

For building the sql connection string.


What I really really need is sql server table level auditing, but I can't
use Windows Authentication for each user. As far as I know, all audit
triggers use the SYSTEM_USER or CURRENT_USER to record changes.

Suppose that I use a single windows identity to connect to sql server (and
administrator id for example): Is there any way to instruct SQL Server to
audit with the asp.net user? Maybe using impersonation (EXECUTE AS LOGIN =
'mySQLrelateduser')

Thanks a lot!

so, this is extremely inefficient especially in ASP.NET apps as you lose
all of the connection pooling performance advantages.

 

[Gen.Dhruva]

I'm trying to implement custom authentication in ASP.NET and then use
this user for making the sql connection (SQL Authentication, not
Integrated security).

When the user logins, I have the sql_username and sql_password stored in
the Session variable.
My question are:

- When should I set the connection string?

Depends on how you are using the SQL Connection Object. Ideally Set the
Connection Object's Connection String in the constructor for connection
object.

- What about connection pooling?

In Your case seems that you want to specify the Database username and
password in the connection string which will be seperate for every user.
ADO.net maintains seperate connection pools for seperate connection string.
For More information about connection pooling see this article.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Gen.Dhruva

 

[Manu Singhal]

I'm trying to implement custom authentication in ASP.NET and then use
this user for making the sql connection (SQL Authentication, not
Integrated security).

When the user logins, I have the sql_username and sql_password stored in
the Session variable.
My question are:

- When should I set the connection string?

Depends on how you are using the SQL Connection Object. Ideally Set the
Connection Object's Connection String in the constructor for connection
object.

- What about connection pooling?

In Your case seems that you want to specify the Database username and
password in the connection string which will be seperate for every user.
ADO.net maintains seperate connection pools for seperate connection string.
For More information about connection pooling see this article.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

 

[Kirsten]

Mark:

Thanks a lot. CONTEXT_INFO is very interesing. I'm trying to follow this
article:
http://www.apexsql.com/training/2007/08/how-to-audit-user-names-in-aspnet_428.htm

The fact is that it's possible to handle CONTENT_INFO if I handle
connections, but what about using visual Datasets? Howdo I know when a table
adapter is opening a new connection? (maybe some event OnConnectionOpen for
something like that).
By now, all I see as a solution is to create a middle layer (bussiness logic
layer) and force opening the connection like this:

MyBLL{

public MyDataTable GetAllProducts()
{
Adapter.Connection.Open();
// set context info
....
return Adapter.GetData();
}

}

Thanks again for all your support!

 

[Fred]

in Kirsten wrote :

The fact is that it's possible to handle CONTENT_INFO if I handle
connections, but what about using visual Datasets? Howdo I know when
a table adapter is opening a new connection? (maybe some event
OnConnectionOpen for something like that).

To avoid this, I use the «Application Name» key of the connection
string.
In my case, I simply store the user name in this key (and a personal
prefix).
In the database, I wrote a function that return this user name using the
SQL function APP_NAME().

I can also simulate a custom user name when working with Management
Studio. When APP_NAME returns a string other than the expected one, I
use SYSTEM_USER and a login mapping table to find a corresponding custom
user name.

 

[Kirsten]

I'll try this too.

Mercy beaucoup!

in Kirsten wrote :




To avoid this, I use the «Application Name» key of the connection string.
In my case, I simply store the user name in this key (and a personal
prefix).
In the database, I wrote a function that return this user name using the
SQL function APP_NAME().

I can also simulate a custom user name when working with Management
Studio. When APP_NAME returns a string other than the expected one, I use
SYSTEM_USER and a login mapping table to find a corresponding custom user
name.

 

[Cowboy \(Gregory A. Beamer\)]

I'm trying to implement custom authentication in ASP.NET and then use this
user for making the sql connection (SQL Authentication, not Integrated
security).

When the user logins, I have the sql_username and sql_password stored in
the Session variable.
My question are:

- When should I set the connection string?
- What about connection pooling?

Back up a second. I have read most of the thread and I think you are asking
one thing and wanting something a bit bigger.

The first thing I would suggest is looking up creating a custom Membership
Provider to fit your schema. It is much less taxing than creating some type
of completely custom authentication method along with the plumbing that goes
along with it. Here is a good starting point:
http://msdn.microsoft.com/en-us/library/f1kyba5e.aspx

There is really no reason to code all the plumbing yourself.

As for the auditing question, you might be better implementing your own
auditing. One way to do this is to set triggers on the audited tables and
put the table name, ID and user information, using the user's GUID (or other
id data type, if you customize this). This will give you row level auditing
on the table, if that is your primary need. You can pass the user's guid to
insert in the table having a "last edited" field and last edited date. The
auditing table will have any other information you need.

In addition, there is no real need to keep the user's id in session, as the
server cookie will hold enough info for you to grab this information.

In general, I would not mess with the connection pooling unless you find you
need to. The default offers you plenty in most applications. Only alter if
you find there are problems.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

********************************************************
| Think outside the box! |
********************************************************

 

[Kirsten]

The first thing I would suggest is looking up creating a custom Membership

Provider to fit your schema. It is much less taxing than creating some
type of completely custom authentication method along with the plumbing
that goes along with it. Here is a good starting point:
http://msdn.microsoft.com/en-us/library/f1kyba5e.aspx

I'll read about it.

As for the auditing question, you might be better implementing your own
auditing. One way to do this is to set triggers on the audited tables and

I already have this. But as I said before, it uses SYSTEM_USER SQL Server's
function and not the asp.net logged in user.

In general, I would not mess with the connection pooling unless you find
you need to. The default offers you plenty in most applications. Only
alter if you find there are problems.

I'm worried because I don't want USER_A to connect, set the CONTEXT_INFO (or
whetever method of passing his/her user id), then USER_B connect using the
same connection (idle in the connection pool) and SQL Server log USER_B
operations as USER_A.

Thanks a lot for your time,
Juan

 

[Paul]

Why not hold your custom Identity in cache and provide the username with
each interaction with the DB (SP parameter). Create an audit Function that
can be called each time you need to audit some changes.

Changing the connection string each time will screw up connection pooling.