locked
Web.config and Windows Executables - Use Windows Authentication RRS feed

  • Question

  • User-431989064 posted

    I have a web app that serves as a front end to a SQL Server 2016 database. The web.config file is set up to use SQL Server authentication. I'd like to know if it's possible to modify the web.config file to use Windows authentication, but here's the catch - it has to be for a specific Windows user account. Using Visual Studio 2012 and IIS 8.0. Login to the web app is via SSO (single sign-on), so this change would only affect how the web app connects to the database, not how users log into the web app.

    I also have a number of executables that query the SQL Server database and then include the query results in the body of an email. These are also set up to use SQL Server authentication, and I'd like to modify these as well to use Windows authentication, for that same specific user account. A sample current connection string is:

    SqlConnection myConn = new SqlConnection("server=" + ServerName + "; uid=[username]; pwd=[password]; database=[database name]");

    All help is appreciated! Let me know if you need additional details.

    Monday, December 9, 2019 4:10 PM

Answers

  • User-719153870 posted

    Hi jjkatz,

    So instead of using one of the built-in accounts, I'd select "Custom Account" and then select the one I want to use?

    Sorry missed this post and yes, choose the custion account and input User name and password, this will work if your servers are under the same domain.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 7:46 AM
  • User475983607 posted

    Thanks, Yang. That worked, so my first question is now answered. I'll repost the second question here so you don't have to scroll up:

    I also have a number of executables that query the SQL Server database and then include the query results in the body of an email. These are also set up to use SQL Server authentication, and I'd like to modify these as well to use Windows authentication, for that same specific user account. A sample current connection string is:

    SqlConnection myConn = new SqlConnection("server=" + ServerName + "; uid=[username]; pwd=[password]; database=[database name]");

    These executables are all stored on my application server.

    The process that invokes the executables must run under the Windows account you setup.  

    Keep in mind, this question has nothing to do with ASP.NET but the concept is the same as the configuring the application pool identity.  Perhaps meet with your security team and ask for assistance. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 4:12 PM

All replies

  • User-719153870 posted

    Hi jjkatz,

    I'd like to know if it's possible to modify the web.config file to use Windows authentication, but here's the catch - it has to be for a specific Windows user account.

    Not quite understand the question but it seems here's a similar one: Connection String Using a Domain User?

    First of all, if you want to change the SQL Server authentication to Windows authentication, as far as i know, this need to be configured in the SSMS instead of in your web.config file.

    Also, please check How to implement impersonation in an ASP.NET application for more information.

    Best Regard,

    Yang Shen

    Tuesday, December 10, 2019 5:10 AM
  • User-431989064 posted

    Hi, Yang:

    Thanks for your reply. I already have Windows Authentication set up in SSMS, and it's working fine. I'll try to explain further. My app is internal to my company, only accessible from inside our network. Our users log into the web app using their own employee ID and password. That connects them to the web application. The web app is a front end to a SQL Server database. The users do not log directly into that. The app connects to the database via the web.config file. This is invisible to our users and none of them know the credentials for that connection. I need to keep it that way. So - I need the web.config file to connect to the database using Windows Authentication, but NOT with the username of the person who just logged in. It has to connect with a specific account name and password. The impersonation link you provided may work. I'll read up on it some more and post back once I've had a chance to try it out.

    Thanks,

    Josh

    Wednesday, December 11, 2019 10:10 PM
  • User475983607 posted

    If you have the Windows account and the account is setup in SQL server then all you have to do is add the same account to your application's app pool identity in IIS.

    https://docs.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities

    Update the connection string to use integrated security. 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax

    Wednesday, December 11, 2019 10:22 PM
  • User-431989064 posted

    So instead of using one of the built-in accounts, I'd select "Custom Account" and then select the one I want to use?

    Thursday, December 12, 2019 2:51 PM
  • User-719153870 posted

    Hi jjkatz,

    Please check Allowing IIS 7.5 Applications to communicate with SQL server via Windows Authentication and Add IIS 7 AppPool Identities as SQL Server Logons, you will see the complete process how you can use Windows Authentication to SQL Server in IIS.

    Best Regard,

    Yang Shen

    Friday, December 13, 2019 5:20 AM
  • User-431989064 posted

    My application and database are on separate servers so it doesn't look like this will work for me. Setting up ASP.NET impersonation looks to be for logging into the Windows app, which I do not want to change. I only want to change the way the app connects to the database.

    Friday, December 13, 2019 5:47 PM
  • User-719153870 posted

    Hi jjkatz,

    So instead of using one of the built-in accounts, I'd select "Custom Account" and then select the one I want to use?

    Sorry missed this post and yes, choose the custion account and input User name and password, this will work if your servers are under the same domain.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 7:46 AM
  • User-431989064 posted

    Thanks, Yang. That worked, so my first question is now answered. I'll repost the second question here so you don't have to scroll up:

    I also have a number of executables that query the SQL Server database and then include the query results in the body of an email. These are also set up to use SQL Server authentication, and I'd like to modify these as well to use Windows authentication, for that same specific user account. A sample current connection string is:

    SqlConnection myConn = new SqlConnection("server=" + ServerName + "; uid=[username]; pwd=[password]; database=[database name]");

    These executables are all stored on my application server.

    Tuesday, December 17, 2019 3:36 PM
  • User475983607 posted

    Thanks, Yang. That worked, so my first question is now answered. I'll repost the second question here so you don't have to scroll up:

    I also have a number of executables that query the SQL Server database and then include the query results in the body of an email. These are also set up to use SQL Server authentication, and I'd like to modify these as well to use Windows authentication, for that same specific user account. A sample current connection string is:

    SqlConnection myConn = new SqlConnection("server=" + ServerName + "; uid=[username]; pwd=[password]; database=[database name]");

    These executables are all stored on my application server.

    The process that invokes the executables must run under the Windows account you setup.  

    Keep in mind, this question has nothing to do with ASP.NET but the concept is the same as the configuring the application pool identity.  Perhaps meet with your security team and ask for assistance. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 17, 2019 4:12 PM
  • User-431989064 posted

    OK, thanks very much. This clears up my options, so I will mark this as answered.

    Thanks everyone for their help, links, etc.

    Tuesday, December 17, 2019 5:28 PM