Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked Server using DOMAIN security fails Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 90, Visits: 720
Hello,

I have been attempting to create a linked server but have only been able to get it to work in one specific setup which is not ideal.

Both servers are SQL 2008 R2 Standard (build 1600)
I have a DOMAIN account, which has been setup with a login on both SQL servers with SYSADMIN rights for this test.
I have a SQL account on both servers with SYSADMIN rights for this test.
Both servers are set to SQL and Windows Authentication.
Both servers have a registration for the DOMAIN account under SETSPN -L.
Remote server using NETWORKSERVICE for SQL Service Account.
Local server using DOMAIN account for SQL Service Account.
I can RDP and remotely login to both servers using the DOMAIN and SQL accounts mentioned below.


I have attempted the following for Linked Server configuration:

==============================================================================================================

Standard setup without a "login not defined" security account

FAILS with error: Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

EXEC master.dbo.sp_addlinkedserver
@server = N'TSUNAMI',
@srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'TSUNAMI',
@locallogin = N'DOMAIN\SQL_LinkedServer',
@useself = N'False',
@rmtuser = N'DOMAIN\SQL_LinkedServer',
@rmtpassword = N'xxxxxxxxxxxxxxxxxxxx'

==============================================================================================================

Adding the DOMAIN account to the "login not defined" option it FAILS with the error: Login failed for user 'DOMAIN\SQL_LinkedServer'. (Microsoft SQL Server, Error: 18456)

On the remote server an error is logged stating: Login failed for user 'DOMAIN\SQL_LinkedServer'. Reason: Attempting to use an NT account name with SQL Server Authentication (Error: 18456, Severity: 14, State: 6)

==============================================================================================================

Creating the linked server with a local SQL account (setup on both servers with SYSADMIN rights for testing) but with nothing in the "login not defined" security account....

FAILS with the same error as the domain account: Access to the remote server is denied because no login-mapping exists

===============================================================================================================

Creating the linked server with a local SQL account (setup on both servers with SYSADMIN rights for testing) and with the SQL account in the "login not defined" security account....

SUCCEEDS

===============================================================================================================

Creating the linked server with nothing in the mapping and the SQL account in the "login not defined" security account....

SUCCEEDS

===============================================================================================================

It seems to fail whatever account is in the mapping security window and always fails back to the "login not defined" security account.

I have also tried using SQLOLEDB as the provider, tried IP addresses, and seen some stuff about @provstr but i cannot seem to find that option.

Any ideas why this may happen??

Many thanks

Samuel
Post #1561522
Posted Monday, April 14, 2014 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 6:38 AM
Points: 33, Visits: 65
This may have to do with how AD tokenizes the windows credentials and passes it to the next server. For whatever reason it seems the remote server can not communicate to an AD server to authenticate the user. Have you tried to set the remote server's agent account to use a domain user (Although I don't think this would fix the issue).

I have always used SQL Authentication for linked servers rather than Windows Authentication.


- Tony Sweet
Post #1561631
Posted Monday, April 14, 2014 3:34 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:57 AM
Points: 802, Visits: 717
The login you set up in the login mapping must be an SQL Server login. You cannot login to SQL Server windows login and password. To login to SQL Server with Windows authentication, you must first have logged into Windows.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1561681
Posted Tuesday, April 15, 2014 2:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 90, Visits: 720
thanks for the answers....however, even when using a SQL Server login for the mappings (same account name on both sides, both have SYSADMIN for the test) i get the same error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)


we have changed the SQL Agent account to a domain account but no difference
nothing gets logged in Error Logs either

any trace flags i can turn on to get more information on this??

thanks in advance :)



Post #1561780
Posted Tuesday, April 15, 2014 3:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:57 AM
Points: 802, Visits: 717
Can you post the output from sys.servers and sys.remote_logins for this servers, so that we get an understanding of your current status?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1561784
Posted Tuesday, April 15, 2014 3:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 90, Visits: 720
2 file attached:

VORTEX (server where link is being created)
TSUNAMI (server where link is pointing)


both tables for remote_logins were empty although i did notice a difference where TSUNAMI has a FQDN where VORTEX only the hostname.

thanks for your help


  Post Attachments 
TSUNAMI.xlsx (1 view, 9.72 KB)
VORTEX.xlsx (6 views, 9.86 KB)
Post #1561796
Posted Tuesday, April 15, 2014 4:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:57 AM
Points: 802, Visits: 717
Sorry, I managed to mix up the catalog views. It's the output from sys.linked_logins we need to see. On VORTEX. The settings on the target server are not of interest.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1561802
Posted Tuesday, April 15, 2014 6:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 90, Visits: 720
sorry for the delay....information attached

  Post Attachments 
VORTEX.xlsx (5 views, 9.15 KB)
Post #1561838
Posted Wednesday, April 16, 2014 3:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:57 AM
Points: 802, Visits: 717
Sorry for the late reply, I have not had the time to reply. And that I have much to reply. With the settings you posted, it should work. There is a mapping for local_principle_id = 0, that is public. So you should not get the error that no login mapping exists.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1562479
Posted Thursday, April 17, 2014 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 90, Visits: 720
OK thank you very much for your investigation.

I will speak to the guys who build the server and get them to do a complete domain authentication, membership and build check to see why this doesnt seem to be authenticating properly.

if no-one has anything else to add you can close this post....i will add anything i find from the IT Team.

regards

Samuel
Post #1562577
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse