March 7, 2011 at 2:16 pm
I have a SQL Server 2005 SP3 with Windows Authentication only,not mixed mode, and the SQL Service startup account is the Local System account. This is the only server that I can't set up Linked Server either To or From. I seem to be configuring it exactly like all the other servers that work fine. Is there a limitation that you can't set up Linked Server when Local System account is used? To other servers and/or from other servers? So far that is the only difference I can find.
(The error I get, when trying to add a valid account in Linked Server security, is Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.)
Thank you,
Holly
March 7, 2011 at 4:11 pm
I am seeing two separate issues here:
1. Your instance is unable to connect to other servers using Linked Servers.
2. Other instances are unable to connect remotely to your instance using a Linked Server.
Re: #1 what type of authentication settings are you configuring. If you're using the GUI to create the new server what do you have on the Security tab?
Re: #2 have you enabled remote connections to your instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 7:40 am
My concern is really with connecting TO this instance from another server (your #2). Yes, remote connections are enabled, TCP/IP.
When I add a domain account, which is sysadmin on both servers, on the security window of the GUI on the remote server from which I want to have Linked Server connection to my problem server, I get: "An exception occurred while executing a Transact-SQL statement or batch. The OLE DB provider "SQLNCLI" for linked server "SQ11" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SQ11". OLE DB provider "SQLNCLI" for linked server "SQ11" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)" whether I enter the account as local logon and as remote logon and remote password, or choose Impersonate.
I am wondering if using Local System account as the startup account for SQL Server means you can't connect to it from a remote server with Linked Server.
March 8, 2011 at 10:22 am
OK, let's pursue item 2 for now...
I do not know of any limitations with using Linked Servers when the service starts up as Local System so I am ignoring that aspect for now.
What Security context are you using for the Linked Server? (see attachment)
I would recommend using "Be made using the login's current security context" or "Be made using this security context" and supplying a valid SQL Server Login credentials for the remote instance.
Disclaimer: I am proceeding with GUI examples since managing Linked Servers via scripting can be messy when troubleshooting. I do recommend using scripts to create Linked Servers across multiple servers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 10:55 am
I am using "Be made using the login's current security context" but it fails with all four, whichever one I use. I tried using "Be made using this security context" and entered the same account, and it failed with "Login failed for user 'xxx\abc_def'. (Microsoft SQL Server, Error: 18456)"
March 8, 2011 at 11:30 am
When you use "Be made using this security context" you have to provide a SQL Server login and password. This option does not work with a Windows login and password.
What is the error you receive when you have it set "Be made using the login's current security context"?
Also, can you please confirm you can connect to the remote server in question in SSMS either with a Query window or through Object Explorer? I just want to rule out any general connectivity issues and make sure this is only a problem for Linked Servers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 12:14 pm
Using the login's current security context, the error is:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Yes, I can open SSMS with "Run As" the account I am trying to enter in Linked Server Security, and connect to this problem server, and query tables, etc.
March 8, 2011 at 12:20 pm
Holly Kilpatrick (3/8/2011)
Using the login's current security context, the error is:Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Is this while logged into SSMS under your own personal account?
Yes, I can open SSMS with "Run As" the account I am trying to enter in Linked Server Security, and connect to this problem server, and query tables, etc.
While you are in there as this other account using "Run as", can you use the Linked Server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 12:51 pm
Yes, this is while logged into SSMS under my own account. Actually, logged in as my sys account that we use here for servers, and which is sysadmin on the SQL Servers.
I just opened SSMS with RUN AS logging on as the account I am trying to use in the Linked Server security (xxx\abc_def). I connected to the remote server where I am trying to set up the Linked Server to the problem server, SQ11, and tested the Linked Server, and got "Login failed for user 'xxx\abc_def" (Microsoft SQL Server, Error: 18456)" .
At the same time, I am also connected to SQ11 under the same account and it is just fine. This same account runs SQL Agent jobs on SQ11 just fine. It is only Linked Server that doesn't like it.
I appreciate all your time, and I feel like I have already monopolized too much of it! But any bright ideas are appreciated!
March 9, 2011 at 12:14 pm
Holly,
I would go to the console on one of the non-problem servers and log on under the same service account that starts SQL Server. Then go into windows explorer/network neighborhood and see if you can access the problem server - look at the files, etc.
Your problem smells like some sort of login problem.
Todd Fifield
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply