Just ran into a little problem, thought I’d share:
My connection string is set up to use a Trusted Connection, with this value set:
However, there are two situations where this fails or becomes problematic:
- In production, when web and database are on different physical machines
- Running unit tests locally
#1 is described here. #2 is more, um, “crampy” — you can easily enough add your local user account (in my case, <machinename>\Anthony) to the SQL Server db_owners group for your database, but is that really the best way to do it? I suppose that in most cases the dev user account will be a local admin, and thus already have all the required permissions without any manual intervention, but that’s why I made up the term “crampy” — maybe it doesn’t feel quite right, but it won’t kill you either.
If you keep SSPI but either (a) don’t login as a local admin or (b) don’t add your local account to db_owners, you get this error:
System.Data.SqlClient.SqlException : Cannot open database "cdb" requested by the login. The login failed. Login failed for user '<machinename>\Anthony'.
A different vendor I work with doesn’t use trusted connections, but rather sets up a distinct user login in SQL Server. With that route, the problem becomes one of encrypting the password in your machine.config / web.config file. We used to do it that way, but without any encryption, thus getting the worst of all possible scenarios.
I may opt for the named-user-with-encryption approach. More later.