connect Coldfusion 9 to SQL using intranet users windows credentials

[Guest]

Is it possible to use pass through / integrated authentication using the application users windows account (rather than the service account) when a Coldfusion application connects to an MS SQL DB?

For background, we are running:

ColdFusion 9,0,1,274733 hosted on a Windows 2008 R2 (64) server

SQL server 2008 R2 hosted on a Windows 2008 R2 (64) server

IE 8 and/or 9 as the client browser

I have an intranet application that is used only by users within our AD domain. I have no problem getting ColdFusion to connect to the SQL database using the ColdFusion service account, but ideally we would like the connection to be made under the application user's account.

I would appreciate any guidance on how to achieve this if it is possible?

(I am not a webmanager/developer and so my ColdFusion knowledge is very limited!)

Thanks in advance,

Darren

[Carl Von Stetten]

I've never been able to get that to work.  I have only been able to use SQL Authentication (an actual account created on the SQL Server instance rather than a Windows account).  However, I think it might be possible if you install Microsoft's SQL Server JDBC driver and tweak the connection string (I have a vague recollection of seeing someone talk about this either in a blog post or on CF-Talk (http://www.houseoffusion.com).

-Carl V.

[Dan_Bracuk]

What is the reason you want to do this?  There might be another way to accomplish that goal.

[Guest]

Thank you for the responses Carl and Dan

the main reason for this is that the ColdFusion application is just one of the interfaces that is interacting with this database. There is a load of security and user action auditing that is going on in the background that is using integrated authentication as a control (i.e. tieing all user actions to their windows account without the user having to login to each application individually). I was hoping that ColdFusion could just integrate in the same way, but I guess I will have to pass the Auth_User variable instead and handle this data value explicitly in the DB.

Regards,

Darren

[BKBK]

@Daz

I'll echo Dan's question, What is the reason you want to do this?.  Imagine a tourist obtaining a visa that also authorizes him to visit a nuclear installation. What you are thinking of doing is analogous to that.

SQL Server is a vital resource within the Windows domain. Permission to access SQL Server should therefore be distinct from permission to enter the domain. In fact the golden rule of IT security says that every resource should have just the minimum permissions necessary for it to perform its tasks, no more. Thus, ColdFusion should have the minimum SQL Server permissions necessary for it to perform its tasks.

[Guest]

BKBK, your point IS why I want to do this.

Using the ColdFusion service account (or an SQL account hard coded into a ColdFusion datasource) to access SQL server forces me to provide the same level of access to all users of that application, or to put application level code in place to control access.

If I can use true integrated security, and connect to the SQL server as the individual user then I am able to set a more granular level of access based upon that users needs.

For a very simple example,

User A can execute procedures 1-4 within the DB

User B can execute procedures 1-4 and procedure 7 within the DB

As I cannot connect to the database as either User A or User B, I have to always connect as another system user or service account that has access to Procedures 1-4 and procedure 7.

This means that User A effectively gets permission to procedure 7, unless additional measures are taken in the application to prevent this. i.e. have several datasources setup that use different SQL accounts, and have the application 'choose' which datasource to use based upon the users account - this is messy and therefore I was hoping to skip this and handle it all with AD group control within the SQL server.

If I could connect as the actual user then I can use standard AD groups/users to manage permissions to what they can/cannot see or execute at a more granular level.

In short I am trying to "have the minimum permission necessary to perform a task" - I just want to control that permission level with AD group control within the SQL server.

As I say, this is not the end of the world as I have a way around it, but I am surprised this is not a more common problem.

Thanks for your response.

[BKBK]

You explain the use case well, and convince me of the need. However, what you call User A, User B and Actual User are not clients of SQL Server. They are users of ColdFusion. So your question boils down to this: how can one create distinct authorization methods by which ColdFusion users can access stored procedures in SQL Server?

Someone had thought about that. One example is <cfStoredProc username="BKBK" password="bkbk_pw">. These credentials override those defined in the datasource page in the ColdFusion Administrator.

[Dan_Bracuk]

I understand what you are saying and don't think you need permissions for every user on the applicable databases.

You gave an example of User A being able to execute 4 procedures and User B being able to execute 5.  What you have to do as the web programmer is to prevent User A from trying to run the 5th sp.  This is irrespective of database accounts.  Whatever UI control User B has to run sp 7 cannot be available to User A.  If it is, and you are relying on permissions, User A can select the UI control and have an error occur.  I wouldn't want my name attached to a program that worked like that.

In fact, using the UI to control access should take away the requirement to have mulitple datasources which you have now.

[Guest]

Again, thanks for the responses - it is nice to be able to talk through these things (I work in a fairly small organisation and so do not often get to talk through technical subjects with other professionals!)

Firstly, to respond to BKBK, I have been considering that approach, though unless I have missed something, I would either:

1. have to use simple authentication - in which case a users windows credentials would be passed as Binary_Base64 (i.e. clear text) - and possibly would need to replicate all AD accounts as SQL server accounts (not sure in that account replication bit as I may be able to still get it to authenticate as a windows account from the SQL engine - but the clear text passwords is the real problem), or:

2. have to use form based credentials, in which case the users would have to 'login' to the application - I am trying to avoid this to make it as seamless for the users as possible.

Neither of those approaches are ideal (unless, as I say, I am missing an option there) and so I am more inclined to use a single SQL account from the datasource definition and control access from a combination of the application and the database.

Secondly, to respond to Dan.

I agree, it would be no good for User A to receive an error if they tried to run a proc that they do not have permissions for. However, if these errors occur they are captured and handled gracefully in both the application suite and the database.

Aside from this, the application does not provide the ability for user A to execute procedure 7 (from the example in my previous post) - which I guess is what you are saying with " Whatever UI control User B has to run sp 7 cannot be available to User A".

The reason for controlling physical permissions in the DB was that:

1. it is universal for all interfaces with that database - so long as integrated auth is used

2. it provides a belt and braces approach (as parts of this application has sensitive data) - so that if somehow user A gets the web application to call procedure 7 then the DB would still prevent it

For thought/discussion:

I think I will end up using a single account from the datasource, but make it a datasource that can only be called from AD users accessing the application (though only AD users can access the application which achieves this already).

Any call to the database must include the CGI variable "AUTH_USER" as an input parameter.

The DB will then:

1. check the account that is logged in to the SQL engine (to ensure a user hasnt bypassed coldFusion and gone straight to the DB - though Group permissions are already set for this scenario)

2. Check the user supplied as a parameter exists in AD and is an active account

3. and check the permissions of that user for the particular task that was requested of the DB engine. - execute if permissable, gracefully refuse with appropriate messages passed back if not.

This combined with the control in the application to only present the right functions to the right user should give me the belt and braces that we are after - all be it in a bit of a convoluted way!

I guess the big question is how easy/difficult is it to fake "AUTH_USER"?

[Dan_Bracuk]

Regarding: "

I guess the big question is how easy/difficult is it to fake "AUTH_USER"?"

There is another active thread on that very topic.