Solved

IIS accessing SQL server

Posted on 2011-03-18
17
906 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lync server 2013 Backup Service Error ID 4049 – After File Share Migration
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now