ASP.NET deployment to IIS connects to localdb but always shows transient failure error-CodePudding

I am having this problem for more than a day already so I decided to ask for help.

I can successfully test my ASP.NET project on the IIS Express from Visual Studio, the connection string works perfectly. Also, when running my program.exe located in the IIS deployment folder it also connects perfectly to the localdb.

Unfortunately, when using the live IP address of my project the error appears, so my guess is that it is connecting to the DB but it is not being able to retrieve or push data to it for some reason.

I tried to use different methods of DB connection, localdb, shared localdb and SQL Express. None worked.

My connection string is the following:
Server=(localdb)\\MSSQLLocalDB;Database=PaymentConnect;Trusted_Connection=True;

I have tried to add the EnableRetryOnFailure to my code, unfortunately the error remains, after the allowed tries it fails again.

I literally have no experience deploying projects to IIS, and neither have experience on ASP.NET. If anyone can help me it would be great! Thanks!

If I forgot to add something please let me know so I can update the question

CodePudding user response：

The error unfortunately is generic, it only says that something went wrong and possibly is a transient failure. But my issue is actually solved, for those experiencing the same issue as I am here is how to solve it.

Instead of using localdb you should download and set up SQL Express, it is like a local server but shared with all users on the PC, you will need that because when IIS tries to open your localdb it will actually do so as its own user, and so the localdb is different. Remember to change your connection string on the app, it should look something like this: "DefaultConnection": "Server=.\\SQLEXPRESS;Database=PaymentConnect;Trusted_Connection=True;"

After doing that you need to create a security user on the DB like so:

IIS APPPOOL\nameOfMyAppPool

where nameOfMyAppPool is the name of the Application pool your website is using.

Now after that what I did, which I am not sure is needed, is that I gave this security user sysadmin Server Roles, you will see this when creating this user, also on user mapping you select your DB and give it db_datareader db_datawriter and db_owner for it.

After doing that go to your DB and click to see its properties, you will need to go to permissions and give this new user you just created permissions to:

     select delete insert update connect replication execute references control

Once more I would like to remind that this is how I resolved the issue, I am not sure which of these are really necessary or not.

And that should do the trick.