The Case for SQL Logins - Part 1

  • barryb01

    Valued Member

    Points: 59

    We use SQL Logins primarily and this gives us the problem of not knowing the Win NT user when there is a user creating problem in the database.

  • johnsonj

    SSCrazy

    Points: 2159

    I agree with you on this topic. I have been using a single sql login with the userid's managed by the application for years (since 4.5).

    It was the most logical fit, especially since the domain admins are in a separate working group. For remote support of an application, it allows much more functionality and you can have a single point of support instead of "passing the buck".


    Joe Johnson
    NETDIO,LLC.

  • well0549

    SSCrazy

    Points: 2259

    I read the article again, and i still strongly disagree with you. No user whatsoever should have access to a database. So the problem of a user executing a stored proc will not arise. Only the SA should have access in a database. This is the only safe way.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716216

    I think you misposted. Users need and should have access to a database. Otherwise, what is the point. Not all apps can use stored procedures. Even if they do, a user needs access.

    I would say that they should not have DBO access and carry it further to say that very, very few people should have dbo or sa access to a server.

    Steve Jones

    steve@dkranch.net

  • Terrie

    Ten Centuries

    Points: 1243

    Good article. I'd like to read more.

  • AllyAnneA

    SSC Eights!

    Points: 944

    Andy's arguments for using SQL accounts applies to *applications*... but what about controlling administrator access? In my organization, we have different levels of people who are in different server roles (e.g., full sysadmins vs. dbcreators). While we could have separate logins for each server role, it's easier to manage them using Windows accounts. Plus, if a DBA leaves, there are no password changes to worry about.

  • bmsjr

    Ten Centuries

    Points: 1199

    I like having both types of authentication. While I agree with access from the web SQL logins are best. I think the Windows Login allows for some useful functionality with tools etc. You can code without the use of a login which allows tools that are run against SQL to only be used by domain admins. The tools can be accessible but having the tool does not automatically gain access to the server.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • retreif

    Old Hand

    Points: 321

    We control WA with a resource domain that only SQL servers are a part of with one way trust from the production domain. combine this with application roles and we feel confortable here. Well, as comfortable as you can with security...

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716216

    AllyAnne,

    NT Auth is nice in that the DBA group can be managed as it applies to the entire group. There are no password changes here either. If you use SQL Auth and a DBA leaves, without a doubt you should have a password change or an account deletion.

    Unless I am misreading.

    Steve Jones

    steve@dkranch.net

  • AllyAnneA

    SSC Eights!

    Points: 944

    Steve,that's my point--NT Authentication would be preferable for DBAs. (Sorry that was not clear.)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716216

    Ahhh, then I agree ().

    I'm usually for SQL Logins, they are easier to deal with and it's easy for NT permissions to get out of hand in any size org. That being said, they do provide extra effort and for System Admin tasks, I think they are great.

    Steve Jones

    steve@dkranch.net

  • ricky_artigas

    SSC Enthusiast

    Points: 107

    Different strokes for different folks! I believe how you will do it will depend on your specific environment. Sometimes it's better to have WA, sometimes it's not. I believe this is why Microsoft has introduced "application roles".

    - Ricky Artigas

    ricky_artigas@yahoo.com

  • AnnBarron

    Grasshopper

    Points: 15

    I agree with you. Windows Authentication is not always the way to go. We have ORACLE and SQL Server databases. These databases pass data back and forth to each other. The only way to do this is with SQL Logins, not WA.

    Ann L. Barron

    Database Administrator

    HomeSide Lending, Inc.

    albarron@homeside.com

    (904) 281-3471


    Ann L. Barron
    SQL Server Database Administrator
    Washington Mutual Bank, FA
    ann.barron@wamu.net
    (904) 281-3471

  • ckempste

    SSCoach

    Points: 17983

    Hey Andy, what will people win for reviewing one of my articles?? ;))

    Some interesting points throughout the article. I run a range of sites where we utilise 3 db logins to a 300 table schema, each login basic segregates the model into into "subject areas" which are accessed via COM's at the business layer and are shared by 000's of end users who authenticate via active-dir. This model is very easy to administer, and the actual "user privs" to the applications screen components are managed "in-code" using a variety of tables. Auditing is throughout via triggers.

    The only issue to watch out for is hiding the connectivity info, a text file INI is simply bad practice. I know of a few sites where hackers have gained entry to the server as a lowly user but gain access quickly to the DB as the INI has all the passwords to get in!

    What I will say though, is that I do like to use win authentication for sysadmin access and rarely utilise the SA account.

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119676

    Just have to be creative!

    I agree about the ini file. Not sure I have a perfect solution, but I think I can offer something a little sturdier in the follow up article.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

Viewing 15 posts - 16 through 30 (of 76 total)

You must be logged in to reply to this topic. Login to reply