Can I connect to SQL Server using Windows Authentication from Java EE webapp?

Question

I am currently investigating how to make a connection to a SQL Server database from my Java EE web application using Windows Authentication instead of SQL Server authentication. I am running this app off of Tomcat 6.0, and am utilizing the Microsoft JDBC driver. My connection properties file looks as follows:

dbDriver              = com.microsoft.sqlserver.jdbc.SQLServerDriver
dbUser                = user
dbPass                = password
dbServer              = localhost:1433;databaseName=testDb
dbUrl                 = jdbc:sqlserver://localhost:1433

I have zero problems with connecting to a SQL Server database in this fashion when using SQL Server authentication.

Is there any way I can retrieve the credentials of the user's Windows Authentication and use that authentication for SQL Server?

UPDATE: I know in ASP.net there is a way to set up Windows Authentication for access to the webapp, which is exactly what I am looking for, except I want to pass that token off to SQL Server for access to the database.

Answer 1

First off, I do not know.

But I would be really surprised if that is possible. If you say Windows Authentication you presumably mean Active Directory. In that case obtaining data from the Active Directory should not be really troublesome; it is, after all, mostly an Ldap server (see Java Naming and Directory Interface.

But what you would need is a password, and I very much doubt Active Directory, or any other mechanism, would give you an unencrypted password for a user. I even doubt Active Directory even stores the unencrypted password; passwords usually are stored encrypted, and a password comparison is usually done by encrypting the given password and comparing that with the stored password.

If you don't use Active Directory the problem remains; getting the unencrypted password out of a authentication system is (should be) impossible.

The only thing you could do is create your configuration as above and name it administrationConnection, and use the AdministrationConnection to obtain per-user user/pass out of the database. And use those to make a Connection called userConnection to do user-specific stuff with.

The disadvantage is that you now need to manage an extra table of credentials. And that those credentials are easily stolen if you're not very carefull in encrypting the data with a key known to tomcat but not the database server.

I personally would not burden the data layer with that compexity; what users should be able to do is business logic and there are good frameworks (Acegi, currently Spring Security) which are designed for autorisation.

What you actually need might be just 2 preconfigured database connections; one for administration and one for user level application logic. With the latter having the smallest amount of permissions possible. That is manageable and simple to build.

Answer 2

Unless you have some really compelling reason not to, I suggest ditching the MS JDBC driver.

Instead, use the jtds jdbc driver. Read the README.SSO file in the jtds distribution on how to configure for single-sign-on (native authentication) and where to put the native DLL to ensure it can be loaded by the JVM.

Answer 3

I do not think one can push the user credentials from the browser to the database (and does it makes sense ? I think not)

But if you want to use the credentials of the user running Tomcat to connect to SQL Server then you can use Microsoft's JDBC Driver. Just build your JDBC URL like this:

jdbc:sqlserver://localhost;integratedSecurity=true;

And copy the appropriate DLL to Tomcat's bin directory (sqljdbc_auth.dll provided with the driver)

MSDN > Connecting to SQL Server with the JDBC Driver > Building the Connection URL

Answer 4

look at

http://jtds.sourceforge.net/faq.html#driverImplementation

What is the URL format used by jTDS?

The URL format for jTDS is:

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

... domain Specifies the Windows domain to authenticate in. If present and the user name and password are provided, jTDS uses Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the user and password provided are the domain user and password). This allows non-Windows clients to log in to servers which are only configured to accept Windows authentication.

If the domain parameter is present but no user name and password are provided, jTDS uses its native Single-Sign-On library and logs in with the logged Windows user's credentials (for this to work one would obviously need to be on Windows, logged into a domain, and also have the SSO library installed -- consult README.SSO in the distribution on how to do this).

Answer 5

Hi, I was having issue with connecting to MS SQL 2005 using Windows Authentication. I was able to solve the issue with help from this and other forums. Here is what I did:

1. Install the JTDS driver
2. Do not use the "domain= " property in the jdbc:jtds:://[:][/][;=[;...]] string
3. Install the ntlmauth.dll in c:\windows\system32 directory (registration of the dll was not required) on the web server machine.
4. Change the logon identity for the Apache Tomcat service to a domain User with access to the SQL database server (it was not necessary for the user to have access to the dbo.master).

My environment: Windows XP clinet hosting Apache Tomcat 6 with MS SQL 2005 backend on Windows 2003