locked
Configure SQL string connection in web.config RRS feed

  • Question

  • User2029284820 posted

    Hi All,

    I am trying to deploy an asp.net c# site into a new web windows 2012R2 server using IIS8.

    The site use SQL server for database, currently it is connecting using sa user. How can I configure and connect using a windows domain user account?

    the connection string on the web.config looks like:

    <connectionStrings>
      <add name="myConnString" connectionString="Data Source=10.10.1.20;Initial Catalog=myDB;Integrated Security=False; User Id=sa; Password=myPass;"
       providerName="System.Data.SqlClient" />
      </connectionStrings>

            <authentication mode="Forms">
                <forms loginUrl="~/Login.aspx" defaultUrl="~/Index.aspx"/>
            </authentication>
            <authorization>
                <deny users="?"/>
            </authorization>

    The domain windows user mySQLuser have the permissions and connect to the SQL server fine.

    I try using this connection string:

    <add name="myConnString" connectionString="Data Source=10.10.1.20;Initial Catalog=myDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

    But I got an error, Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'myDomain\MY-DEV-WEB$'.

    Where MY-DEV-WEB is the name of the server. How can I included the name my user in the string connection?

    Thanks in advanced!

    Wednesday, April 5, 2017 11:31 AM

All replies

  • User-943250815 posted

    Short answer,.

    On web.config you can use:

    <add name="<your connection string name>" connectionString="Data Source=<your server name>; Initial Catalog=<your database name>;Integrated Security=True;" providerName="System.Data.SqlClient" />

    On SQL Server, create a Login for you windows user, assign database and proper DB roles.

    On IIS8, create an Application Pool, in Advanced Settings > Identity change from Built-in Account to Custom Account, provide Windows User Name & Password (same used for SQL Server Login). Finally assign the Application Pool created to your website, you can do it in Basic Settings

    This way, Application Pool will use user assigned to Login on SQL Server.

    But remember when user password changes, you have to adjust it of Application Pool Identity

    Had same issue a week ago, after some research, settings above worked for me.

    Wednesday, April 5, 2017 12:51 PM
  • User2029284820 posted

    Thanks for you reply and help jzero.

    I followed your steps and now i am getting different error: HTTP Error 503. The service is unavailable.

    Any ideas?

    How can I get more info about this error?

    Best,

    Wednesday, April 5, 2017 1:54 PM
  • User2029284820 posted

    I found this error in the ISS event:

    Application pool myApp App Pool has been disabled. Windows Process Activation Service (WAS) encountered a failure when it started a worker process to serve the application pool.

    Wednesday, April 5, 2017 2:10 PM
  • User-943250815 posted

    I had no similar issue, but searching for error you mention, results in "Reset User Password", and write it again in Application Pool Identity. Don´t forget to start Application Pool, this should prevent Error 503

    Wednesday, April 5, 2017 2:33 PM
  • User2029284820 posted

    I created a new App pool for this user, no errors while entering the password. This mean that my login validation is ok, right?

    The App Pool cannot start. My domain user is also part of service account. I also Added the user as part of IIS_USRS group as:

    https://technet.microsoft.com/en-us/library/cc735179(v=ws.10).aspx

    I don't know what else to check.

    Thanks!

    Wednesday, April 5, 2017 2:47 PM
  • User-943250815 posted

    From my perspective, when you enter user password in App Pool, no validation is done. It just write write User & Password in appropriated section of IIS xml config.
    But validation/authentication is necessary when you Start AppPool and if not, when you load a web site page.
    Use the format domain\username for the username

    Also, there is no need to add App Pool user to any group, App Pool user will be used by IIS to autenticate against OS and SQL Server, so you can get access to your DB and also can give rights to it write on some folder of your web site like an Upload folder.

    But seems there is a little difference between your scenario and my one.
    In my case: Windows Server 2012 Datacenter / IIS 8.0 / SQL Server 2014 Standard (not the free one "Express"), all in a single box, there is no domain, only local accounts

    If possible in your case, try using local account (if all is in single box) a check what happens.
    From Technet article, note "identity may be incorrect, or the user may not have batch logon rights", so the question is: Can you logon from your computer using credentials of created user?

    OR, perhaps due to Domain some rights should be given to user: https://blogs.msdn.microsoft.com/ssehgal/2009/06/23/running-iis6-app-pools-under-a-domain-account-identity/

    Wednesday, April 5, 2017 6:32 PM
  • User2029284820 posted

    The App pool was set with user myDomain\mySQLuser. I got an error typing the wrong password. I have two servers one for SQL Server and the other one for the web server.

    I cannot use local account because the SQL server is in a different box. The user myDomain\mySQLuser can login into sql server box and into sql server database.

    That configuration is for IIS6, windows server 2012R2 is using IIS8.

    Any other ideas?

    Wednesday, April 5, 2017 7:52 PM
  • User-943250815 posted

    Ok user can login on SQL box and server, what about IIS box?
    By a moment, forget SQL Server, just focus on IIS & App Pool, with a web site with a simple default page, if you still have problems, it will be on IIS box.

    Wednesday, April 5, 2017 8:01 PM
  • User2029284820 posted

    Agreed, the issue is in the IIS box.

    I cannot connect to the IIS box with this user, it is strange because the user is part of the domain. Let me figure this out and back to you.

    Thanks!

    Wednesday, April 5, 2017 10:48 PM
  • User-2057865890 posted

    Hi jfb00,

    Mapping your application process to a Windows domain user account requires that you configure the following:

    • The Web server. You must make sure that the Windows domain user account that you specify has sufficient user rights (but no more) to run a Web application.
    • Your application. You must configure the Web.config file for ASP.NET to recognize the domain user account name.
    • A connection string. When you create connection strings for connection objects in your application, you have to specify that the connection strings will use Windows integrated security.
    • SQL Server. You must add the specified domain user account as a SQL Server login user.

    To connect to SQL Server using Windows integrated authentication, you must identify the Windows identity under which your ASP.NET application is running. You must also be sure that the identity has been granted access to the SQL Server database. After establishing the correct user rights for the domain user account, configure the application identity impersonation. Open the Web.config file for your application, and then add the following identity impersonation code:

    <identity impersonate="true" userName="domain\username" password="********"/>

    How to: Access SQL Server Using Windows Integrated Security

    Best Regards,

    Chris

    Thursday, April 6, 2017 5:07 AM
  • User2029284820 posted

    Thanks for your reply and help Chris.

    That was the issue, it was missing the identity impersonate tag. We don't need to set the app pool to a domain user, this is only if the entire app connect windows auth users.

    Is there a way to encrypt the password?

    Best,

    JFB

    Thursday, April 6, 2017 12:08 PM
  • User-943250815 posted

    Interesting, looks you have a little more work when in Domain.

    On the other hand, should have a way to still using App Pool and have all website running isolated in App Pool context, so we can have multiple web sites running each in an AppPool.

    Back to initial sub-question, "How to hide user/password", by the end you moved from connectionstring to impersonate....

    Article suggested by Chris, mention you can use <impersonate = "true"> without user/pwd, may be an alternative. The other one, I know is Encrypt Sections of web.config (never touch this feature).

    For now, at least to me, seems very simple everything in a single box. Of course my scenario impose this condition. Different than your

    Thursday, April 6, 2017 2:25 PM
  • User2029284820 posted

    Is this a good way to do the web.config encryption?

    https://msdn.microsoft.com/en-us/library/zhhddkxy.aspx

    Thanks ~JFB

    Thursday, April 6, 2017 4:51 PM
  • User-943250815 posted

    To me seems good, you can also take a look at https://www.codeproject.com/Tips/877258/How-to-Encrypt-Web-config-Using-aspnet-regiis-exe

    There you can find more examples, how to encrypt a particular section

    Thursday, April 6, 2017 11:55 PM