1

I have asp.net app and I'm going to store my session in SQL Server. I'm using Amazon RDS (Microsoft SQL Server Express Edition). I am using local db for testing and its works well there.

So I've tried to create session db with next line 

aspnet_regsql.exe -ssadd -sstype p -S mydb.rds.amazonaws.com -U myuser-P mypass

So as I meant it works for local db. But for Amazon RDS I've received next exception:

Start adding session state.

.. An error occurred during the execution of the SQL file 'InstallSqlState.sql'. Th e SQL error number is 229 and the SqlException message is: The EXECUTE permissio n was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. SQL Server: mydb.rds.amazonaws.com Database: aspnetdb SQL file loaded: InstallSqlState.sql

Commands failed:

/* Drop all tables, startup procedures, stored procedures and types. */

/* Drop the DeleteExpiredSessions_Job */

DECLARE @jobname nvarchar(200) SET @jobname = N'ASPState' + '_Job_DeleteExpiredSessions'

-- Delete the [local] job -- We expected to get an error if the job doesn't exist. PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expect ed.'

EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname

SQL Exception: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was deni ed on the object 'sp_delete_job', database 'msdb', schema 'dbo'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res ult, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionSta te, Boolean isInstall, SessionStateType sessionStatetype)

Does anybody know a way to solve this? This creates a database, but it is incomplete.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RredCat
  • 5,259
  • 5
  • 60
  • 100

2 Answers2

2

You can use sql server session state on SQL RDS.

You just need to comment out the parts of the InstallSqlState.sql that have to do with creating SQL Server Agent jobs. The script is used when running the aspnet_regsql.exe command. It's located in the framework folder right next to that exe. It doesn't gracefully handle failing to create the jobs, so it doesn't do any of the other steps to install session state.

Here's what my InstallSqlState.sql ultimately looked like: http://pastebin.com/QJDXC093

Or alternately, you can run the aspnet_regsql.exe command on a database where you do have permissions to create jobs and then use the import/export feature in management studio to move the resulting database schema from your database to RDS.

jaminto
  • 3,895
  • 3
  • 32
  • 36
  • Thanks for your comment. As I get correct I can't establish sql jobs on Amazone RDS, right? Establish state DB without jobs doesn't have sense. Because DB will grow extremely (job clear expired session's records). – RredCat Jan 11 '13 at 08:36
  • You're right. You can set up an operational task on another server to run a sql script on a schedule to clean these up. It's up to you but it depends on how badly you want the benefits of RDS. We're just using RDS on a test system now, so this is not a concern for us. – jaminto Jan 11 '13 at 16:19
  • As I had mentioned I installed MS SQL on EC2 service (choosed extra drive and so on) and I don't see any reason to pay for two services as I can use only one. – RredCat Jan 11 '13 at 18:48
  • 1
    You could also run the cleanup script based on some URL request - e.g. make one extra javascript GET call to a url on one out of every x requests to your site. Not the ideal solution, but it gets the job done. Again it all depends on how badly you want the benefits of RDS (i.e. a managed sql server instance). It depends on your specific use case and personal preferences. – jaminto Jan 11 '13 at 19:38
2

I've solved my issue create session DB on Amazon RDS.

  1. Create ASPState DB on AWS RDS and database and schema migration using Following Tool SQLAzureMW v5.15.6 Release Binary for SQL Server 2014.
  2. Change DB Name on sessionState mode="SQLServer" allowCustomSqlDatabase="true" sqlConnectionString="data source=amazon-server-name;initial catalog=ASPState;persist security info=True;user id=userid;password=password" cookieless="false" timeout="100">
RredCat
  • 5,259
  • 5
  • 60
  • 100
Nonu Thakur
  • 36
  • 1
  • Looks like MS has added this possibility now. I can't check it now but anyway thank for the answer. – RredCat Jan 04 '17 at 09:00