April 14, 2014 at 9:23 am
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
April 14, 2014 at 12:25 pm
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
April 14, 2014 at 3:34 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 15, 2014 at 2:48 am
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 🙂
April 15, 2014 at 3:00 am
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?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 15, 2014 at 3:49 am
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
April 15, 2014 at 4:21 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 15, 2014 at 6:34 am
sorry for the delay....information attached
April 16, 2014 at 3:17 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 17, 2014 at 4:09 am
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
April 17, 2014 at 4:26 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply