June 12, 2019 at 8:24 am
LS,
The situation: 2 node cluster (SQL 2012), 4 instances , 2 instances prefered active on node1 - 2 instances prefered on node2, security setting on all instances: "Windows authentication Mode", Security (logins) all instances: Domain\DBAgroup (containg domain user Domain\AAAA) - and Domain-user Domain\AAAA as a seperate registration (both with SA privs)
Linkedserver from instance 1 on node1 to instance 2 on node2 is not working (Error: 18456, Severity: 14, State: 58)
Linkedserver from instance 1 on node1 to instance 3 on node1 is working
Linkedserver from instance 1 on node1 to instance 4 on node2 is not working (Error: 18456, Severity: 14, State: 58)
Linked server specifications - for the working LINKEDSERVER to instance 3:
General tab ; name / provider: Microsoft OLE DB provider for SQL server / Datasource: server and instance name to one the instances 3.
Security tab ; local-login: Domain\AAAA / Impersonate: YES / For Login not defined: "Not to be Made"
Server Option tab ; Standaard + RPC / RPC out both TRUE
The above LINKEDSERVER setting do not work for the instances 2 and 4, I tried:
General tab ; name / provider: Microsoft OLE DB provider for SQL server / Datasource: server and instance name to one of the instances 2 and 4.
Security tab ; local-login: Domain\AAAA / Impersonate: NO / Remote user; Domain\AAAA / Remote Password ; <<pw>> / For Login not defined: "Not to be Made"
Server Option tab ; Standaard + RPC / RPC out both TRUE
When hitting the OK-button for the LINKEDSERVER to instance 2 and 4 an error pops up telling :
Login failed for user 'Domain\AAAA'. (.Net SqlClient Data Provider)
while the Domain-user is present and active on the instance.
Examining the SQL-Lof on the instances (both 2 and 4) I see :
Login failed for user 'Domain\AAAA'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 58
This indicates the LINKEDSERVER has reached the apropriate server/instance but for some reason its is not resolving the domain-user Domain\AAAA (which is present and active).
On both nodes Share-Memory / TCP/ IP and Named-Pipes are enables (for all instances).
The Firewall is configured as it should be.
The 'Adhoc querie' option in SP_configure has been disabled (with reason) so I cannot use OPENROWSET or OPENQUERY as repacement for the LINKEDSERVER.
The 'Anonymous user" has been disbled as well (with reason).
The former DBA has set the "Windows authentication Mode" with reason so I'd like to leave it as it is.
I'm out of options - can someone please help me out ????
Regrards,
Guus Kramer
The Netherlands
June 12, 2019 at 4:41 pm
The login error you are getting with State 58 is due to this setting:
Security tab ; local-login: Domain\AAAA / Impersonate: NO / Remote user; Domain\AAAA / Remote Password ; <<pw>> / For Login not defined: "Not to be Made"
You use the remote user and password with SQL Server logins. It won't work with Windows logins. You would want to set it similar to the one that works. Without knowing what error you get when it doesn't work, the first guess would be an issue with Kerberos. Check the following article which explains this and setting up delegation:
SQL Server: Curse Of Linked Server Security And The Fix: Pass-through Authentication
Sue
June 13, 2019 at 8:22 am
Sue,
Thanks you - this pointed me into a direction I overlooked.... I will investigate and ask our AD-Networks specialist(s) to look into it.
Guus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply