Using SSPI in connection strings vs. a named SQL Server user

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:

integrated security=sspi

However, there are two situations where this fails or becomes problematic:

  1. In production, when web and database are on different physical machines
  2. 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.


0 Responses to “Using SSPI in connection strings vs. a named SQL Server user”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

TwitterCounter for @anthonyrstevens
Add to Technorati Favorites

RSS Feed

View Anthony Stevens's profile on LinkedIn

%d bloggers like this: