Issue with database connection from sharepoint workflow with integrated security options

Question

Good morning everyone,

I'm running into an issue using a SharePoint workflow project (C#, VS 2008) and connecting to a database. Here is my database connection string:

Data Source=DBSERVER;Initial Catalog=DBNAME;Integrated Security=True;

When I attempt to run the following code I get the following error ...

SqlConnection dbEngine = new SqlConnection(Constants.DBCONNECTION_STRING);
dbEngine.Open();

"Login failed for user 'DOMAIN\MACHINE_NAME$'"

What I need it to do is pass through the logged in user's credentials. I've got impersonation turned on but it doesn't seem to be passing through. Any suggestions would be very much appreciated.

Thank you in advance for any advice,

Scott Vercuski

Answer 1

Are the web front end and the SQL server on the same box ?

If not, you'll have to set up Kerberos to allow credentials propagation.

Answer 2

You cannot do that - the workflow does not run in the context of a user. Workflows are executed asynchronuously. Only (HTTP) WebPage-Requests run in the context of the user (if you turn on impersonation). You cannot flow the impersonation to the workflow. To restore impersonation in the workflow (which you should not do) would require username AND password OR protocol transition (process would need to run under system then).

Additionally, your application has a serious design issue if you try to access the db from an impersonated user context. That messes up connection pooling and will seriously hurt performance. That is generally a no-go.

This is not a kerberos issue. The process tries to access the db as the machine account, which tells you the process is running as either network service or (win 2008 and later) system.

Answer 3

So what if I do want to access the database from an impersonated user context within a workflow, is it possible?

Answer 4

Any DB access should run as a Windows Service account for security and connection pooling reasons.

Regarding the Workflow Security Context, see:

SharePoint, Workflows and Security http://cglessner.blogspot.com/2008/09/sharepoint-workflows-and-security.html

Declarative Workflows and User Context http://blogs.msdn.com/sharepointdesigner/archive/2008/09/28/declarative-workflows-and-user-context.aspx

Security and Application Development in SharePoint: First Steps Workflow http://www.microsoft.com/technet/community/columns/secmvp/sv0408.mspx#EACAC