I am on a virtual ColdFusion 10 Developer computer running Windows 2008 R2 Datacenter and am trying to setup a datasource to connect to our SQL 2008 server which is running on Windows Server 2008 Standard.
I can successfully connect using ANY SQL Login that I create, however I cannot authenticate when using Windows Authentication. Both servers belong to the same domain, and the SQL Server is setup for Mixed Mode.
On the SQL Server I created a new User called: DOMAINNAME\Username. He is public and was mapped to a database as user DOMAINNAME\Username with a default schema of DOMAINNAME\Username.
When attempting the authentication, CF returns this error:
Connection verification failed for datasource: test
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Drive][SQLServer]Login failed for user ‘DOMAINNAME\Username’.
The root cause was that: java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user ‘DOMAINNAME\Username’.
I have spent 3x 10-hour days, doing countless searches on Google and all things have appeared fruitless, so I’m asking for whatever help anyone can give. Previous solutions hinted at active directory delegation being enabled for both servers, but after performing those steps, I was at the same end result.
To utilize Windows Authentication wouldn’t your coldfusion process have to run as the user you want to authenticate under? As that would be the user trying to establish the connection. Passing a username would be considered as using a user login (I think).
ColdFusion 10 has 5 services, I have each of them running as DOMAIN\AdminUser.
So are you saying that CF is passing this credential over to the SQL server rather than the Username/Password that is being defined in the datasource?
By chance, I actually have the DOMAIN\AdminUser setup as a User on the SQL server as well. I changed the DSN’s credentials to the DOMAIN\AdminUser and put its password in, and am getting the same error (sans it referencing the DOMAIN\AdminUser rather than the DOMAIN\Username)
The following situations can cause a Connection Refused error:
If you specified authentication information in SQL Server, ensure that you have not defined a user name and password in the ColdFusion data source.
Sure enough, I REMOVED the UN/PW from the DSN and it CONNECTED. Does this mean I can only use integrated authentication for the service that CF is running as?
Well, I think it is possibly two fold: one would be the service the CF is running as, the other possibility is if you are using ODBC and have a defined connection at the operating system level that says to use Windows Auth and then it will use who is logged in to the server (I think).
Typically when I use Windows Auth, I allow the user that the service is running as, so I don’t have to do an auto login on the server.