Using SQL Server authentication with ASP.NET forms authentication

Question

How would I from asp.net validate a SQL Server 2005/8 db login and if that is valid sign the user in to the application?

I would also like to use this username and password in the connection string if possible. I can think of a way to achieve this with string.replace on UID and PWD but that's hardly ideal.

I am not interested in windows authentication since that isn't valid here. Pure sql server logins. I already have a way of doing that with using the windows api's.

Also not interested in the membership providers.

So if I understand correctly, the authentication is based entirely on whether the username/password is valid to access the SQL database? — mellamokb
– mellamokb, Commented Apr 3, 2012 at 13:26
yup Jeeves already has a build in user authentication system so might as well use that than to fiddle with something different. — mhenrixon
– mhenrixon, Commented Apr 3, 2012 at 19:06

JotaBe · Accepted Answer · 2012-04-03 14:43:15Z

2

I'd recommend to create a SqlConnection object with provided user and password, and check if the connection is succesful. After that, close the connection inmediately, and use "the standard connection" for all functionalities of your app. This way your app will benefit from connection pooling.

The user's connection string should not allow connection pooling, so that it's truly closed, and not sent back to the app pool for possible reuse.

Simply add Pooling=false to theconnection string. This avoid it from pooling.

See this: Connection pooling

Pooling is specially important in web scenarios, where connections are opende and closed continuously. It's used by ADO.NET by default.

edited Apr 3, 2012 at 14:43

answered Apr 3, 2012 at 14:37

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mhenrixon Over a year ago

Thank you for the detailed explanation. No real need for connection pooling since there will be maximum 10 users at the same time and the pooling is not going to be an issue from those windows CE devices. What I did like however was to simply test the connection at login and based on that create the cookie etc.

JotaBe Over a year ago

In fact what I want to explain is that if you open a connection to check the user credentials, unless you disable Pooling, the connection will keep open. In this case is not very relevant because there are only 10 users, but that wasn't on the original post. Anyway I think it's worth adding the pooling = fals to the connection string.

mhenrixon Over a year ago

You are right in that I should be using the pooling. There are other users to the database from the ERP system through both thin and rich clients and keeping a connection open is best avoided.

Diego · Accepted Answer · 2012-04-03 13:30:50Z

1

you can simply ask for user name and password on your login page and embed them on your SQL connection string

answered Apr 3, 2012 at 13:30

Diego

36.5k21 gold badges94 silver badges138 bronze badges

3 Comments

JotaBe Over a year ago

But idea: that would cretae a different set of connections for each user, defeating the ADO.NET connection pooling.

Diego Over a year ago

but that's his requirement. If you want different users, the pooling will behave differently, of course

JotaBe Over a year ago

Look at my answer. I propose checking by opening and inmediately closing a SqlConnection with the user's credentials, and ppoling disabled. If that's succcesful the user is logged in, but starts using the regular connection on all the subsequent DB operations. This way all users have the same connection and share the pool.

Collectives™ on Stack Overflow

Using SQL Server authentication with ASP.NET forms authentication

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related