Linked servers and Windows Authentication
I have seen similar questions, but none of the answers have solved my problem.
I have several SQL2000 servers running under Win2003. In the past, we have been using SQL logins, but I have been trying to transition to using Windows Authentication. It has been working great except that sometimes queries using linked servers fail.
The linked servers are set up for the connection to use the login's current security context.
For testing this problem, I have limited myself to two servers to do this between. My Windows account is defined with sa rights on both servers. I have tried explicitly to impersonate my account, I have told it to explicitly use the same account and password on the other server. I have tried not listing my account and letting it default my original setting of using current security context. (if I tell it to use the sa account remotely, that does work)
Both SQL Servers run under the same domain account. That account had delegation rights. My account is not marked as sensitive. The computer account has delegation rights. There is a Service Principle Name defined for both servers.
I have tried everything I can find to try, yet I still get the following message (or something similar, depending on which settings I have changed around).
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.What am I missing?
All Replies
Can you check MDAC level between those 2 servers?
And check the impersonation settings again.
Both servers appear to have MDAC 2.80.1022 installed.
Both servers have a link to the other with no specific users listed and the default set to using the login's current security context.
Dwatney,
Did you ever get an answer to your question? I am having the exact same problem.
RoyAF,
Not yet, but I'm still moving people over to Windows Authentication hoping that an answer can be found before people show up at my door with tar and feathers, :-).
Have you been able to resolve this issue yet? I am having similar problems and haven't been able to get this to work. Seems my linked server (to sql server 2000) works intermittently.
Michael
My problem was self-inflicted. I was positive I had a login on the remote server but did not. When I created a login all worked well.
Another thing I discovered when I was trying to set up another user, to access the remote server, was that the login alreay exists. After a lot of head scratching, I discovered that the database had been copied from another server and attached to this database. And, they did not move and attach master thus leaving a lot of orhpaned logins, I deleted all of the orhphans from sysusers and all is working.
Roy
Michael,
I will try cleaning up orphans, as suggested above, but otherwise, I am no closer to an answer. There was the question about the version of MDAC. I haven't checked to see if that version translates to the original version 2.8 or one of the later serice packs. If the original, I could try updating to a later service pack.
- Try a little more detail on your situation. Pick one configuration, detail it and post the error.
For example;
Specify “Be made using the login's current security context” in the properties of the linked server.
Look at the Logins is 'yourdomain\yourusergroup' listed and granted login?
Are you authenticated to Server1 through the 'yourdomain\yourusergroup' group when you run a distributed query to Server2?
Which queries fail, and which queries succeed?
What error appears in the application event log on server2 when a query fails? Server1 is Windows 2003 Enterprise, SQL Server 2000 Enterprise SP4.
Server 2 is Windows 2003 Standard, SQL Server 2000 Standard SP4.
Both servers are in the same domain. Both servers run their sql service as the same domain user. That domain user has been given the "Account is trusted for delegation" option in Active Directory.
Each server had a linked server connection to the other. The security settings are such that every connection will "be made using the login's current security context." (In the linked server security tab, there are no login mappings and the option for what to do if not in that list is that connections will "be made using the login's current security context.")
On each server, my domain account is defined as a login with sa rights. Normally, it would be done as part of a group, but to simply matters, I made my individual account a login. If it matters, my domain account happens to be a domain admin, but obviously that won't be the case for others.
On Server1, I run the following query: "Select * From Server2.mydatabase.dbo.mytable" and I get
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.I see no errors in the Event Viewer Application log. I see some successful audits in the Security log for my account (and no failures).
At the moment, I don't have an example of a query that actually works.
I am having a similar issue with SQL 7.0 and linked servers... but I get the following error:
Login Failed for user 'NT Authority\ANONYMOUS LOGON'...
If anyone as any suggestions, please let us know..
Thanks!
ggentile,
That is an error message I have seen, too, while working with this problem.
I had the same problem and posted my solution.
I would strongly recommend that you check to be sure you have a login on the remote computer. I was positive I did but, when I checked I discovered I did not.
HTH,
Roy
To set up a remote server to allow the use of remote stored procedures
-
Run the following code on the first server running Microsoft® SQL Server™:
2.EXEC sp_addlinkedserver ServerName1, N'SQL Server'3.EXEC sp_addlinkedserver ServerName24.EXEC sp_configure 'remote access', 15.RECONFIGURE6.GO- Stop and restart the first SQL Server.
- Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication.
9.-- The example shows how to set up access for a login 'sa'10.-- from ServerName1 on ServerName2.11.EXEC sp_addlinkedserver ServerName2, local12.EXEC sp_addlinkedserver ServerName113.EXEC sp_configure 'remote access', 114.RECONFIGURE15.GO16.-- Assumes that the login 'sa' in ServerName2 and ServerName117.-- have the same password.18.EXEC sp_addremotelogin ServerName1, sa, sa19.GO- Stop and restart the second SQL Server.
- Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.
This is out the SQL books.. I did find that you will probably have to drop the servers first, to clear any entries…
Exec Sp_dropserver ‘RemoteServerName’, droplogins
You will not need step 18, by default, it should set the mapping to ‘Map all remote logins to - <Same Name>’.. you can check this in remote-servers in Enterprise Manager.
If you run sp_helpremotelogin you should see something like this.
Server local_user_name remote_user_name options
‘localservername’ ** use local name ** ** mapped locally **
‘remoteservername’ ** use local name ** ** mapped locally **
RPC only works for local SQL account not NT. Can you logon to Query Analyzer as ‘sa’ and test the you can run a Stored Procedure on the other server. Create something stupid that will email ‘hello’, so you know it worked.
Once this works run the following:
Exec sp_serveroption @server = 'remoteserver’ ,@optname = 'data access',@optvalue = ‘True’
Check the server settings..
Exec Sp_helpserver
Name network_name status id
‘remoteservername’ ‘remoteservername’ rpc,rpc out,data access 2
You should be able to run a distributed query now, with local or NT Authentication.
You will see entries in the Remote-Sever, but not in the Linked-Servers in Enterprise Manger.. at least I do not..
Good luck!
-
- It turns out that my problem isn't as widespread as I thought. I started trying other pairs of servers and most of them work properly. One of the two I was using for my original testing seems to be the problem child of the whole bunch. I hope that by comparing it to a server which does all this just fine will reveal the problem.
We are having the same problem in our agency. It has become quite frustrating. If you find a solution/answer would you post what you find? We will do likewise. Unfortunately, we don't have extra servers to try and set up another set to see if they work properly. Our problem is intermittent. If we reboot, the problem goes away, but comes back several days later. So far we haven't come up with a permanent solution, but our networking and DBA spend a little while on it, then usually resort to a reboot, just to get us up and running again. It would be nice to find out what is happening once and for all.
Thanks,
NorrisPC
- As if this wasn't strange enough, I discovered that what user does it can make a difference even if they both have sa rights on both servers. We each ran the same very simple query from one server to another. It worked for me and it failed for him.
I found some articals that answered my question, they may help with yours as well.
Go to: http://groups.google.com/group/microsoft.public.sqlserver.security
Search on the following inside the group:
"Linked Server Security and AD Domain"
Here are a few of the results. I found that these groups give some pretty good answers
Have Fun!
Gregory
One of those links led me to turn on Kerberos logging where I was able to generate the following error message:
An anonymous session connected from VALENTINE has attempted to open an LSA policy handle on this machine. The attempt was rejected with STATUS_ACCESS_DENIED to prevent leaking security sensitive information to the anonymous caller.
The application that made this attempt needs to be fixed. Please contact the application vendor. As a temporary workaround, this security measure can be disabled by setting the \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\TurnOffAnonymousBlock DWORD value to 1.Which leads me to the question "Why am I getting an attempt to connect anonymously when the linked server property is set to make the connection with the login's current security context?"
I don't really want to use the suggestion to allow the anonymous connection because it seems like that would totally nullify all my user rights settings on the target server.
- More weeks have passed and still no solution. I went through a Kerberos troubleshooting document and all seems good there. In the end, I realized that an anonymous connection was coming from the first SQL Server, so the problem must be on that end. The linked server is set up explicitly not to use anonymous connections, so I am at a loss as to why it is using them.
Have you setup a Service Principle Name (SPN) for the both servers and are they running under the same domain service account?
Dwatney - I am using linked SQL servers already for several years on tens of SQL 2000 servers.
I have the same issues as you. I also have tried all kinds of security combinations and linked server settings.
Linked servers using SQL logins usually always worked fine.
But as soon as I use Windows Authentication for users with limited rights I get error 18452 whenever using linked servers.
My next approach will be to switch all users to SQL logins.
Thomas
(it has been a while since I checked on this)
Yes, I made sure I had SPNs for all the servers and they all run as the same domain account.
Using SQL logins is my only suggestion when someone can't make it work with their Windows login. Going back to SQL logins just seems so wrong since using the Windows login is the recommended more secure option.
In your situation, does it work between some servers and not others or does it just not work for anything?
I had the same problem. View KB840219.
- please refer to http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx to see if it solve your problem.
Thank you, thank you, thank you!!
Finally, something that works. I started reading the referenced article thinking I would have to dig through every detail that I hadn't seen and tried before. Instead, it was a little thing that caught my eye. The sample comand to create a link server had the parameter @provstr='Integrated Security=SSPI;' in it. I thought "hmm, when I do this through the BUI, instead of selecting the "SQL Server" option, I'll choose the "Other Data Source" option and put stuff in manually so that I can put that SSPI stuff. And it worked! Why I can make some functional connections with the "SQL Server" option and I have to this with others, I don't know, but after 9 months, I'm not sure I care as long as I can make it work. Since I have so many connections to re-do, I put together a script that makes them with the options I want. In case anyone wants it, here it is (your options may vary)
declare @ServerName varchar(50)
Set @ServerName = 'MyServer'
exec sp_dropserver @ServerName
exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'
-- Set options
exec sp_serveroption @ServerName, 'data access', 'true'
exec sp_serveroption @ServerName, 'rpc', 'true'
exec sp_serveroption @ServerName, 'rpc out', 'true'
exec sp_serveroption @ServerName, 'use remote collation', 'true'
-- Test connection
declare @SQL nvarchar(200)
set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
exec sp_executesql @sql- Well, things aren't working so well after all. In all the links I fixed to work with Windows Authentication, the SQL logins now fail to work! Other than set things back so that all the things that had been working work again, I haven't had a chance to see about finding a solution to make both work at the same time. If I find one, I will post what I find.
- Using Windows Authentication with linked servers requires to grant the SQL server service account delegation rights, assign service principal names (SPNs) to it and use only the TCP/IP network library for SQL.
KB319723: How to use Kerberos authentication in SQL Server (http://support.microsoft.com/kb/319723) - This worked for me, thanks dwatney!
A lot of time has passed. My servers have been migrated to SQL2005 and they still have this issue.
To summarize, only some combinations of servers have this problem. They all run as the same domain account which has delegation rights. I remember setting up all that SPN stuff before. Looking at the network protocols, I see tcp/ip and shared memory both enabled. Would the shared memory protocol even matter when dealing with a linked server? I expect the servers that are working together properly have this enabled as well.
Given that the SQL Server software has been replaced on all of these servers, I have to wonder if it is some obscure Windows configuration difference. I have tried unsuccessfully in the past to discover such a difference.
Just a few back to basic's questions.
How many servers?
Has this ever worked correctly? ( completely)
On the linked server I think the 'logon' to the linked server is the login of the 'account' that wants to use the linked server. ( can you confirm )
Please start over from the begining. There are way too many do this, do this, do this, to figure out the current state.
What exactly is the version of SQL 2005, please post your results from SELECT @@VERSION.
Did you "migrate" or do a clean install of SQL 2005?
Next, in SSMS right click a working Linked Server and select the script option, and post it. Then do the same for a non-working Linked Server.
What kind of error are you getting?
Ok, starting from the begining... I have almost 50 instances registered in my SSMS. Most are 2005, but there are still a few 2000s. This problem has been there ever since I changed people from using SQL logins to Windows Logins when everything was 2000. This problem does not happen with SQL logins. In the past, I have tried to make a matrix of which connections worked and which ones didn't (we has a lot fewer instances back then). Most of the problems seemed to revolve around just a few servers and usually it was a problem in both directions. I have chosen 3 (one being the biggest culprit) that give a full range of working and not working connections.
All of our 2005 upgrades involved uninstalling SQL2000 and installing 2005. Some were fresh installs on new machines (or fresh OS installs).
First, our biggest problem child, Callisto. Callisto was an uninstall/reinstall.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
connection to DevDB1:
Code Snippet/****** Object: LinkedServer [DEVDB1] Script Date: 07/08/2008 09:06:17 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DEVDB1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEVDB1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'use remote collation', @optvalue=N'true'Connection to RepDB1:
Code Snippet/****** Object: LinkedServer [REPDB1] Script Date: 07/08/2008 09:07:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'REPDB1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REPDB1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'use remote collation', @optvalue=N'true'my test is the following code:
Code Snippetselect
top 1 * from repdb1.master.sys.objectsselect
top 1 * from devdb1.master.sys.objectsAs a sql user, those work fine. As a Windows user (a domain admin with sa rights on all 3 servers), I get the following for both commands:
Code SnippetMsg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Next we have DevDB1. DevDB1 was an uninstall/reinstall that also went from 32 to 64-bit (it is also a multi-instance server)
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
connectioon to Callisto:
Code Snippet/****** Object: LinkedServer [CALLISTO] Script Date: 07/08/2008 09:12:58 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CALLISTO', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CALLISTO',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'use remote collation', @optvalue=N'true'connection to RepDB1:
Code Snippet/****** Object: LinkedServer [REPDB1] Script Date: 07/08/2008 09:13:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'REPDB1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REPDB1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPDB1', @optname=N'use remote collation', @optvalue=N'true'Using the same test as above, everything is fine with SQL logins and with Windows logins, the connection from DevDB1 -> RepDB1 works fine and the connection from DevDB1 -> Callisto gives the same error as above.
Last we have RepDB1. If memory serves, RepDB1 was installed from scratch as 64-bit 2005.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
connection to Callisto:
Code Snippet/****** Object: LinkedServer [CALLISTO] Script Date: 07/08/2008 09:16:41 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CALLISTO', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CALLISTO',@useself=N'False',@locallogin=NULL,@rmtuser=N'ieuser',@rmtpassword='########'GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLISTO', @optname=N'use remote collation', @optvalue=N'true'Connection to DevDB1:
Code Snippet/****** Object: LinkedServer [DEVDB1] Script Date: 07/08/2008 09:17:03 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DEVDB1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEVDB1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULLGO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DEVDB1', @optname=N'use remote collation', @optvalue=N'true'RepDB1 can actually do the sample test to both servers with both types of logins succesfully.
It just happens that RepDB1 and DevDB1 are both 64-bit servers while Callisto is 32-bit. When everything was 2000, everything was 32-bit.
This problem has been a thorn in my side for 2 and a half years, so I'll be happy to try just about anythig within reason. The servers are in use, so I won't be able to try anything drastic during the working day.
Thank you in advance!
- Ok. From your example, it looks like your problems are all to and from Callisto and your problem is the error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
This is always caused by: the SQL Server service not being run as a domain account, the domain account not having "delegation" rights, the SPNs don't exist, or the firewall/router blocking NTLM connections to the server.
Are all 3 servers, on the same domain, using the same domain user account for the SQL Service?
Is Callisto running the Windows Firewall or other Firewall? Is it on a different network?
Are all the servers running on the default port (dynamic under 2005)? Do you have multiple instances on a server?
Log into DevDB1 and go to Start-> Run and type "cmd" click OK, and run:
sqlcmd /S Callisto /E
Does it work?
Have you tried deleting the linked server and using the "SQL Native Client"?
Try this on DevDB1
EXEC master.dbo.sp_addlinkedserver @server = N'testtest', @srvproduct=N'Test connection', @provider=N'SQLNCLI', @datasrc=N'Callisto', @catalog=N'tempdb'
and your your query against [testtest]. Let me confirm each of these as I go...
All 3 of these servers are in the same domain
All 3 are running as the same domain user (sqlsystem)
sqlsystem is trusted for delegation (and my account is not marked as "cannot be delegated")
Callisto is on a different subnet from the other two, but there is no firewall in between. I'm sure I can find an example of the problem from a server in the same subnet. Windows Firewall is not running on any of the three.
All three are running on port 1433. DevDB1 is the default instance of a multi-instance server. The other two are single-instance.
Running on Callisto, the test selects work both in sqlcmd and in SSMS. I failed to mention before that I was running SSMS on my workstation when they failed. I seem to recall past reseach indicated that it was a chain of 3 systems that made things complicated, while two was no big deal. I should also point out that this problem is consistant for just about everyone, so I have no reason to think it is something peculiar to my computer (which has changed twice since this began).
If I do a "setspn -L sqlsystem" get a whole list of MSSQLSvc entries which include Callisto, RepDB1 and DevDB1 on port 1433. If I do setspn on the server names, I just get host entries. I don't remember if this is correct or not.
I have not previopusly tried using SQL Native Client. I created testtest as you describe. The resulting connection looks good, but I still get the same error when trying to select from testtest from my SSMS on my computer. After what I saw earlier, I am not surprised to find that it works just fine from SSMS on DevDB1. Just for fun, from SSMS on DevDB1, I opened a query window connected to Callisto and ran a query pulling data from RepDB1 and got the error.
It has taken hours to write this because of constant interruptions, but I hope I didn't miss anything.
- Try changing my code to:
EXEC master.dbo.sp_addlinkedserver @server = N'testtest', @srvproduct=N'Test connection', @provider=N'SQLNCLI', @datasrc=N'np:Callisto', @catalog=N'tempdb'
This should force the connection to use Name Pipes instead of TCP/IP. Does that act any diferently?
There is a problem with machines on 2 domains and 3 hops (I cannot find the KB article). I have a situation where we have domain1->parentdomain->domain2. It will never pass Windows authentication from domain1 to domain2 thru parentdomain.
Also, if you have something like domain2 PC -> domain1.server1 -> domain1.server2, this will not work either. You can connect to domain1.server1 without problem, but it will never pass your authentication from domain1.server1 to domain1.server2. The only solution to these problems is to use SQL logins on the linked servers. Of course it acts differently, because named pipes is disabled, :-). I can enable it tonight (SQL restart required) and try.
All of our servers and workstations are on a single domain.
The testtest connection using named pipes seems to be working.
So riddle me this... Why does everything say to only use TCP/IP in order to make this work?
Should I only use NP for my "troublesome" links?
