The Server Error in ‘/’ Application will often render your SQL server unusable for the session if ASP.NET is not installed. It will display a message- ” Unable to use SQL Server because either ASP.NET version 2.0 Session State is not installed on the SQL server, or ASP.NET does not have permission to run the dbo.”
It will probably look like this:
This issue can sometimes occur even when you have an advanced ASP.NET version like 4.0. There are a few reasons this error can occur and, we have discussed how to resolve them.
Session Storing in Database
1. Restart the Wizard.
2. Choose the Remove option to remove all those tables from the database.
As you are probably using the aspnet_regsql.exe application, it starts a Wizard and then goes on to add various aspnet_* tables in your database tables.
3. Copy and Run the given command:
aspnet_regsql.exe -ssadd -d -sstype c -S -U -P
It will add the tables ASPStateTempApplications and ASPStateTempSessions to your database.
Moving on, you have to modify your web.config file.
4. Copy this configuration:
<sessionState mode=”SQLServer” allowCustomSqlDatabase=”true” sqlConnectionString=”Data Source=Server;Initial Catalog=Database;User ID=UserId;Password=Password” cookieless=”false” timeout=”20″ />
Note: This solution works only when a user wants to store the session within its applications database. On the other hand, you can run this command without the “-d” parameter. That is if you want to maintain the session database separately. The server will then create a new ASPState database having two tables, as mentioned earlier in the article. Finally, you can specify the name of this new database in your configuration.
Stored Procedures for ASP.NET Session State Mode Error of SQL Server
When your server session expires automatically within a few minutes, you may have to reconfigure the procedures.
You have to properly set up the stored procedures for the session to work like before.
Run this code:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe -S . -E -ssadd -sstype p
The -ssadd option will add the necessary support for the session state of SQL Server mode.
Roles Configuration Issue
The server error in the ‘/’ application may occur after the user successfully installs the ASP.NET Session State and later tries to lock down the permissions of the SQL Server.
You need to carefully configure the new roles and then assign permissions to them.
Here’s a code that may help you with it:
CREATE FUNCTION [dbo].[fnGenerateNewTDBWebUserRole] ( @rolename SYSNAME ) RETURNS @ReturnTable TABLE ( ID INT IDENTITY(1,1), FieldValue NVARCHAR(4000) ) AS BEGIN DECLARE @bUserTables BIT DECLARE @bTVF BIT — TABLE VALUED FUNCTIONS DECLARE @Execute NVARCHAR(4000) INSERT INTO @ReturnTable SELECT ‘DROP ROLE ‘ + @rolename INSERT INTO @ReturnTable SELECT ‘GO’ INSERT INTO @ReturnTable SELECT ‘CREATE ROLE ‘ + @rolename INSERT INTO @ReturnTable SELECT ‘GO’ — Database statement permissions INSERT INTO @ReturnTable SELECT ‘DENY CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG TO ‘ + @rolename — Try to lock off the systables –INSERT INTO @TempTable –SELECT ‘DENY SELECT, VIEW DEFINITION, UPDATE ON sys.’ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.sys_objects –WHERE type = ‘S’ — USER TABLES AND INLINE FUNCTIONS INSERT INTO @ReturnTable SELECT ‘DENY ALTER, CONTROL, DELETE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE (type = ‘U’ OR type = ‘IF’) AND name NOT LIKE ‘aspnet_%’ AND name NOT LIKE ‘ASPStateTemp%’ — STORED PROCS INSERT INTO @ReturnTable SELECT ‘DENY VIEW DEFINITION, ALTER, TAKE OWNERSHIP ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘P’ AND name <> ‘TempGetVersion’ — Now need to grant VIEW DEFINITION access to TempGetVersion for — the session state server to work INSERT INTO @ReturnTable SELECT ‘DENY ALTER, TAKE OWNERSHIP ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘P’ AND name = ‘TempGetVersion’ INSERT INTO @ReturnTable SELECT ‘GRANT VIEW DEFINITION ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘P’ AND name = ‘TempGetVersion’ INSERT INTO @ReturnTable SELECT ‘GRANT EXECUTE ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘P’ INSERT INTO @ReturnTable SELECT ‘DENY EXECUTE ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘P’ — VIEWS INSERT INTO @ReturnTable SELECT ‘DENY ALTER, TAKE OWNERSHIP, VIEW DEFINITION, INSERT, UPDATE, DELETE, REFERENCES ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘V’ AND name NOT LIKE ‘vw_aspnet_%’ INSERT INTO @ReturnTable SELECT ‘GRANT SELECT ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘V’ AND name NOT LIKE ‘vw_aspnet_%’ — TABLE VALUED FUNCTIONS INSERT INTO @ReturnTable SELECT ‘DENY ALTER, CONTROL, REFERENCES, SELECT, TAKE OWNERSHIP, VIEW DEFINITION ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘TF’ — SCALAR FUNCTIONS INSERT INTO @ReturnTable SELECT ‘DENY ALTER, CONTROL, REFERENCES, EXECUTE, TAKE OWNERSHIP, VIEW DEFINITION ON ‘ + QUOTENAME(name) + ‘ TO ‘ + @rolename FROM sys.objects WHERE type = ‘FN’ INSERT INTO @ReturnTable SELECT ‘GO’ –SELECT * FROM @TempTable ORDER BY ID RETURN END
