There is an option for configuring ASP.NET application to store session info into SQL Server: https://learn.microsoft.com/en-us/previous-versions/ms178586(v=vs.140)
However it requires creating a new database(ASPState) with some script
I'm wondering is there an option for using the application database itself for providing session storage or are there any hardcoded requirements that db name should be ASPState and the only option is creating another database.
I'll first mention it's not a good idea to mix aspstate and user objects in the same database. The aspstate data is used quite heavily and can require significant SQL Server transaction log space for ephemeral data that never needs to be recovered. It would be better to have a dedicated database in the SIMPLE recovery model for session state instead of co-mingling data with different recovery requirements.
The name of the aspstate database is configurable. Specify the Aspnet_regsql -sstype c argument for a custom database and the database name with -d. For example:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Aspnet_regsql.exe" -ssadd -sstype c -d YourUserDatabase -S YourSqlInstance -E
Below is an aspstate connection string example given the example above for Windows authentication. This can be identical to your normal user database connection string but here I added an explict application name to uniqueify the connection string so that session state uses a separate connection pool.
Data Source=YourSqlInstance;Initial Catalog=YourUserDatabase;Integrated Security=SSPI;Application Name=aspstate
You'll also need to grant permissions on the stored procedures used by session state unless you use a privileged account (a bad practice). Below is a script to do that via role membership:
USE YourUserDatabase;
GO
CREATE Role APSStateRole;
ALTER ROLE APSStateRole
ADD MEMBER [YourUserAccount]; --assuming user already exists
GRANT EXECUTE ON dbo.TempReleaseStateItemExclusive TO ASPStateRole;
GRANT EXECUTE ON dbo.TempInsertUninitializedItem TO ASPStateRole;
GRANT EXECUTE ON dbo.TempInsertStateItemShort TO ASPStateRole;
GRANT EXECUTE ON dbo.TempInsertStateItemLong TO ASPStateRole;
GRANT EXECUTE ON dbo.TempUpdateStateItemShort TO ASPStateRole;
GRANT EXECUTE ON dbo.TempUpdateStateItemShortNullLong TO ASPStateRole;
GRANT EXECUTE ON dbo.TempUpdateStateItemLong TO ASPStateRole;
GRANT EXECUTE ON dbo.TempUpdateStateItemLongNullShort TO ASPStateRole;
GRANT EXECUTE ON dbo.TempRemoveStateItem TO ASPStateRole;
GRANT EXECUTE ON dbo.TempResetTimeout TO ASPStateRole;
GRANT EXECUTE ON dbo.GetMajorVersion TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetVersion TO ASPStateRole;
GRANT EXECUTE ON dbo.GetHashCode TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetAppID TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItem TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItem2 TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItem3 TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItemExclusive TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItemExclusive2 TO ASPStateRole;
GRANT EXECUTE ON dbo.TempGetStateItemExclusive3 TO ASPStateRole;
GO