Database connection

Discussion in 'ASP .Net Security' started by Filip, Jun 6, 2005.

  1. Filip

    Filip Guest

    Hi,

    I have a website runnning on Windows 2003 Web Server edition that needs to
    connect to an MS SQL2000 database.
    The web server is NOT part of the domain, but can talk to my database via
    IP and retrieve data when using SQL server login.
    This however means there is a User ID /Password in clear text.

    I would like to use SSPI, so I did the following:

    1. created local account on my Web server with known password
    2. using aspnet_setreg I encrypted and inserted the User ID/Password into
    registry
    3. ACL set on the registry key to Read
    4. In Web config I set
    <identity impersonate="true"
    userName="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,userName"
    password="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,password"
    />
    User is being correctly impersonated
    5. I gave permissions to my new user to have access to files/folders required
    by ASP.NET
    6. Created "mirrored" local account on my database server.

    However, when I run a page that contains database connection/data retrieval
    I get the following error:

    "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
    Server connection."

    obviously my User ID / Password are not being passed through.

    Can anybody suggest, what I need to do, obviously I don't want to have the
    User ID and Password in clear text.
    Please keep in mind Web server and Database server are NOT in tha same domain
    (can't use domain logins!)

    Thanks in advance,
    Fili
     
    Filip, Jun 6, 2005
    #1
    1. Advertising

  2. Are you sure the impersonation is working correctly? How did you verify
    this? This would be the first thing I would check.

    --

    - Paul Glavich
    ASP.NET MVP
    ASPInsider (www.aspinsiders.com)


    "Filip" <> wrote in message
    news:...
    > Hi,
    >
    > I have a website runnning on Windows 2003 Web Server edition that needs to
    > connect to an MS SQL2000 database.
    > The web server is NOT part of the domain, but can talk to my database via
    > IP and retrieve data when using SQL server login.
    > This however means there is a User ID /Password in clear text.
    >
    > I would like to use SSPI, so I did the following:
    >
    > 1. created local account on my Web server with known password
    > 2. using aspnet_setreg I encrypted and inserted the User ID/Password into
    > registry
    > 3. ACL set on the registry key to Read
    > 4. In Web config I set
    > <identity impersonate="true"
    >

    userName="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,userName"
    >

    password="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,password"
    > />
    > User is being correctly impersonated
    > 5. I gave permissions to my new user to have access to files/folders

    required
    > by ASP.NET
    > 6. Created "mirrored" local account on my database server.
    >
    > However, when I run a page that contains database connection/data

    retrieval
    > I get the following error:
    >
    > "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
    > Server connection."
    >
    > obviously my User ID / Password are not being passed through.
    >
    > Can anybody suggest, what I need to do, obviously I don't want to have the
    > User ID and Password in clear text.
    > Please keep in mind Web server and Database server are NOT in tha same

    domain
    > (can't use domain logins!)
    >
    > Thanks in advance,
    > Filip
    >
    >
     
    Paul Glavich [MVP ASP.NET], Jun 6, 2005
    #2
    1. Advertising

  3. Hello Paul Glavich [MVP ASP.NET],

    try inspecting WindowsIdentity.GetCurrent() after impersonating - this little
    tools may also help:

    http://www.develop.com/technology/resourcedetail.aspx?id=00e7be6c-69a1-44a5-8790-7969ea94df4d

    ---------------------------------------
    Dominick Baier - DevelopMentor
    http://www.leastprivilege.com

    > Are you sure the impersonation is working correctly? How did you
    > verify this? This would be the first thing I would check.
    >
    > "Filip" <> wrote in message
    > news:...
    >> Hi,
    >>
    >> I have a website runnning on Windows 2003 Web Server edition that
    >> needs to
    >> connect to an MS SQL2000 database.
    >> The web server is NOT part of the domain, but can talk to my database
    >> via
    >> IP and retrieve data when using SQL server login.
    >> This however means there is a User ID /Password in clear text.
    >> I would like to use SSPI, so I did the following:
    >>
    >> 1. created local account on my Web server with known password
    >> 2. using aspnet_setreg I encrypted and inserted the User ID/Password
    >> into
    >> registry
    >> 3. ACL set on the registry key to Read
    >> 4. In Web config I set
    >> <identity impersonate="true"

    > userName="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,userNa
    > me"
    >
    > password="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,passwo
    > rd"
    >
    >> />
    >> User is being correctly impersonated
    >> 5. I gave permissions to my new user to have access to files/folders

    > required
    >
    >> by ASP.NET
    >> 6. Created "mirrored" local account on my database server.
    >> However, when I run a page that contains database connection/data
    >>

    > retrieval
    >
    >> I get the following error:
    >>
    >> "Login failed for user '(null)'. Reason: Not associated with a
    >> trusted SQL Server connection."
    >>
    >> obviously my User ID / Password are not being passed through.
    >>
    >> Can anybody suggest, what I need to do, obviously I don't want to
    >> have the
    >> User ID and Password in clear text.
    >> Please keep in mind Web server and Database server are NOT in tha
    >> same

    > domain
    >
    >> (can't use domain logins!)
    >>
    >> Thanks in advance,
    >> Filip
     
    Dominick Baier [DevelopMentor], Jun 6, 2005
    #3
  4. Hello Filip,

    another approach would be (and IMO a much better)

    1. configure your worker process identity to a custom account (via the AppPool
    feature in IIS6)
    2. create a "mirrored" account for the app pool account on the sql box
    3. give SQL access to this account
    4. don't impersonate
    5. connect to SQL

    ---------------------------------------
    Dominick Baier - DevelopMentor
    http://www.leastprivilege.com

    > Hi,
    >
    > I have a website runnning on Windows 2003 Web Server edition that
    > needs to
    > connect to an MS SQL2000 database.
    > The web server is NOT part of the domain, but can talk to my database
    > via
    > IP and retrieve data when using SQL server login.
    > This however means there is a User ID /Password in clear text.
    > I would like to use SSPI, so I did the following:
    >
    > 1. created local account on my Web server with known password
    > 2. using aspnet_setreg I encrypted and inserted the User ID/Password
    > into
    > registry
    > 3. ACL set on the registry key to Read
    > 4. In Web config I set
    > <identity impersonate="true"
    >
    > userName="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,userNa
    > me"
    >
    > password="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,passwo
    > rd"
    > />
    > User is being correctly impersonated
    > 5. I gave permissions to my new user to have access to files/folders
    > required
    > by ASP.NET
    > 6. Created "mirrored" local account on my database server.
    > However, when I run a page that contains database connection/data
    > retrieval I get the following error:
    >
    > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > SQL Server connection."
    >
    > obviously my User ID / Password are not being passed through.
    >
    > Can anybody suggest, what I need to do, obviously I don't want to have
    > the
    > User ID and Password in clear text.
    > Please keep in mind Web server and Database server are NOT in tha same
    > domain
    > (can't use domain logins!)
    > Thanks in advance,
    > Filip
     
    Dominick Baier [DevelopMentor], Jun 6, 2005
    #4
  5. Filip

    Filip Guest

    Hello Dominick,

    Thank you very much for the info and the aspx page.
    Looks like the issue was elsewhere, but I will give the "AppPool" a go.

    Filip
     
    Filip, Jun 7, 2005
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mythran
    Replies:
    5
    Views:
    4,952
    Mythran
    Oct 5, 2005
  2. Dave Johnson

    Using Connection Class VS Connection Module

    Dave Johnson, Dec 9, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    634
    Dave Johnson
    Dec 11, 2005
  3. Linus Nikander
    Replies:
    5
    Views:
    545
  4. Replies:
    3
    Views:
    40,284
    Roedy Green
    Nov 16, 2005
  5. R
    Replies:
    1
    Views:
    574
Loading...

Share This Page