SQL Connectivity by Web App

Discussion in 'ASP .Net Security' started by Guest, Jun 21, 2004.

  1. Guest

    Guest Guest

    Hi,

    To start with, i'm not too sure if this is the right group to be posting in
    so please let me know if these is a more suitable one.

    I am trying to get my head around the mechanics behind .NET web applications
    accessing SQL databases located on another pc. My current scenario is that
    both PC's (IIS5 and SQL2000) are on the same domain but are physically
    different. I wish to retrieve data from the database to include in the
    asp.net application. As far as I can see I have two options when building
    the connection:

    a) pass credentials in a connection string;
    b) change the impersonated user to a domain account with sql priivaleges.

    I notice that the SQL box is set to use windows authentication. Does this
    make a difference?

    What I am looking for is are links to resources discussing the matter to
    determine which method is best, if the are other methods and how to set up
    the solution.

    Any help would be appreciated.

    Regards,

    <M>ike
     
    Guest, Jun 21, 2004
    #1
    1. Advertisements

  2. Guest

    Alek Davis Guest

    Mike,

    The most common approach to this common problem is to specify SQL
    credentials in the connection string. The downside here is that you need to
    protect the connection string at storage, which is a challenge, but unless
    you have certain specific conditions, you do not have many alternatives. If
    you want to propagate user's credentials to SQL server, you have to enable
    delegation at the AD domain level, which is not a good idea from security
    perspective. And what is worse, your app will not be able to use connection
    pooling, so the scalability goes down he drain. If you want to connect to
    SQL Server using credentials of the IIS worker process, you either need to
    run the IIS process as a domain user or set them identically on both the SQL
    serve and Web server using a local account (with the same password). I don't
    think that either of these options is good, because if you do this (for one,
    any application running under your Web site will be able to connect to SQL
    server with privileged rights). The bottom line here is that you will
    introduce more problems than you solve. Just go with the SQL credentials in
    the connection string. From my experience, this is what most enterprise apps
    do.

    Alek
     
    Alek Davis, Jun 21, 2004
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Thanks AleK,

    That certainly sounds good and reinforces what I was thinking. I think I
    will try to save the credentials in the Web.config file as AppSetting keys
    so they are not saved in each page and should be more secure.

    Cheers,

    <M>ike
     
    Guest, Jun 22, 2004
    #3
  4. Guest

    Guest Guest

    Top Tip:

    I've also noticed that if you're using passed credentials to connect to the
    SQL box it helps if the server is set to accept both SQL Server and Windows
    authentication, otherwise you kep getting (and getting and getting) the good
    old 'Not a Trusted Connection' error message.

    <M>ike
     
    Guest, Jun 22, 2004
    #4
  5. Guest

    Alek Davis Guest

    Just make sure that the credentials are encrypted.

    Alek
     
    Alek Davis, Jun 22, 2004
    #5
  6. Guest

    Guest Guest

    Any top tips on how to encrypt these credentials. I guess the details are
    decrypted by a function in a class within the project every time they are
    needed?

    <M>ike
     
    Guest, Jun 23, 2004
    #6
  7. Guest

    Maras Guest

    Read about aspnet_setreg tool.
     
    Maras, Jun 23, 2004
    #7
  8. Guest

    Alek Davis Guest

    This is a tough problem to solve and depending on your environment some
    approaches may be worse than others. Check the "Protect It: Safeguard
    Database Connection Strings and Other Sensitive Settings in Your Code"
    article at http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/,
    it addresses this topic.

    Alek
     
    Alek Davis, Jun 23, 2004
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.