Use SQL Server to save session state for single sign-on

When we do some application sites, we may encounter a situation where the whole project is composed of multiple sites, and we want to implement a user login from one site, jump to other sites do not need to repeat the login, that is, to achieve single sign-on. There are several techniques for implementing single sign-on at the moment, and this article describes how to use asp.net2.0 and SQL Server for single sign-on. Generally after the user login success, we need to keep the user login success information stored in the session, but the value of the session can only be stored in the user's current access to the site, as long as we achieve the session of the cross-site sharing, Also basically realized that the user in a site after the successful login in other sites do not need to repeat the login, here, we use SQL Server to save the session state, the implementation of multiple sites sharing session.

Development environment: WindowsXP, VS2005,. NET Framework 2.0, SQL Server 2000

First we need to create a separate database to hold the session state, after installing the Framework2.0, we can find a "c:/windows/microsoft.net/framework/v2.0.50727" directory under the " InstallPersistSqlState.sql "SQL file, this file is used to create a save session state of the database, only need to execute in the Query Analyzer, you can, of course, can also use the" UninstallPersistSqlState.sql "This file to uninstall the state database that was created.

"InstallPersistSqlState.sql" After the execution, refresh the Object Browser, we can see a database called "ASPState", there are two tables in the database, "Aspstatetempapplications" is used to store all sites that use SQL Server's saved state, "ASPStateTempSessions" is, of course, used to save all sessions. Such as:

After the database is created, we can create a new test site to see if the session state can be saved to the database, and of course we will need to make some settings for the Web. config file before testing. Open Web. config, create <sessionState> tag under <system.web> tab, Mode property is a description of where the session state is stored, stateful server, memory, database, etc. Here we will select SQL Server. The settings are as follows:

[XHTML]View Plaincopy

1. <system.web>
2. <sessionstate mode="SQL Server" sqlconnectionstring="Data source=127.0.0.1; User Id=sa; password=; " timeout="/> "
3. </system.web>

When testing the session state, it is important to note that if we do not give the session any value in the program, the session state is not saved to the database. Let's just give the session a key value, after running, use Query Analyzer to look at the contents of the two tables in the "ASPState" database, as follows:

Aspstatetempapplications table

ASPStateTempSessions table

The session content that is displayed on the test page:

Here we need to explain AppID and SessionID, first look at the following two images in the SessionID, table aspstatetempsessions sessionid than the page 8 more, is the back of the "64021378", This string is actually the hexadecimal representation of the AppID in the table aspstatetempapplications, and you can see that the SessionID value in the database is a combination of "page Sessionid+appid".

Since we are going to achieve a cross-site sharing session, then we need to create another test Site 2, and follow the above method to save the session state to the database, showing that in the browser access to these two sites, "ASPStateTempSessions" Table saved session information:

These two sessionid the same in front of the same, different place is the value of the back 8 bit identity AppID, in the ASPState library, to determine whether the same user is based on sessionid conditions, although here the user is the same person, but the site of the visit is different, SessionID is not the same. The next thing we want to do is to ignore the SessionID inside the site identity, so that ASPState can be from the same user multiple site access as the same site access, session value can be shared at multiple sites.

In the ASPState library, we need to modify the "tempgetappid" This stored procedure, the inside of the two "WHERE AppName = @appName" comment out. Here, you should be aware that after modifying the stored procedure, you need to restart the ASP.

[C-sharp]View Plaincopy

2. ALTER PROCEDURE dbo. Tempgetappid
4. @appName Tappname,
6. @appId int OUTPUT
8. As
10. SET @appName = LOWER (@appName)
12. SET @appId = NULL
16. SELECT @appId = appId
18. from [Aspstate].dbo. Aspstatetempapplications
20. --where AppName = @appName
24. IF @appId is NULL BEGIN
26. BEGIN TRAN
30. SELECT @appId = appId
32. from [Aspstate].dbo. Aspstatetempapplications with (Tablockx)
34. --where AppName = @appName
38. IF @appId is NULL
40. BEGIN
42. EXEC GetHashCode @appName, @appId OUTPUT
46. INSERT [Aspstate].dbo. Aspstatetempapplications
48. VALUES
50. (@appId, @appName)
54. IF @ @ERROR = 2627
56. BEGIN
58. DECLARE @dupApp Tappname
62. SELECT @dupApp = RTRIM (AppName)
64. from [Aspstate].dbo. Aspstatetempapplications
66. WHERE AppId = @appId
70. RAISERROR (' SQL session ' state fatal Error:hash-code collision between applications '%s ' and '%s '. Rename the 1st application to resolve the problem. ',
72. 1, @appName, @dupApp)
74. END
76. END
80. COMMIT
82. END
86. RETURN 0

OK, now we can test, in a site to assign a value to the session, and then in another site to take a bit, is it possible to remove it, if you can, then congratulations, you have implemented the session sharing, apply to your system bar.

• Previous article Formsauthentication.setauthcookie
• What's the difference between Session.Abandon and session.clear (turn)

Top

0

Step

Use SQL Server to save session state for single sign-on