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.

This is driving me crazy. I have a stored procedure script that I can compile either from within SQL Server Management Studio or within Visual Studio 2005. When I compile from within SQL Management Studio, and then run the SP, i.e.

exec student_progress_report_get_by_student 500791

I get the right results (256 rows in this case). When I go into Visual Studio 2005 and select “Run On” to compile the stored procedure, the subsequent execution of that stored procedure (from either within SQLMS or via the web app interface) returns zero results!

I have spent the last hour building the proc side-by-side, both editors open, confirming it’s the right file, isolating anything out, such as a view, that might skew the results, but I can still reliably demonstrate the problem. I am guessing that since I’ve been awake since 4:30 my reptile brain is too tired to figure out the obvious, but this is one of those situations for which the phrase “going postal” seems apt.

UPDATE: this turned out to do with how the VS 2005 “compiler” deals with ANSI_NULLS. I’m not sure if there’s a default setting somewhere, and I’m too lazy to look, but when I explicitly set the ANSI_NULLS option in the script file using the statement:

set ansi_nulls on

… my problem became fixed. VS 2005 had ansi_nulls OFF by default.

Moral: always check your assumptions, and track everything down. Software doesn’t just stop working — there’s always a reason for everything.