Custom authentication

[acent]

Greetings y'all,

Before anyone else says it, I will. I am a noob at asp.net. This should be the simplest of topics, but it has got me lost for the last week or two.

I'm prototyping a custom login site. I am using ASP.net 2.0, and SQLServer 2005. In the SQLServer, I have some stored procedures to save (sp_insertCredential) usernames and passwords encrypted and the get procedure (sp_getCredential) to return an id if the correct username and password are given. All is simple enough - the SQLServer is doing it's job fine.

The 64 gazillion dollar question of the day is, how to I get my asp website to utilize these procedures and maintain the session?

I have experimented with the asp:sqldatasource to use the procedures and then populate a field with the ID just fine, but what about starting the session and storing that id value? After googling, I have found some topics which advocate 20some lines of VB or C#, but have come up short on ASP.NET.

A point in the right direction would be great!

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[ca8msm]

There's several things that you should do to get this working (properly):

1. Avoid the sqldatasource control, it makes life difficult when it comes to debugging. Instead, create a data layer where you write the database access code.

2. Start by reading this as there are a few ways to implement session state and you should use whatever is best for you.

3. Avoid using sp_ as the prefix to your procedures. This will slow down sql server as it will first check the master database to see if they exist there before checking your own database.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[NoCoolHandle]

Mark.
re sp_
It is a good idea not to name procs with an sp_ for the simple reason being this is a prefix for a "system proc", ie. for admin type purposes and having user-defined procs with that name can be confusing.

Realistically there is no performance hit! I had to do some testing to prove this as stuff I had done in the past tended to indicate that your statement might not be correct.

To test this I opened profiler and added 2 counters. 1 for proc:cacheinsert and one for proc:cachemiss.

Local proc only hit local db. master sp_ hit the local db first and then went to master. SO the behavior is the opposite of what you described and the only issue is ambiguity.

for what it is worth

Rob

 

[ca8msm]

What I observed (this is on a SQL Server 2000 box) is that a "sp_" underscore procedure will generate a SP:CacheMiss. This indicates that an "sp_" procedure is causing SQL Server to look for a compile plan in the master db for that procedure, not find one and then go to the relevant db to check there. So, I'd have to disagree with you there based on my observations.

I think that either way, even if the timing is negligible, it's probably best practice for the user to get into the habit of avoiding that prefix anyway.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[ca8msm]

Oh, and Microsoft have this to say on the subject:

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

1. The stored procedure in the master database.

2. The stored procedure based on any qualifiers provided (database name or owner).

3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[NoCoolHandle]

My tests were SQL 2005 build 9.0.1406

I tend to not believe everything I read from MS. I have caught to many errors over the years, and seen 3 different documents all disagree with each other. To get my results I cleared everything ... (stoped and restarted the server.)

I am happy to be wrong, but the tests tended to showed the opposite behaviour to what you described and the book said.

(If you have a different build I would love to know the results - also what version of the server and build.)

The other interesting thing I noticed was I/O. For sp in the master the reads where nearly 4x greater.

Rob
PS I also agree the performance hit is almost not an issue and can't be counted in milliseconds. But

 

[NoCoolHandle]

Results ..... (both procs return a string of the same length)
And I was wrong.. The sp in master too nearly 40x as long.. 41,000 milliseconds as apposed to 1,200 milliseconds.
Always intersting when you test somehting.

EventClass,duration,reads,writes,databaseid,databasename,textdata
Trace Start, NULL, NULL, NULL, NULL, NULL, NULL
SP:CacheMiss, NULL, NULL, NULL, 12, NULL, NULL
SP:CacheInsert, NULL, NULL, NULL, 1, master, NULL
SQL:BatchComplted,41393, 47, 0,NULL, NULL, sp_masterproc
SP:CacheMiss, NULL, NULL, NULL, 12, NULL, NULL
SP:CacheInsert, NULL, NULL, NULL, 12, junk, NULL
SQL:BatchComplted,1202, 44, 0, NULL, NULL, sp_localproc
Trace Stop, NULL, NULL, NULL, NULL, NULL, NULL

 

[ca8msm]

I was using:

Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

and I'm using this simple test which always results in one extra SP:CacheMiss for the sp_ named procedure:

USE tempdb
GO
CREATE PROCEDURE dbo.Select1 AS SELECT 1
GO
CREATE PROCEDURE dbo.sp_Select1 AS SELECT 1
GO

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[NoCoolHandle]

Interesting..

I wonder what the behaviour on SQL 2008 is. (damn now i need to download the demo)
as a side note. One SQL MVP mentioned that he had created a sp_who in a user db. when he dropped it it dropped the one in the master also.

Only happend on SQL 2000
(this was from the MS instructors forum for SQL from earlier today)

Also there was no clear concensus there either.

That forced me to run as many tests as I could think of.