JDBC Connection to MS SQL with Windows Authentication

[iekhan]

Environment -

I am using Tomcat as my servlet engine and IIS as the Server. Additionally, I am using MS SQL 2000 as my DB. The DB resides on a seperate machine, and have created an ODBC JDBC connection on the local machine to access it. Additionally, the authentication for the DB is set to "Windows Authentication".

Problem -

Everything works fine if I simply run tomcat and access the databse. Also, things run fine when I run tomcat via IIS too. However, when I try accessing the database, I get the error

Login failed for user ' '

Now, the funny part is, this username is actually the username of the local machine on which the application has been placed. It is not the username that I am providing in

con=DriverManager.getConnection("jdbc : odbc : DBName","username","password"); /* had to add in extra spaces to stop this site from substituting :+D,etc to smiles*/

Can someone please help me figure it out?? Why is it working when I run the tomcat (Apache) and run the application from there, but ones I run it via IIS, it fails. Also, why is it taking the username from the local machine and not the one that I am providing out there?

PS: Someone suggested me that its not possible and that I should not be doing NT Authentication. Is it really true? Is there no turn around for this? I will really appreciate your help in this.

[rmiao]

Did you add that windows account as sql login in sql server yet? By the way, you don't need specify user id and password in connection string if use windows authentication. System will pass current windows user to sql, and that windows user should have login id and proper permission in sql.

[iekhan]

This looks like a very good lead to me and I will check it out as my first thing tomorrow morning in the lab (about 10.5 hrs from now).

However, I am still baffled by one thing. Presume the name of the computer is CompName and domain it is in is myDomain. Now, even though I have logged onto to computer through the user name XYZ, the error says -

Login failed for user myDomain/CompName

Why is it not stating that login failed for user XYZ. Additionally, this XYZ happens to be the owner of that database.

[rmiao]

Did you try connect with sql client tool or via asp page? If via asp, it may use web service's account to talk to sql. So you should add myDomain\CompName as sql login and grant proper permission to it in sql.

[mitul]

Hello,

Even I am facing a same sort of issue. I have an SQL Server and a web server from a different machine. When I access the ASP page on the web server itself it works find but when I access the same from a different workstation it gives an error that it cannot authenticate. I have already enabled windows authentication in the sql database with users having proper permissions to access the same. I have also set NTFS file permissions on the asp pages with users having proper access.

The only workaround that I could fine was to enable anonymous access on the webserver and add a domain user having access to sql instead of the default anonymous user.

But this configuration is not acceptable as it is against security and in the logs for database it shows that only the specified user has accessed the database as it is done by the webserver.

So even I am working for a workaround for the same.

Thanks in Advance for any help or suggestions...

Regards,
Mitul