Give Machine Access to SQL Server 2008, R2, 2012 & Up

UPDATE: See my new blog post on How to Work with Windows Authentication and Local Databases.

Motivation

During development giving database access to a large team of developers can become challenging.

It’s a chore to setup SQL Logins and things only get more convoluted if you try to use AD/domain accounts. Some enterprises also try to make things more secure by updating passwords in regular intervals, which adds up to the inconvenience.

SQL Logins are a bad idea, because it encourages plain text passwords to be stored in config files (worse checked in to a repository) and inevitably left around the office on white boards or post-its.

If you use Windows Authentication, you can get rid of all passwords. Your application will use your machines identity to try to login to the SQL Server. If you give your computer access to Sql Server, then you won’t have to worry about config files or passwords. Below is how you can achieve this.

How To

1. Find out your machine name

Windows 7: From Start search for “computer name”, click on “See the name of this computer”

You’ll find Computer Name on the red circled line.

2. Create a new login in SQL

Launch Sql Server Management Studio (SSMS). I used the 2012 version for screenshots. Connect to the database you need. Hopefully you’re using Windows Authentication for that as well.

Create a new Windows Authentication login with name “domain/computername$”, where domain is your AD group name and computer name is the one you just grabbed on Step 1. It is crucial to add $ to the end. Then just hit OK and the new login should be created.

3. Give the appropriate permissions to a database

Afterwards, you can right-click on the new login and select Properties. On the User Mapping page, you can map the user to a database you wish and give them db_datareader and db_datawriter roles.

You can then observe the Properties of the database in question and have more granular access control in the Permissions page.

Wrap Up

By this point you have already forgotten that su password you’ve been using. Enjoy pain-free database access during development.

Wait, but if you’re working on a large team, and you use a personal database, you still have the risk of accidentally checking in/pushing an incorrect connection string. Please see my colleague Shahed Chowdhuri’s excellent blog post on how to solve that issue: http://wakeupandcode.com/all-your-database-are-belong-to-us/.