Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trusted Connection, SQL Server, and ASP.NET

Posted on 2004-10-21
11
Medium Priority
?
1,179 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
Comment
Question by:RichardFox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 400 total points
ID: 12372174
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
ID: 12372200
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
daffodils earned 400 total points
ID: 12372469
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:AerosSaga
ID: 12372494
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
ID: 12372505
>>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:
raterus earned 800 total points
ID: 12373867
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
jnhorst earned 400 total points
ID: 12378451
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
ID: 12380230
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
ID: 12381492
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
ID: 12381516
glad to hear it Richard;)
0
 
LVL 33

Expert Comment

by:raterus
ID: 12381537
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

610 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