Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

IIS accessing SQL server

Posted on 2011-03-18
17
Medium Priority
?
969 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 900 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 600 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 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