Change Windows credentials when connecting to a database using Windows Authentication

Image you have a two accounts in Active Directory. You have one that you log into Windows (call it BasicUser1 for example). The other is one that you use for development and is the account (call it DevUser1 for example) that you need to use to access a MS SQL Server database using Windows Authentication.

Now when running SSMS (SQL Server Management Studio), LINQPad, Visual Studio, etc and trying to connect to a database that requires Windows Authentication (using DevUser1), but you are logged into Windows as BasicUser1 which doesn't have permissions to the database.

The problem is how do we impersonate DevUser1 when connecting to the database using Windows Authentication. The answer is actually pretty simple and seamless once configured.

The answer is the Credential Manager built into Windows. You can find it in the start menu, but you can also run it directly using:

control /name Microsoft.CredentialManager

It looks something like this:

Click the Add a Windows credential link.

Enter the fully qualified server name, etc AND the port for the SQL Server database (the default is 1433). The username should be in the format domain\username. The password is the password for the specified user.

Now when you connect to the database with Windows Authentication Windows will automatically pass the credentials specified in the Credential Manager, not the ones you are currently logged into Windows as.