Forum Discussion
Change SQL Login to AD service account for Link server
Hi Everyone,
I tried to change account used to impersonate from SQL account to AD service account for link server however im getting below error. The AD service account has sys admin rights on the SQL servers.
Access to the remote server is denied because no login-mapping exists. (Framework Microsoft SqlClient Data Provider)
Any ideas how to resolve?
Regards
5 Replies
will try this out thanks and will let you know if it worked.
Hi kevinfr820 ,
this usually happens when the AD service account is not properly mapped for the linked server connection. Please try using sp_addlinkedsrvlogin with @useself = false and specify the AD account under @rmtuser. Also, make sure Kerberos delegation is set up correctly and SPNs are registered for both servers. That should fix the login mapping error. here is the sample script
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'YourLinkedServerName', -- name of your linked server
@useself = 'false', -- use remote credentials
@locallogin = NULL, -- NULL = applies to all local logins
@rmtuser = 'DOMAIN\ServiceAccountName', -- your AD service account
@rmtpassword = NULL --Update the passwordhttps://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver17
Im investigating further myself to understand why im getting this error message. The login has sys admin rights on both servers. Any other ideas what can be done to resolve?
Hi,
From your explanation, I understand that you have two SQL Server instances you're trying to connect using Linked Server.
You're attempting to impersonate a "Windows Login" on the remote server.
For this configuration, the [AD/User] login has to exist on both instances, which you explain you already have in place.
You should only supply remote user/password when not doing impersonation.
If you at any point supply a username/password combo the linked server will attempt SQL Server Authentication, not AD/Windows authentication.From the official documentation:
Create linked servers - SQL Server | Microsoft Learn3. Select Impersonate (optional).
[...] For Windows logins, the login must be a valid login on the linked server.
To use impersonation, the configuration must meet the requirement for delegation.
Note the point about delegation.
Delegation in this context is related to Kerberos and the "double hop" issue with AD Authentication.
I have not been able to find Microsoft documentation on how to configure delegation, but here is a sqlshack article specifically on linked servers and kerberos.
How to link two SQL Server instances with KerberosI hope this helps you in resolving your issue.
> because no login-mapping exists.
The AD account may have persmissions for the one SQL Server, but by the error message not for the other SQL Server.
