locked
Deploying a site where DB and web application are on different servers RRS feed

  • Question

  • User1149602699 posted

    Good day,

    We have a very simple web application that simply reads and writes data from a SQL server table. We would like to deploy this on a server within our domain while having the SQL server DB residing on another server within the same domain. 

    We have created a SQL server authentication user and we have included it in the connection string of the web application. We have tried turning off the integrated security as well. We still cant get this to work.

    The error message now (with integrated security set to false) is: login failed for user [domain name]/[server machine name + $]

    I verified this using my own PC as a web server and tried to publish the web application on it. I get the same exact error message.

    I should note that we only get that error message when the web application tries to access the DB.

    We are able to publish asp.net web applications with ease provided that both the web application and the SQL server are on the same machine. But never in the scenario I explained. This is actually a trial for us to fix this once and for all. 

    I have searched online and found solutions suggesting to grant permission to the machine name. Others suggesting adding the machine name to the AD. Not really sure which to try. I feel the solution is very simple but we're missing it. We did not try anything related to the AD or the IIS yet.

    If anyone can help with this please let me know.

    Tuesday, January 22, 2019 9:58 AM

Answers

  • User753101303 posted

    When your site runs under the default local application pool identity account and try to access the network it is seen as DOMAIN\SERVERNAME$ (ie the machine account). So as pointed already a possible solution is to grant access to this account on the SQL Server side (but then all apps on this web server could potentially access this db) :

    Another option is to create a dedicated domain account to run your app and you'll use the same account to access the db. See https://docs.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 22, 2019 10:30 AM

All replies

  • User-746821919 posted

    Could you share your connection string, please?

    Tuesday, January 22, 2019 10:21 AM
  • User753101303 posted

    When your site runs under the default local application pool identity account and try to access the network it is seen as DOMAIN\SERVERNAME$ (ie the machine account). So as pointed already a possible solution is to grant access to this account on the SQL Server side (but then all apps on this web server could potentially access this db) :

    Another option is to create a dedicated domain account to run your app and you'll use the same account to access the db. See https://docs.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 22, 2019 10:30 AM
  • User1149602699 posted

    Hello Sakthivel

    Right now it is as such

    <connectionStrings>
    <add name="****ConnectionString" connectionString="Data Source=***.**.**.**;Initial Catalog=******; User Id=*****; Password= *****; Integrated Security=False" providerName="System.Data.SqlClient"/>
    </connectionStrings>

    We use this format for all our web applications and it works perfectly if the web application and the DB are on the same machine.

    Tuesday, January 22, 2019 10:43 AM
  • User1149602699 posted

    For the first solution, do we need to include the $ when giving SQL access?

    second solution: So what we need is a new separate application pool on IIS with a new Identity which should also be used as a SQL authentication account. I think we can try that.

    Thank you for your reply.

    Tuesday, January 22, 2019 10:46 AM
  • User753101303 posted

    It's surprising that you have a user id and password in your connection string as well as Integrated Security=false (the behavior you see is that integrated security works, I would expect to have user id and password or to have Integrated Security=True.

    Yes the final $ is part of the machine account name.

    It all depends on which isolation you need/want accross apps (and yes you'll need a separate application pool with its own identity). It is often suggested to use an application pol for each site.

    Tuesday, January 22, 2019 10:51 AM
  • User1149602699 posted

    That is my mistake actually. We have tried that once based on suggestion made online but now Integrated Security is back to true. 

    To be honest, our admin team is more towards Oracle products. They dabble with Microsoft products to a point, but beyond that, developers try to find solutions despite limited admin experience.

    One thing I just tried based on your suggestion and I used my own domain account which has DB access and it worked perfectly. So I thank you for this!  Solved a chronic problem for us.

    Just need to get a dedicated account going for it. 

    Tuesday, January 22, 2019 11:03 AM