I'm going to be demoing a ASP.NET MVC website on a local network. This application has a connection to a database:
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-EBC-20141127093222.mdf;Initial Catalog=aspnet-EBC-20141127093222;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
I would like if this database can be used by both IIS and whenever I run my application locally. I've made a site on IIS - it is running .NET v4. My project lives in c:\inetpub\www\ebc
. I can publish the website but recieve this error upon viewing the page:
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Unexpected error occurred inside a LocalDB instance API method call. See the Windows Application event log for error details."
I know I need to allow remote connections to the sql server through microsoft sql server manager? Is there a way to do it elsewhere?
Click on the Create button and it will update the Index view as well add this new record to the database. Now let's go the SQL Server Object Explorer and refresh the database. Right-click on the Employees table and select the View data menu option. You will see that the record is added in the database.
Right-click on your ASP.NET MVC5 application inside Visual Studio and then click "Publish". Now, select the "IIS" option from the left menu and click "Create Profile" button. Change your publish method to "Web Deploy Package" and provide your package location, then click "Next". Click "Save" on the next screen.
Right-click your application and then click Properties. On the Directory Security tab, click Edit. In the Authentication Methods dialog box, clear the Anonymous Access check box, and then do one of the following: If SQL Server is on the same computer as IIS, select the Integrated Windows authentication check box.
I solved it like this:
Start by using Developer PowerShell or Developer Command Prompt for Visual Studio as Administrator and confirm that you only have one instance of LocalDb.
Type sqllocaldb info
to see your LocalDb instances.
I then followed this article and had Application Pool Identity set to my currently logged in user, credit: https://stackoverflow.com/a/38294458/3850405
https://docs.microsoft.com/en-us/archive/blogs/sqlexpress/using-localdb-with-full-iis-part-1-user-profile
In there I found that it is not enough to have Load User Profile
set to true for your Application Pool, you also need to set setProfileEnvironment
to true in applicationHost.config
normally located at C:\Windows\System32\inetsrv\config
. With this configuration it worked:
Original answer:
https://stackoverflow.com/a/62810876/3850405
The best solution is to use SQL Expression but if you don't want there are few steps that you have to do to make your mvc application work with localdb
sqllocaldb share v11.0 IIS_DB
(then the database will be shared and IIS can access it) Data Source=(LocalDb)\.\IIS_DB
then publish again Note that in this point you may receive an access deny error and there is how to fix it
For the server name enter (LocalDb)\.\IIS_DB
then right click on the connection and choose new query and execute this command
create login [IIS APPPool\DefaultAPPPool] from windows;
exec sp_addsrvrolemember N'IIS APPPool\DefaultAPPPool, sysadmin
When there are multiple version of SQL Server localDB installed in a machine, this error is very common.
Try to use (localdb)\mssqllocaldb
, as a common server name to connect to the LocalDB automatic instance, instead of "v11.0" or "v12.0".
The above automatic localdb instance, have a default name across all the version and avoid conflict between name & version.
Important: When updating the localdb to new version, remember the database created with an older version does not work with the newer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With