Database access using windows authentication

[timeToPlay]

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]

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.

[timeToPlay]

Our Organization uses Kerberos to authenticate users against active directory for its .Net applications.  I am trying to leverage this for use with CF and have set up the ColdFusion Sever's IIS  to use the Kerberos authentication. Is there a way to have coldfusion calls to the database use the Kerberos authentication instead of the service account that the ColdFusion Server is running on? This would allow the database calls to use the windows login credentials instead of the service account credentials.

Thanks for any suggestions

Tim

[Carl Von Stetten]

Not that I'm aware of.  As far as I know, the only way to pass Windows authentication at all is through the ColdFusion service account.  Otherwise, ColdFusion can only pass username/password via JDBC - which would utilize SQL Server authentication rather than Windows Authentication.  The reason it works for .Net applications is that .Net natively supports Windows Authentication.  ColdFusion does not.

-Carl V.

[timeToPlay]

Thanks Carl

Is there a java solution that CF could use. As I understand it, the .Net Apps also log in to the database with a service account but then use the impersonate feature granted to the account in Active Directory to use the windows login. Is there, perhaps a Java solution that coldfusion could use to enable impersonation?

Tim

[Carl Von Stetten]

I haven't found one, but that doesn't mean one doesn't exist.  I spent a couple minutes on Google, but nothing seemed to be on point.

Also, keep in mind that while IIS can use Kerberos authentication, once the request is passed to ColdFusion, IIS has nothing to do with how ColdFusion processes the server-side code or talks to databases.

-Carl V.