Trusted Connection, SQL Server, and ASP.NET

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
RichardFoxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AerosSagaCommented:
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
AerosSagaCommented:
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
daffodilsCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Aeros
0
daffodilsCommented:
>>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
raterusCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jnhorstCommented:
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
RichardFoxAuthor Commented:
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
RichardFoxAuthor Commented:
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
AerosSagaCommented:
glad to hear it Richard;)
0
raterusCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.