Previously I’ve documented How To Give Machine Access To SQL Server for a frictionless or password-less development experience leveraging Windows Authentication capabilities.
When I wrote that article, my development database was hosted on a remote SQL server and recently we’ve made a decision to move those development servers to our local machines. While I was working on migrating my database, I’ve hit a wall. Machine Access authentication simply wouldn’t work on my local SQL database.
Worse, the errors I was getting were cryptic at best.
Login Failed For User
Login failed for user ‘domain/computername$’.
After proving that I’m insane, by trying the same things over and over again and expecting a different outcome, I decided to dig deeper. Looking at SSMS error logs (Object Explorer –> Management –> SQL Server Logs), things got weirder.
Error: 18456, Severity: 14, State: 5.
A quick Google search revealed State 5 means Invalid User Id. Here’s the source: Understanding "login failed" (Error 18456) error messages in SQL Server 2005.
I thought this was impossible, my machine existed as a proper login, and I’m on the machine, how could it be invalid.
Then I stopped thinking logically and arrived at a solution.
The Workaround
The workaround for this, as documented in How to: Access SQL Server Using Windows Integrated Security, is to modify your web.config file to include:
<system.web><identityimpersonate="true"/> …
This is will use your Windows Identity instead, when you login through the browser, but at least things will work.
You can easily remove impersonation in your Release build, by adding a transformation. See the example below:
<?xmlversion="1.0"?><configurationxmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform"><system.web><identityxdt:Transform="Remove"/></system.web></configuration>
TL;DR
For whatever reason machine access with windows authentication doesn’t work with local databases. Impersonation is the answer.