1

I have an ASP.NET (4.0) site accessed using Window Authentication & Impersonation only (Anoymous Disabled).

The SQL Server Connection is done with SSPI, and with the user identity of the Application pool, not with Windows Domain User of the logged in and Windows Authenticated user.

Everything works fine when the website is accessed locally from the IIS Web Server.

When accessed from a remote PC, even when using the same domain user, it fails.

And all servers are on the same domain.... Any ideas?

Thanks

Gabriel
  • 595
  • 7
  • 10
  • I'm confused, you are saying two different things. You say you're using Windows Authentication and Impersonation. Impersonation causes the worker process to run as the identity of the logged in user, which also causes the connection to the database to be the logged in user if using an SSPI connection because the App pool identity is the logged in user. But then you say you're not... how exactly are you doing those to mutually exclusive things? – Erik Funkenbusch May 08 '14 at 19:11
  • @ErikFunkenbusch you are right. Turns out the user i was testing was the one used to the SQL Server as well. Do you know of any way possible to have Windows Authentication and Impersonation for the Web Server processing, yet connect to SQL Server with a different Active Directory user? – Gabriel May 09 '14 at 12:19
  • Why do you need to impersonate the user? – Erik Funkenbusch May 09 '14 at 13:55
  • Because we also access some files on the disk from the web app, and the client has the users organized in active directory groups, some having access to some files while others not – Gabriel May 10 '14 at 07:18
  • There are other ways to access the files, for instance you could check their AD permissions in your code and compare them to the AD permissions of the file (all files would have to have the worker processes access as well). – Erik Funkenbusch May 10 '14 at 13:48

1 Answers1

2

This is a "double-hop" issue, which is where the server is not being trusted to pass the client's credentials on to another box (hop 1 is the credentials to the IIS box, hop 2 is from the IIS box to the SQL Server). It works when running directly on server but does not work when accessing from a remote PC. More here and here

Depends on requirements you might need Windows Authentication but not the impersonation on SQL Server. Impersonation on SQL Server means that for every user you need to have account/permissions in the database. If this is not the case and you only need a secure authentication (without hardcoding username and password in web.config) then you can do following

  • make IIS not impersontate
  • set pool to use Network Service account
  • create a login account in your db server with domainName\WebServerMachineName$ and grant rights

More in How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

If you still need impersonation than do

Ensure your Application server is set as Trusted for Delegation. Ensure in IIS that Anonymous Authentication is disabled and Windows Authentication is enabled, if using Windows 2008, enable ASP.Net Impersonation also. If using Windows 2008 and your app pool is running under Network Service then goto Advanced settings of Windows Authentication and turn Kernal Mode off. Set yourDomain\yourAppServer$ to have read access to the ASP.Net application folder. [Source]

Community
  • 1
  • 1
user2316116
  • 6,726
  • 1
  • 21
  • 35
  • I don't need a double hop. I don't need the user impersonated on the web server to be the one logging in the SQL Server, need the single user of the App pool to be acesssing the SQL Server while ned Windows authentication on the Web Server for authentication – Gabriel May 09 '14 at 07:25
  • Then follow the three steps I listed above. – user2316116 May 09 '14 at 07:27
  • Didn't get the chance to try them, client bit security-sensitive, but guessing these 3 steps would enable double-hop and logging into the SQL Server Database with the windows-authenticated user. Which they don't want. I'm guessing we'll have to choose between impersonation on Web Server and SQL Authentication on SQL Server Vs. No impersonation on Web Server and Active Directory authentication (using a single hardcoded user) on SQL – Gabriel May 10 '14 at 07:23