Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: Security questions.

    SQL2K SP4

    The apps that connect to my SQL DB's (for internet use) use SQL
    authentication (the app login so to speak). Anyways, a Java developer showed
    me that he built into his app a way to retrieve that app login and password
    from SQL Server. Obviously I wasn't very happy about this. So my questions:

    1; Does anyone know if the same thing can be done using .NET code?
    2; I've heard of a method using .Net Web Services (WS) for using WINNT
    authentication even for internet apps. It would be that the app calls a WS,
    that WS the passes in WIINT authentication to the DB and all is good. Has
    anyone else out there used this type of security? Is there a link you can
    provide?
    3; If number 2 is implemented, does it then eliminate the possibility of
    apps being able to retieve the sensitive information?

    TIA, ChrisR

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Use connection pooling and only use sproc access to the data
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In order to get the login information from the SQL Server system tables, he would have to log into SQL Server. If he had done that, didn't he already know the login name and password?

    As the Car Talk guys would say, this sounds bo-o-o-o-gus. At least on the surface.

  4. #4
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by Brett Kaiser
    Use connection pooling and only use sproc access to the data
    Too late to enforce only using sprocs to access the data. How would connection pooling help?

  5. #5
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by MCrowley
    In order to get the login information from the SQL Server system tables, he would have to log into SQL Server. If he had done that, didn't he already know the login name and password?

    As the Car Talk guys would say, this sounds bo-o-o-o-gus. At least on the surface.

    Well, sort of. We have a strange environment here. Developers write the code, QA changes the password in the config file, then migrates it into Production. So technically no, the developer doesn't (shouldn't) know the Production password.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I have seen your option 2 used plenty of times before. It has the benefit of not relying on sql auth, which is discouraged because it usually means you have passwords lying around in plaintext in web.config files. If you use windows auth in sql server, you won't have to worry about passwords in web.config as all the connstrs will be "trusted_connection=yes" which reveals nothing. You'll just have to grant access in your db to the nt account that the web service is running as.

    It certainly doesn't remove the possibility of apps being able to retrieve sensitive information however. they'll be able to get anything that the web service provides, so if the web service has perms to fetch protected data, the app can get it (albeit only through the web service).

    In general, you want to avoid as much as possible exposing sql server on the internet. it's much safer to expose it through a web service or similar, and then only expose the webmethods that clients need. don't expose a ExecuteSql(string sql) WebMethod, for example! Strive for a minimal, but complete interface in everything you do :}
    Last edited by jezemine; 10-10-06 at 17:29.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It should be easy to use an application pool, if the web application can run on windows 2003.

    I think I see how the developer did it. He is reading the config file in the java application, and displaying it. Depending on how sensitive this information is, this may have to be reported to managers/HR.

  8. #8
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by jezemine
    I have seen your option 2 used plenty of times before. It has the benefit of not relying on sql auth, which is discouraged because it usually means you have passwords lying around in plaintext in web.config files. If you use windows auth in sql server, you won't have to worry about passwords in web.config as all the connstrs will be "trusted_connection=yes" which reveals nothing. You'll just have to grant access in your db to the nt account that the web service is running as.
    Thats what I was looking for, thanks! The developers app queries the web.config and gets the password.

    Quote Originally Posted by jezemine
    It certainly doesn't remove the possibility of apps being able to retrieve sensitive information however. they'll be able to get anything that the web service provides, so if the web service has perms to fetch protected data, the app can get it (albeit only through the web service).
    I know that the web service will be able to get sensitive data and thats fine. My concern is more people getting this datafrom outside the app (Access, etc.), as well as the password becoming public knowledge.

  9. #9
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by MCrowley
    It should be easy to use an application pool, if the web application can run on windows 2003.

    I think I see how the developer did it. He is reading the config file in the java application, and displaying it. Depending on how sensitive this information is, this may have to be reported to managers/HR.

    That's exactly what he's doing. What will an app pool do for me?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    An app pool can be configured to log on with a Windows account, just like a service. You can then grant the application's required permissions to that Windows account. That windows account does not even have to be mentioned in the application's config files, I believe.

    Still. I would report this developer at least to managers in your department. There is no telling what other back doors he has left himself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •