On 3rd day of trying to solve cross server login issue to MSSQL 2008
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)
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.