If all my sql server database access is done thru stored procedures, and I plan on continuing that practice, is using linq2sql and/or the entity framework for future projects an unnecessary layer of complexity that doesn't add much value?
Related question: is Microsoft trying to steer developers away from relying on stored procs for data access?
No. LINQ2SQL adds a lot of value in terms of being able to easily map your database entities to classes in your code and work with those classes easily with native langugage constructs. You can easily map the CRUD operations of the generated entity class onto your stored procedures if you want. I do find that some things no longer require stored procedures to work easily and so I have moved away from using them, but you are not forced to. Essentially what LINQ2SQL can do is replace much, if not all, of your DAL, saving you from having to write this code.
LINQ-to-SQL supports mapping lots of operations (including CRUD) to stored procedures, but you lose composability - i.e. you can't just add (exp).Where(x=>x.IsActive). One option there is table-valued functions (UDFs) in place of stored procedures that query data; this also presents a more rigid meta model (rather than SET FMT_ONLY ON, which is hit'n'miss).
That way, your query methods are composable at the database; but note that Entity Framework does not support this, even though LINQ-to-SQL does.
I use Linq2sql for calling my stored procedures as well just because its so fast to generate .net code I can call from my app, its just drag and drop, basically done in seconds. Still I think you need to ask yourself how much time you spend maintaing those stored procedures. If you do you would save a lot of time using linq2sql doing your crud calls. I use sprocs only when doing multiple step operations on the database.
Just to address your related question: The benefits of using stored procedures are not as prominent as they once were. Though I would not go as far as saying stored procedures are evil, as some have said in the past (http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html - this is a very nice article though), I would say that the use of dynamic sql, as long as it's done in a well defined structured way, is perfectly acceptable these days.
I don't this Microsoft are trying to steer developers away from using stored procs but that dynamic sql should be seen as an acceptable option.
You lose the ability to write Linq queries, which is the best part of linq-to-sql.