The question for the month of June seems deceptively easy:
What issues are exposed when using SSPI authentication? How does one avoid these issues?
When I initially wrote this question, I was thinking about SQL Server Reporting Services reports and the data source connections they establish, but the question also applies to applications of all kinds that connect to data sources. As you (should) know, SSPI (or “trusted”) connections use the currently logged in Windows system credentials to pass along to the data source. The trusted approach precludes the need to use hard-coded (or generated) data source-dependent login names and passwords. With SSPI/trusted authentication, if the Windows user has a login account on the target SQL Server (or other data source), the connection is permitted to be (further) authenticated. No, the data source might not authenticate the connection if the user does not have rights on the initial catalog (default database), or if the server is too busy to take on additional connections, but that’s another issue.
- The question is, do you know what issues are exposed when you use this trusted connection approach?
- What are the alternatives and why should they be considered?
- What are the downsides to these alternatives?
Want a hint? Check out Chapter 9 of my 7th Edition.
Note that you are not permitted to post answers to this question until June 1st. 2011.
So, it seems that most responders to this question think that SSPI is the way to go. Here is another hint: Consider that SQL Server permits an application to submit any number of TSQL operations in a single query.