Windows authentication trusted connection not work

问题:

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.

回答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

回答2:

Changing the login credentials might help, use SQL authentication instead of nt authentication

https://support.microsoft.com/en-us/kb/555332

Symptoms

After you install Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005, or SQL Server 2000 and you try to connect to the server that is running SQL Server, you receive one of the following error messages:

Login failed for user '%.*ls'. The login is a SQL Server login and cannot be used with Windows Authentication.%.*ls

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Login failed for user ''. (Microsoft SQL Server, Error: 18456)

Resolution

This problem occurs if the user tries to log in with credentials that cannot be validated. This problem can occur in the following scenarios:

Scenario 1:

The login may be a SQL Server login but the server only accepts Windows Authentication

To resolve this issue, configure SQL Server in Mixed Authentication Mode.

Scenario 2:

You are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server

To resolve this issue, verify that the SQL Server login exists. For more information, see Create a login in SQL Server Books Online.

Scenario 3:

The login may use Windows Authentication but the login is an unrecognized Windows principal

An unrecognized Windows principal means that Windows can't verify the login. This might be because the Windows login is from an untrusted domain. To resolve this issue, verify that you are logged in to the correct domain.

回答3:

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