How should developers connect to SQL Server?

Question

We're a team of 4 developers that all have access to all servers (dev, test, uat, production) and we are trying to choose the best (secure, reliable, simple etc) way to connect SQL Server (currently 2008 R2).

Things we considering:

• Windows / SQL Server authentication?
• What roles?
• Same for all servers or different for the production servers?

Please let me know what you do and what you recommend.

Answer 1

If you all require the same permissions, assuming that you are working on a domain and the servers are also on the domain.

1. Create a Developers group on the windows domain.
2. Add the 4 developers to that group.
3. Create a Login on each sql server mapped to that group. CREATE LOGIN [domain\group] FROM WINDOWS
4. Assign permissions to that login, depending on what you need.

Then a new developer just needs to be added to the domain group to get the permissions to all the servers. Also any changes to permissions only have to be done once to that group.

To answer your considerations.

• You should use Windows authentication over SQL Authentication where possible.
• The roles you need and whether to have them the same on all servers can only be answered by yourselves IMO, but you should use the principle of least privilege and give only the minimum permissions required by the developers per server.

Answer 2

What we use:

Dev/test servers - Windows Authentication for ease and simplicity

UAT - SQL Server authentication (because it's usually in a different domain)

Production - No direct access - you have to use RDP. You don't want anyone accidentally connecting to production when they think they're connecting to something else.