Database access using windows authentication

Question

We are updating our Applications to use single sign on and are running into a problem with database access. We are using CF11 Enterprise and SQL Server 2008 on IIS 7.5.

We have set up the ColdFusion Application Service to run under an AD service account and have created the data sources in CFAdmin leaving the username and password blank. The data sources verify and all seems good. The problem comes when running a query. The credential passed to the database is the service account and not the windows authenticated user. As such the query fails. What are we missing to get CF to pass the Windows Authenticated user credential instead of the service account?

Thanks

Tim

Carl Von Stetten · Answer

ColdFusion does not pass user's credentials to the database connections by default, and cannot pass Windows Authentication credentials that way. It only sends the service account's credentials (if you leave username/password blank as you have done). The only way to pass user credentials is to put them into the individual query calls themselves, and even then you can't pass Windows Authentication credentials. You would have to use SQL Server Logins, and create accounts for each user.

I think most people are using either a dedicated SQL Server login for ColdFusion and run all queries under that account, or they do as you have already done and use Windows Authentication along with the ColdFusion service account. If you need an audit trail, then pass usernames into the insert/update queries and store them manually along with the other data you are inserting/updating.

-Carl V.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.