Windows Authentication Trusted Connection Problem?

Question

MSSQL Server is in the "abc" domain and have mixed mode authentication. I am connecting from the machine which is not in domain or in a domain "xyz" but with in the same network using MSSQL Jdbc driver 2.0. I have logged in as admin or account in xyz domain.

It works fine using following url for connection for "sa" or SQL Mode Authentication.

jdbc:sqlserver://%DB_IP%:%DB_PORT%;SelectMethod=cursor;DatabaseName=dbname

It doesn't work For window authentication using credential "MSSQLDomain\username" i.e "abc\username", using following url

jdbc:sqlserver://%DB_IP%:%DB_PORT%;SelectMethod=cursor;integratedSecurity=true;DatabaseName=dbname;

Gives following error. Login failed for user ''. The user is not associated with a trusted SQL Server connection.

I have tried adding property Trusted_Connection=Yes to url, but still gives same error. I don't want to map the drive of the SQL Server. I am able to access the any shared folder of the SQL Server Machine by providing "MSSQLDomain\username" and password.

It works fine for both authentication mode, if both machine is in same domain. If I am using jtDS Driver from the machine which is not in domain or in "xyz" domain within same network i.e same subnet, it works fine.

Answer 1

This is the deliberate and correct behaviour of Windows Authentication.

It is because the Domain from which you are connecting from, is not the same Windows Domain as the one where your SQL Server instance resides.

I believe there are methods for bridging the Domains so to speak, however they require custom and tricky implementation. You also will have to configure a trust relationship between the domains.

The following thread contains discussions which you will likely find useful.

http://sql-server-performance.com/Community/forums/p/24601/137574.aspx

Answer 2

Hi

I have been involved with making a SQL server connection cross to domains like that, and it is exceptionally painful. In order to use credentials from another domain, the domain where you are assigning the permissions has to trust the domain, where the account is coming from. IT Pro's are generally VERY reluctant to trust another domain in this manner, and for good reason, so if this trust relationship has not been established it might not be very likely to convince the admins to do this.

Once you have the trust relationship established, you will probably need to register the SPN's for your SQL server in Active Directory, and assign delegation permissions. This type of environment is very difficult to setup, troubleshoot and maintain.

I hope that there is some other way that you can do this, because it sounds like you are headed for a very difficult scenario.

Hope it helps Rihan