Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: SQL Authentication vs. Windows

    We currently use SQL Authentication and have generic logins per application (each application has exactly 1 login).

    Pros
    - Makes application configuration easy
    - Management of security accounts within SQL simple

    Cons
    - Relies on the application to pass a real "UserID" to the application for auditing purposes
    - Permission-ing is an extra layer

    Is this how most people implement security on their DB? Is there a best practices based on number of application users?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The MS position is Windows. I'm always nervous that, in this situation, a windows user with a copy of Access (or SSMS or any programming language etc), minimal server info and half a brain can access the database.
    In principle I prefer windows authentication apart from the above issue. As such, I typically use a specific account for the application (be it a domain account or SQL account).

    I read a pretty good article setting out the same stall as above (but much better) quite some time ago. I'll see if I can dig it out.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    The MS position is Windows. I'm always nervous that, in this situation, a windows user with a copy of Access (or SSMS or any programming language etc), minimal server info and half a brain can access the database.
    this is only an issue if security is poorly implemented.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...which, of course, is NEVER an issue....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    this is only an issue if security is poorly implemented.
    How do you define poorly implemented?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by blindman
    ...which, of course, is NEVER an issue....
    not when I am driving the DBA bus. anytime I walk in some place new, this is the 2nd thing I check out after disaster recovery.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    How do you define poorly implemented?
    ooohhh I dunno. Everyone's network login is a a group giving them dbo access. when data can be accessed outside of stored procedures, views, functions that you can place explicit permissions on.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well the dbo access NEVER happens. However - tables access...in theory no. In practice, unfortunately (and usually due to dynamic sql limitiations) sometimes IS granted although I can only think of examples involving select (not any other dml operations).

    But what about sprocs? In the world of security "Well I never thought he\ she would start firing off sprocs via Access...." doesn't really wash does it? I think I am right in saying (but now realise I need to check) that if one has permissions to exec a sproc one can also view its contents. And forget encryption - encryted sprocs can be easily decrypted.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you ever work or are you just going to chase me around today?

    I know in SQL 2K, you have to be a sysadmin, dbo or a ddladmin to use ALTER PROC. I dunno why you would be able to see procs, you can't change.

    It will be something cool to test if I had SQL Server installed at the moment.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by pootle flump
    .... And forget encryption - encryted sprocs can be easily decrypted.
    Except for signed ones
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    not when I am driving the DBA bus. anytime I walk in some place new, this is the 2nd thing I check out after disaster recovery.
    If you think you are driving the bus, you are either the CIO or you are mistaken.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    do you ever work or are you just going to chase me around today?
    I rolled the dice this morning and you were the lucky recipient of my attentions

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm rather fond of using Windows Authentication to control whether or not the user can get to the database, then using CREATE ROLE to give the application super-powers. They let me combine the best of both worlds easily.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rdjabarov
    Except for signed ones
    Good point Robert

    Quote Originally Posted by Pat Phelan
    I'm rather fond of using Windows Authentication to control whether or not the user can get to the database, then using CREATE ROLE to give the application super-powers. They let me combine the best of both worlds easily.
    Sorry Pat - could you go into more detail? How do you "give the application super-powers" and not the domain account?

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    give the NT accounts read permissions and the app account read\write?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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