Post reply

SQL login name practices - development vs production

  • August 1, 2015 at 12:19 pm

    #310521

    Hello experts,

    I know the general best practice for SQL Server is to use Windows logins, but we're not there yet. My question is, is it OK for those SQL logins in development to have the same name as production (I assume the passwords should be different) or is it better practice to differentiate the development logins to clarify whether a login is being used live or in test?

    I'm just trying to weigh whether there is any real security benefit that would balance the extra work it seems like it would take to handle two different sets of naming conventions for the logins (well, three if we include staging, but staging is supposed to match production closely as I understand it).

    Thanks for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • August 2, 2015 at 10:08 am

    #1816672

    webrunner (8/1/2015)

    I know the general best practice for SQL Server is to use Windows logins, but we're not there yet.

    I know you already know this but I have to say it out loud just to make myself feel better. If you enable people to do things the wrong way to "get the job done", they will never take time to go back and do it the right way.

    With the disclaimer than I'm not a Windows networking or security expert, consider that that these people are already able to login to your Windows Network. That means that all you have to do is set them up as Windows Logins instead of creating SQL Server logins for them. Of course, it would also be best if you setup various Windows groups but that's not absolutely essential,

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 2, 2015 at 3:42 pm

    #1816702

    Jeff Moden (8/2/2015)

    webrunner (8/1/2015)

    I know the general best practice for SQL Server is to use Windows logins, but we're not there yet.

    I know you already know this but I have to say it out loud just to make myself feel better. If you enable people to do things the wrong way to "get the job done", they will never take time to go back and do it the right way.

    With the disclaimer than I'm not a Windows networking or security expert, consider that that these people are already able to login to your Windows Network. That means that all you have to do is set them up as Windows Logins instead of creating SQL Server logins for them. Of course, it would also be best if you setup various Windows groups but that's not absolutely essential,

    Thanks, Jeff! I see your daily contributions to SSC and greatly respect your advice. I just want to add a clarification and a follow-up question.

    Clarification: Yes, the users (developers) already have Windows logins, so we use those for them whenever possible. My dilemma is more with the service logins that are requested for applications to connect to SQL Server. Because we have many applications on Unix-style servers, they want SQL logins for those services. I think someone on SSC once advised me that even Unix/Linux can be set up to use Windows authentication, but I don't know if that is true. And even if it is, if my place chooses not to go that route, I would still be called upon the create the SQL logins.

    Follow-up question: If it remains the case that the SQL logins are still used, does it make sense to name them differently across the environments, such as WidgetEditor (prod), DWidgetEditor (dev), and SWidgetEditor (staging)? I would prefer to have them all use the same name but not if there is a good reason for naming them differently.

    Thanks again for your help!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • August 3, 2015 at 9:25 am

    #1816857

    In the environments I have worked in the SQL logins used by applications have always had the same name \ permissions in each environment the only differentiation being the passwords.

    I don't see any benefit from a security perspective in having them different with the exception of the SA account.

    MCITP SQL 2005, MCSA SQL 2012

  • August 3, 2015 at 9:45 am

    #1816868

    RTaylor2208 (8/3/2015)

    In the environments I have worked in the SQL logins used by applications have always had the same name \ permissions in each environment the only differentiation being the passwords.

    I don't see any benefit from a security perspective in having them different with the exception of the SA account.

    Thanks - I'll go with this as the solution but thanks to Jeff as well for his response!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • August 3, 2015 at 9:46 am

    #1816869

    webrunner (8/2/2015)

    Jeff Moden (8/2/2015)

    webrunner (8/1/2015)

    I know the general best practice for SQL Server is to use Windows logins, but we're not there yet.

    I know you already know this but I have to say it out loud just to make myself feel better. If you enable people to do things the wrong way to "get the job done", they will never take time to go back and do it the right way.

    With the disclaimer than I'm not a Windows networking or security expert, consider that that these people are already able to login to your Windows Network. That means that all you have to do is set them up as Windows Logins instead of creating SQL Server logins for them. Of course, it would also be best if you setup various Windows groups but that's not absolutely essential,

    Thanks, Jeff! I see your daily contributions to SSC and greatly respect your advice. I just want to add a clarification and a follow-up question.

    Clarification: Yes, the users (developers) already have Windows logins, so we use those for them whenever possible. My dilemma is more with the service logins that are requested for applications to connect to SQL Server. Because we have many applications on Unix-style servers, they want SQL logins for those services. I think someone on SSC once advised me that even Unix/Linux can be set up to use Windows authentication, but I don't know if that is true. And even if it is, if my place chooses not to go that route, I would still be called upon the create the SQL logins.

    Follow-up question: If it remains the case that the SQL logins are still used, does it make sense to name them differently across the environments, such as WidgetEditor (prod), DWidgetEditor (dev), and SWidgetEditor (staging)? I would prefer to have them all use the same name but not if there is a good reason for naming them differently.

    Thanks again for your help!

    - webrunner

    There's an advantage to having them all be named the same in that no code would ever need to be changed. However, it is a HUGE risk in having them named the same in that it's way too easy to make a mistake. There's nothing worse than someone thinking that they're in the dev environment when they actually pointing to prod or vice versa. My recommendation would be to have separate names not only for the logins but also for the databases. Enforce the "2 part naming convention" in all code and if references external to the database is required, use synonyms. I can't prove the value of doing this except to say that it has absolutely saved our keesters on more than one occasion. It's a little bit more difficult but well worth it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 3, 2015 at 9:52 am

    #1816876

    Jeff Moden (8/3/2015)

    webrunner (8/2/2015)

    Jeff Moden (8/2/2015)

    webrunner (8/1/2015)

    I know the general best practice for SQL Server is to use Windows logins, but we're not there yet.

    I know you already know this but I have to say it out loud just to make myself feel better. If you enable people to do things the wrong way to "get the job done", they will never take time to go back and do it the right way.

    With the disclaimer than I'm not a Windows networking or security expert, consider that that these people are already able to login to your Windows Network. That means that all you have to do is set them up as Windows Logins instead of creating SQL Server logins for them. Of course, it would also be best if you setup various Windows groups but that's not absolutely essential,

    Thanks, Jeff! I see your daily contributions to SSC and greatly respect your advice. I just want to add a clarification and a follow-up question.

    Clarification: Yes, the users (developers) already have Windows logins, so we use those for them whenever possible. My dilemma is more with the service logins that are requested for applications to connect to SQL Server. Because we have many applications on Unix-style servers, they want SQL logins for those services. I think someone on SSC once advised me that even Unix/Linux can be set up to use Windows authentication, but I don't know if that is true. And even if it is, if my place chooses not to go that route, I would still be called upon the create the SQL logins.

    Follow-up question: If it remains the case that the SQL logins are still used, does it make sense to name them differently across the environments, such as WidgetEditor (prod), DWidgetEditor (dev), and SWidgetEditor (staging)? I would prefer to have them all use the same name but not if there is a good reason for naming them differently.

    Thanks again for your help!

    - webrunner

    There's an advantage to having them all be named the same in that no code would ever need to be changed. However, it is a HUGE risk in having them named the same in that it's way too easy to make a mistake. There's nothing worse than someone thinking that they're in the dev environment when they actually pointing to prod or vice versa. My recommendation would be to have separate names not only for the logins but also for the databases. Enforce the "2 part naming convention" in all code and if references external to the database is required, use synonyms. I can't prove the value of doing this except to say that it has absolutely saved our keesters on more than one occasion. It's a little bit more difficult but well worth it.

    Ha, thanks Jeff, now I have to mark this as the solution too! I can see both sides, but I hadn't even thought of the database-naming part as well.

    I still have some mulling over to do but thanks for the advice.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 7 posts - 1 through 6 (of 6 total)

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