CODEWITHSUNDEEP
asp.netsqliteazureiisconnection-string
trademark
trademark

Reputation: 595

Can't Access SQLite DB on Azure Web App

I have created an ASP.NET MVC web app which uses an SQLite database under EF6.

The app works fine in VS. The database is included in the project in the App_Data folder. When I publish to Azure (or an IIS test server on the LAN) I get an error. The Azure instance is showing an HTTP 500 error, and it only happens on views which require database access to load. Other than that it works fine. The IIS test server shows an error saying unable to open database file. I tried loosening file permissions on the .db file on the IIS instance, but nothing changed. I'm not sure how to go about doing anything on the Azure instance.

Can someone please assist me with debugging a SQLite connection problem on Azure/IIS?

Details

I have tried all sorts of combinations of connection strings:

Nothing has worked or even changed the error. Perhaps one of those configurations is able to access the file but simply can't open it (which is sort of what the IIS error suggests, I think). Even then, how do I go about finding out?

EDIT On the IIS test server I discovered that the entire problem was that the connection string in Web.config was wrong. I manually edited it on the server and it fired right up. I'm still not sure how to correct the path on the Azure server, nor how to correct my deployment settings in VS to inject the correct path. Thoughts along those lines are still appreciated.

EDIT 2

I've also found that each time I hit a page requiring DB access, I see this error show up in the application logs on Azure:

2017-01-03T20:25:32 System.ApplicationException: The trace listener AzureBlobTraceListener is disabled. ---> System.InvalidOperationException: The SAS URL for the cloud storage account is not specified. Use the environment variable 'DIAGNOSTICS_AZUREBLOBCONTAINERSASURL' to define it. at Microsoft.WindowsAzure.WebSites.Diagnostics.AzureBlobTraceListener.RefreshConfig() --- End of inner exception stack trace ---

Upvotes: 2

Views: 6548

Answers (1)

trademark
trademark

Reputation: 595

After some abysmally slow debugging sessions, I finally remembered that in toying around I had set a connection string value in the Azure portal.

Apparently whatever value is set in the Azure Portal overrides (at runtime?) the values in the Web.config file

I simply deleted the connection string from the Azure Portal (your app/Application Settings/Connection Strings) and it worked. The final connection string looks like this:

 <add name="yourThingEntities" connectionString="metadata=res://*/cadb_SQLite.csdl|res://*/cadb_SQLite.ssdl|res://*/cadb_SQLite.msl;provider=System.Data.SQLite.EF6;provider connection string=&quot;data source=D:\home\site\wwwroot\App_Data\yourSQLiteDBFile.db&quot;" providerName="System.Data.EntityClient" />

To get the Web Deploy working in VS, you'll need to edit the publish profile to have a connection string matching the format

data source=D:\home\site\wwwroot\App_Data\yourSQLiteDBFile.db

Apparently not many people use SQLite for web apps, and most people should not. If you ever expect more than one user for your website at a time, you shouldn't fix SQLite, you should convert to SQL Server CE or just full-blown SQL Server which Azure will take care of for a paltry $5/mo. Choose wisely!

Upvotes: 3

Related Questions