Login failed for user '...' in ASP.NET WebAPI

Question

The client application and the sql server instance are on separate machines. I'm working with Entity Framework and have created the Data Model for the database. Locally the application works fine with the database. But after the application has been deployed on IIS, I cannot access to the database. I'm getting the error message:

The underlying provider failed on Open.

Login failed for user 'domain\account'.

In the Web.config of the WebAPI is the connectionString defined as follows:

<system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <authentication mode="Windows" />
</system.web>
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>
<connectionStrings>
    <add name="QuReContext" connectionString="metadata=res://*/Models.QuReModel.csdl|res://*/Models.QuReModel.ssdl|res://*/Models.QuReModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=spartak,2500;initial catalog=First_DB;User Id=domain\account;Password=MyPW;MultipleActiveResultSets=True;&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

I've tried many kinds of solutions from threads in stackoverflow. But nothing has helped. I'm using the ASP.NET WebAPI 2, the Entity Framework 6, IIS 7.5 on a remote Webserver with Windows Server 2008 R2 and the SQL Server 2014 where my domain account is defined for access the database. This domain account I've written in the connection string of Web.config.

On IIS is created in Default Web Site "MyClientApp" and in this application is an extra application "RestApi" available. The Application Pool is defined as follows: Identity: ApplicationPoolIdentity, Managed Pipeline Mode: Integrated, Under the authentication of the application "RestApi" is just Windows authentication and basic authentication enabled.

Do anyone has an idea?

@Devart This domain account is from the windows authentication and in the sql server defined too. — yuro

JotaBe JotaBe · Accepted Answer · 2016-03-15T09:49:49

You can set up SQL Server to support two authentication modes:

Integrated (Windows) security
Mixed (Windows + SQL Server logins)

The first mode uses the current user credentials to try to login in SQL Server. In this case, the credentials are the ones from the process running the ASP.NET application, in other words, the app pool user. To specify this kind of login you need to include this in your connection string: Integrated Security=SSPI. (I've seen on some occassions that, apart from specifying integrated security, people specify user and password, but I'm not sure if you can override the current user credentials. In fact, I think that only worked in Windows CE).

The second mode supports integrated security, and also SQL Server logins, where the user and passwords are managed by SQL Server itself, and have nothing to do with Windows users and passwords. To authenticate a SQL Server login you must specify the user ID and password as you're doing in your query string: User Id=myUsername; Password=myPassword;

You should read about integrated and mixed security mode in SQL Server. This is quite an old information, but still applies.

Login failed for user '...' in ASP.NET WebAPI

1 Answers