Solved

IIS accessing SQL server

Posted on 2011-03-18
17
915 Views
Last Modified: 2013-01-14
Hi!

I havea WCF service that uses ADO.Net Entity Framework, the Entity Framework uses "Integrated Security" all works fine when I host the service in "Visual Studio Developer Server" but when I use the "Local IIS Web Server" the Entity Framework cannot connect to the SQL server.

How do I setup the Authentication so IIS can acces the SQL server via "Integrated Security"?

Using Windows XP IIS
0
Comment
Question by:ulf-jzl
17 Comments
 
LVL 6

Accepted Solution

by:
wtapsell earned 300 total points
ID: 35165794
The easiest way is to set the account for the service to run under in the config file as detailed in the link below:

http://msdn.microsoft.com/en-us/library/72wdk8cc(v=VS.100).aspx
0
 
LVL 10

Assisted Solution

by:pramodsk40
pramodsk40 earned 200 total points
ID: 35165833
Make sure the id(domain id) exists on SQL server also as you want to use Integrated security.
0
 

Author Comment

by:ulf-jzl
ID: 35171567
Is it possible to use the NETWORK SERVICE account here? And if Yes, how do I set that up then?

this is my config,
<system.web>
    <compilation debug="true" targetFramework="4.0" />
    <authentication mode="Windows" />
    <identity impersonate="true"/>
    <authorization>
      <allow users = "*"/>
    </authorization>
  </system.web>

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 2

Expert Comment

by:KentMarsh
ID: 35180848
Yes. I have successfully done this using the NETWORK SERVICE.
0
 

Author Comment

by:ulf-jzl
ID: 35181067
I get  this error message all the time from the SQL server

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"

even if when I have created a domain user, added it to the SQL server i still get that error.

Can't figure out what I'm missing.

<system.web>
    <compilation debug="true" targetFramework="4.0" />
    <authentication mode="Windows" />
    <identity impersonate="true"
              userName ="COMPANY\Test.sa"
              password ="X1234FG"/>
    <authorization>
      <allow users = "*"/>
    </authorization>
  </system.web>

Open in new window

0
 

Author Comment

by:ulf-jzl
ID: 35181346
Thhis is the SQL server log:

03/21/2011 15:15:32,Logon,Unknown,Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxxxxxx]

03/21/2011 15:15:32,Logon,Unknown,Error: 18452<c/> Severity: 14<c/> State: 1.

03/21/2011 15:15:32,Logon,Unknown,SSPI handshake failed with error code 0x8009030c while
establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxx]

03/21/2011 15:15:32,Logon,Unknown,Error: 17806<c/> Severity: 20<c/> State: 2.
0
 
LVL 6

Expert Comment

by:wtapsell
ID: 35181518
This link may help:

http://support.microsoft.com/kb/306158

it covers all the ways to connect from a web service/asp.net web site to sql server.

The local Network_service account on the web service box will not work as SQL server will not recognize it.

0
 

Author Comment

by:ulf-jzl
ID: 35181552
This is the rest of my config


<system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior>
          <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->
          <serviceMetadata httpGetEnabled="true"/>
          <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
  
  <!-- DAL connections for ADO.Net Entity Framework-->
  <connectionStrings>
    <add name="FbiDmsEntities" connectionString="metadata=res://*/Models.FBI.DMS.Model.csdl|res://*/Models.FBI.DMS.Model.ssdl|res://*/Models.FBI.DMS.Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=EDS-SRV31;Initial Catalog=FBI.DMS;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Open in new window

0
 

Author Comment

by:ulf-jzl
ID: 35181699
This is what I have done.

1: I have added the domain user to the SQL Server to access the right database (read & write)
2: I have added the same domain user to have full access in the IIS Default Web Site
3: I have changed the Web config as you can see here
4; The Integrated Windows Authentication is Enabled in IIS

And when running in the "Visual Studio Developer Server" all works but when going over to "Local IIS Web Server"  I get this error message from the ADO.Net Entity Framework

The underlying provider failed on Open.
{"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."}

And the SQL server log:
03/21/2011 15:15:32,Logon,Unknown,Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxxxxxx]

03/21/2011 15:15:32,Logon,Unknown,Error: 18452<c/> Severity: 14<c/> State: 1.

03/21/2011 15:15:32,Logon,Unknown,SSPI handshake failed with error code 0x8009030c while
establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxx]

03/21/2011 15:15:32,Logon,Unknown,Error: 17806<c/> Severity: 20<c/> State: 2.
<system.web>  
    <compilation debug="true" targetFramework="4.0" />  
    <authentication mode="Windows" />  
    <identity impersonate="true"  
              userName ="COMPANY\Test.sa"  
              password ="X1234FG"/>  
    <authorization>  
      <allow users = "*"/>  
    </authorization>  
  </system.web> 

<system.serviceModel>  
    <behaviors>  
      <serviceBehaviors>  
        <behavior>  
          <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->  
          <serviceMetadata httpGetEnabled="true"/>  
          <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->  
          <serviceDebug includeExceptionDetailInFaults="false"/>  
        </behavior>  
      </serviceBehaviors>  
    </behaviors>  
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />  
  </system.serviceModel>  
  <system.webServer>  
    <modules runAllManagedModulesForAllRequests="true"/>  
  </system.webServer>  
    
  <!-- DAL connections for ADO.Net Entity Framework-->  
  <connectionStrings>  
    <add name="FbiDmsEntities" connectionString="metadata=res://*/Models.FBI.DMS.Model.csdl|res://*/Models.FBI.DMS.Model.ssdl|res://*/Models.FBI.DMS.Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=EDS-SRV31;Initial Catalog=FBI.DMS;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />  
  </connectionStrings>

Open in new window

0
 
LVL 6

Expert Comment

by:wtapsell
ID: 35182016
have you had a look at this part of the link I listed:

Note The identity of the process that impersonates a specific user on a thread must have the "Act as part of the operating system" privilege. By default, the Aspnet_wp.exe process runs under a computer account named ASPNET. However, this account does not have the required privileges to impersonate a specific user.
0
 

Author Comment

by:ulf-jzl
ID: 35182112
ok but is also says:

"This privilege is not required for the .NET Framework 1.1."

Or I'm I missing something?
0
 

Author Comment

by:ulf-jzl
ID: 35183682
Can't get it to work.. I did add the ASPNET account to "Act as part of the operating system"  but I still get the same error trying to connect to the SQL server.
0
 

Author Comment

by:ulf-jzl
ID: 35183690
is there a way to see witch account that is trying to connect to the SQL server?
0
 

Author Comment

by:ulf-jzl
ID: 35183815
The client I use is the WCF Test Client to start the execution that leads to the SQL connection error.

If that helps?
0
 

Author Comment

by:ulf-jzl
ID: 35202995
Thx guys for trying, but it doesn't work, I tried it under IIS 7 and it worked directlly. So i'm leaving XP for W7 instead. thx anyway.
0
 

Author Closing Comment

by:ulf-jzl
ID: 35929962
It did not work..  But probably some problems on my side.
0
 

Expert Comment

by:krovomi
ID: 38774275
I have the same issue, what did you do to correct this ?

Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question