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.