Solved

Trusted Connection, SQL Server, and ASP.NET

Posted on 2004-10-21
1,132 Views
Last Modified: 2012-05-05
We have our IIS server and SQL server on separate boxes. Up until now, we have configured a separate login on the SQL server, call it db_user, given it access to the databases the (intranet, no external access) web site needs, and used a connection string in our ASP.NET pages like:

"workstation id=\iisserver\;packet size=4096;user id=db_user;data source=sqlserver;persist security info=True;initial catalog=ACASCRM;password=mypassword"

which has worked fine. Now however, we have gone to a new sqlserver and are not permitted to create db_user. Instead, we are required to use windows authentication and so a new sql server login is created which is one of our active directory groups. Users who are members of this group should have access to the database via the asp.net pages.

I understand that in this case we must use this connection string:

"Data Source=sqlserver;Initial Catalog=acascrm;Integrated Security=SSPI;"

we already have the IIS configured so that the user must log in to have access to the web page (Integrated WIndows authentication, anonymous access off). What else do we have to do to pass the windows credentials to the SQL server database?

Thanks
0
Question by:RichardFox
    11 Comments
     
    LVL 17

    Assisted Solution

    by:AerosSaga
    Ok the best idea is to setup a domain account for each asp.net application running, then use identity impersonation and your web.config should look like so:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <appSettings>
          <add key="EmeraldConnStr" value="data source=MyServer;initial catalog=MyDb;integrated security=SSPI;persist security info=False;packet size=4096;"></add>
          </appSettings>
      <system.web>
          
        <!--  DYNAMIC DEBUG COMPILATION
              Set compilation debug="true" to insert debugging symbols (.pdb information)
              into the compiled page. Because this creates a larger file that executes
              more slowly, you should set this value to true only when debugging and to
              false at all other times. For more information, refer to the documentation about
              debugging ASP.NET files.
        -->
        <compilation defaultLanguage="vb" debug="true" />

        <!--  CUSTOM ERROR MESSAGES
              Set customErrors mode="On" or "RemoteOnly" to enable custom error messages, "Off" to disable.
              Add <error> tags for each of the errors you want to handle.

              "On" Always display custom (friendly) messages.
              "Off" Always display detailed ASP.NET error information.
              "RemoteOnly" Display custom (friendly) messages only to users not running
               on the local Web server. This setting is recommended for security purposes, so
               that you do not display application detail information to remote clients.
        -->
        <customErrors mode="Off" />

        <!--  AUTHENTICATION
              This section sets the authentication policies of the application. Possible modes are "Windows",
              "Forms", "Passport" and "None"

              "None" No authentication is performed.
              "Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to
               its settings for the application. Anonymous access must be disabled in IIS.
              "Forms" You provide a custom form (Web page) for users to enter their credentials, and then
               you authenticate them in your application. A user credential token is stored in a cookie.
              "Passport" Authentication is performed via a centralized authentication service provided
               by Microsoft that offers a single logon and core profile services for member sites.
        -->
        <authentication mode="Forms" >
        <forms name="synthesis" loginUrl="login.aspx" timeout="30" />      
              
          </authentication>
           <identity impersonate="true" userName="MYDOMAIN\ASPNET_MyUserAccount"
                                 password="XXXX"/>
        <!--  AUTHORIZATION
              This section sets the authorization policies of the application. You can allow or deny access
              to application resources by user or role. Wildcards: "*" mean everyone, "?" means anonymous
              (unauthenticated) users.
        -->
       
        <authorization>
                  <allow users="*"/>
        </authorization>
          
        <!--  APPLICATION-LEVEL TRACE LOGGING
              Application-level tracing enables trace log output for every page within an application.
              Set trace enabled="true" to enable application trace logging.  If pageOutput="true", the
              trace information will be displayed at the bottom of each page.  Otherwise, you can view the
              application trace log by browsing the "trace.axd" page from your web application
              root.
        -->
        <trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true" />


        <!--  SESSION STATE SETTINGS
              By default ASP.NET uses cookies to identify which requests belong to a particular session.
              If cookies are not available, a session can be tracked by adding a session identifier to the URL.
              To disable cookies, set sessionState cookieless="true".
        -->
        <sessionState
                mode="InProc"
                stateConnectionString="tcpip=127.0.0.1:42424"
                sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
                cookieless="false"
                timeout="30"
        />

        <!--  GLOBALIZATION
              This section sets the globalization settings of the application.
        -->
        <globalization requestEncoding="utf-8" responseEncoding="utf-8" />
     
      </system.web>

    </configuration>

    ideally you would also hash the password, but for clarity I left it in as plain text.

    Aeros
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    assuming your user account that your application is running under has the needed SQL permissions, and your SQL server is setup for Integrated Windows Authentication that should be it.  You set the acount for the application to run under in its IIS Virtual Directory properties.

    0
     
    LVL 8

    Assisted Solution

    by:daffodils
    I agree with AerosSaga, not completely though.. you need Windows authentication with Group Authorization.

    >> Users who are members of this group should have access to the database via the asp.net pages.

    So more than one user would need to access the SQL Server with his own credentials.. and not one standard SQL user credentials.

    In this case, you need:
    1. Authentication mode set to Windows. <authentication mode="Windows" >
    2. Impersonation turned on. <identity impersonate="true"/>
    3. Connection string in Web.config
    <configuration>
        <appSettings>
            <add key="ConnectString" value="Data Source=sqlserver;Initial Catalog=acascrm;Integrated Security=SSPI;" />
        </appSettings>
       <system.web>
         .
         .
        </system.web>
    </configuration>

    Now in your web page, you can access this value as
         string appName = ConfigurationSettings.AppSettings["ConnectString"];

    4. Authorize group memebers
        <authorization>
                <allow users="MYDOMAIN\Group_Name"/>
                <deny users="*"/>
        </authorization>
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    If the scope of your application warrents such management then I agree with  daffodils  100% Thanks for adding that.

    Aeros
    0
     
    LVL 8

    Expert Comment

    by:daffodils
    >>I understand that in this case we must use this connection string:
    >>"Data Source=sqlserver;Initial Catalog=acascrm;Integrated Security=SSPI;"

    And you are right here.. for an SQL server connection in Windows authentication mode, you need to set up three things:
    -- Data Source -or-Server -or-Address -or-Addr -or-Network Address
       The name or network address of the instance of SQL Server to which to connect.
    -- Initial Catalog -or-Database
       The database name.
    -- Integrated Security -or-Trusted_Connection (default is 'false')
       If ‘true’ or ‘sspi’, the current Windows account credentials are used for authentication.
    0
     
    LVL 33

    Accepted Solution

    by:
    If you don't want to set the user/pass in web.config as daffodils suggest, but have the user hit sql server using their own credentials, you will have to enable delegation on the WebServer.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;810572

    That can be a bit tricky the first time you do it, but once it is set up it just works.

    0
     
    LVL 10

    Assisted Solution

    by:jnhorst
    Aeros' web.config has this:

    <authentication mode="Forms" >
    <forms name="synthesis" loginUrl="login.aspx" timeout="30" />

    Your post specified Windows auth, so you do not want to do this:  You should have:

    <authentication mode="Windows" />

    Also, this:

    <identity impersonate="true" userName="MYDOMAIN\ASPNET_MyUserAccount" password="XXXX"/>

    The idea is right, but you do not want to specify the username and pwd.  Just have impersonate=true:

    <identity impersonate="true" />

    Since you are using Windows auth and not allowing anonymous auth on the IIS box, the code that accesses the db will attempt to do so under the logged in user's security context.  You have added to your SQL Server Logins a Windows Group, so once you put that above tag in web.config, as long as the logged in user is in the group, the connection (as your string has it: Integrated Security=SSPI) should work fine.

    The following:

    <authorization>
           <allow users="MYDOMAIN\Group_Name"/>
           <deny users="*"/>
    </authorization>

    will restrict access to the website (not the database) to the group in question.  You may not want to do this.  If you have a large set of users that access the site but not the database in question (perhaps they use other portions of the site), and a smaller subset of users that should have access to the site and the database, you do not want to do this.

    John
    0
     

    Author Comment

    by:RichardFox
    I am getting strange results:

    On my IIS server, with the configuration

    <authentication mode="Windows" />
    <identity impersonate="true" />
       <authorization>
                 <allow users="*"/>
        </authorization>

    and connection string

    <add key="ConnectString" value="Data Source=sqlserver;Initial Catalog=acascrm;Integrated Security=SSPI;" />

    with anonymous access turned off, Inegrated Windows authentication on for the website,
    a Windows group given access to the database on the sqlserver and myself added to the group, my page runs correctly and my query returns the expected results.
    I can run a browser on the webserver or access the webserver from a browser on any machine and the query runs.. good thing or else I would be REALLY confused. But, other users (members on the group with access to the DB) who load the same page (and are promted to login, and do so) get

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Obviously their credentials are not getting passed to sqlserver. I can copy my aspx page to another webserver, configured the same way, recompile, and when I run the page I get this same error message when I log in. If I comment out the sql query from my page and just keep the code

    WindowsIdentity wi = System.Security.Principal.WindowsIdentity.GetCurrent();
    Response.Write("user: "+wi.Name.ToString()+"<br>");

    my group\user name are shown correctly.

    The original machine where the page works is my own development machine with Enterprise Manager installed, I have a profile, etc FYI.

    Other users have reported this behavior, in newsgroups, but no explanations have been offerred. I am very puzzled why it only works for me on only one webserver. Obviously there are variables of which I am not aware.

    raterus says I have to set up Delegation on the webserver, but my page is working for me on my machine without it. Nonetheless, I will do this if it is required. Some people say, in newsgroups, that ASP.NET will not pass through Windows credentials, forget about it. Other like yourselves propose a straightforward way to do it, and I have seen it work firsthand, if only on one machine and for one user. WHy this difference of opinion? Just curious.

    Anyway I will proceed to read the article proposed by raterus.

    Thoughts, please? and thanks for all your help, very informative

    Rich


    0
     

    Author Comment

    by:RichardFox
    I think I got it.. two more articles helped

    http://www.powerblue.net/forum/display_topic_threads.asp?ForumID=3&TopicID=29&PagePosition=1
    and
    http://www.win2000mag.com/SQLServer/Article/ArticleID/23670/23670.html

    When I pointed my browser not to my IP address but to my NETBIOS name (It may be necessary to register a "Service Principal Name" (SPN) for the IIS server using the Windows 2000 Resource Kit SetSPN tool if the web site name does not match the NetBIOS name of the server. (Q294382 Authentication May Fail with "401.3" Error If Web Site's "Host Header"...)

    I was able to delegate and my page works from any machine. Cheers!

    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    glad to hear it Richard;)
    0
     
    LVL 33

    Expert Comment

    by:raterus
    Shame I didn't know about EE when I had my first delegation experience, it took me over a week of confusion before I got it working!  My problem ended up being the SPN issue it appears you've already nailed!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.

    857 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now