0

I'm currently working on an intranet portal for my company, it will remain internal, only users with accounts on our domain will have access to it. At the moment, I use forms authentication method, and authenticate users against the Active Directory domain with System.DirectoryServices. I also interrogate the AD with System.DirectoryServices.AccountManagement to manage access rights depending on the Active Directory groups the user belongs to and I store some data in user sessions and everything works fine.

I've been asked to study whether Single Sign-On could be implemented in our context, all our users work in RDS without exceptions, so they always are in an authenticated session and the higher-ups would prefer not having to sign in at all. As far as rewritting the code to authentify and authorize users as explained here and here I'm not too concerned.

I tried everything on a blank ASP.Net MVC5 project, to which I added an ADO.Net model with integrated security. My dev SQL Server is hosted on a remote server (not the dev desktop), the same server also hosts the dev IIS server. In production, both services will be on separate servers. This worked fine as long as I was debugging localy on my desktop, but once I published to the IIS server, I hit the first snag and couldn't get out of it.

When I changed settings in the IIS app to disable anonymous and enable windows authentication, IIS automatically changed <authentication mode="Forms" /> to <authentication mode="Windows" /> in the web.config file (as suggested here), but I couldn't make it work with Integrated Security=True or Integrated Security=SSPI, I get an error page saying [SqlException (0x80131904): Login failed for user 'DOMAIN\MACHINE$'.].

The connection string reads like: <add name="ASPNetProjectEntities" connectionString="metadata=res://*/Models.ModelASPNetProject.csdl|res://*/Models.ModelASPNetProject.ssdl|res://*/Models.ModelASPNetProject.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MACHINE;initial catalog=DATABASE;persist security info=True;Integrated Security=SSPI;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I tried to add the machine account DOMAIN\MACHINE$ to SQL logins and grant it rights to the database, but no dice. I tried to authorize delegation in the server AD account and activate identity impersonation, but that didn't work either. I can only make it work on the remote IIS server if I put a SQL user/pass in the connection string.

So I'm at loss what could be missing here to make it work with Integrated Security. Or will I have to put SQL credentials in the connection string and encrypt it on IIS?

1 Answers1

0

You're confusing Integrated Security with the authentication mechanism of your website. If you use Integrated Security, the connection used from your web app to SQL Server will use the account that the application pool your web app is running as is using. To use Integrated Security, have a service account created in AD, set your app pool to run as that account, and grant the necessary permissions to that service account in SQL Server.

Note that whether you're using forms auth or integrated security isn't relevant to how the web app connects to SQL Server, unless you're using impersonation, which you should never use.

mason
  • 31,774
  • 10
  • 77
  • 121
  • Why, yes, this does solve the issue of the web app trying to connet to the SQL server, but it does introduce another problem. When published to the IIS Server, the whole code runs under the service account, and `System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString()` returns that service account username, rather than the end user username. –  Oct 29 '19 at 16:44
  • 1
    @Eric Of course, that's because you used the wrong thing to grab the current user's username. That method you described is for getting the account the application is running as, not the account of the user accessing the app. There's several ways to get that depending on what context you're in. In an MVC controller, you can get it via `User.Identity.Name`, because `User` is a property of the controller instance. – mason Oct 29 '19 at 16:50