 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / March 2007SQL2005 Linked server authentication problem
Hi, I am having an authentication issues with all my SQL2005 servers when using double-hop Kerberos authentication. The user I am authenticating is a domain admin. The problem occurs when I tried to connect from a client PC, to a SQL database where the query uses a linked server to retrieve the information from a different server e.g. Running on a PC with SQL Native Client: sqlcmd -S SERVER1 -E -I -t 15 -Q "select * from server2.database.dbo.table" When I try from a client to a SQL2005 then onto SQL2000 via linked server, I get this error: OLE DB provider "SQLNCLI" for linked server "dbaserver" returned message "Communication link failure". HResult 0x2746, Level 16, State 1 TCP Provider: An existing connection was forcibly closed by the remote host. HResult 0x4814, Level 14, State 1 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. When I try from a client to a SQL2005 then onto SQL2005 via linked server, I get this error: Msg 18456, Level 14, State 1, Server WEBSTAGING, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. I have seen various articles with solutions they are all either irrelevant or dont make any difference: 1) http://support.microsoft.com/kb/919710 ---We dont use certificates 2)Use SQL Server authentication ---Not an option 4) SynAttackProtect: http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx ---Set this up in registry, rebooted server, made no difference I checked this article and all steps were correct up to point 7: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx BUT when I run “select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid” I get : net_transport auth_scheme ----------------- --------------- Shared memory NTLM I am at a loss! Any ideas?? ta Wendy Wendy, You might want to read this document, which is not specific to SQL Server. http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/secu rity/tkerberr.mspx The one time I saw this problem it turned out to be this topic within the above document: Multiple principal entries in database RLF > Hi, > [quoted text clipped - 63 lines] > ta > Wendy The results from sys.dm_exec_connections tells you it's a local connection - maybe you are picking up cached connection? You probably have to clear out all caches - clearing the distributed query cache with DBCC FREESESSIONCACHE isn't likely to do it. You'd need to use: DBCC FREESYSTEMCACHE ('ALL') -Sue >Hi, > [quoted text clipped - 57 lines] >ta >Wendy Hiya, Tried this and am now getting: From Client to Server1: net_transport auth_scheme ----------------- --------------- TCP KERBEROS From server1 to Server2: net_transport auth_scheme ----------------- --------------- Shared memory NTLM Any ideas? Thanks Wendy > The results from sys.dm_exec_connections tells you it's a > local connection - maybe you are picking up cached [quoted text clipped - 66 lines] > >ta > >Wendy Check the client protocols on server1 using Configuration Manager. log onto Server1 and check things when you use sqlcmd to connect from Server1 to Server2. It should only being using Shared Memory when connect locally. -Sue >Hiya, > [quoted text clipped - 87 lines] >> >ta >> >Wendy Sue, I checked this out and the Client Protocols are setup like this: Name Order Enabled ----------------------------------------------------- Shared Memory 1 Enabled TCP/IP 2 Enabled Named Pipes Disabled VIA Diabled Should the order by reversed?? Thanks for your help with this Wendy > Check the client protocols on server1 using Configuration > Manager. log onto Server1 and check things when you use [quoted text clipped - 94 lines] > >> >ta > >> >Wendy Hi again sue, I am a bit confused to what you want me to check. Do you want me to log onto Server1 and run something like: sqlcmd -S SERVER1 -E -I -t 15 -Q "SELECT * from SERVER2.Database.dbo.TABLE" I am not 100% sure how I am supposed to check if it is connecting with shared memory or not. Do I just run this below query in the same CMD window as I ran the SQLCMD or not?? select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid I am finding it hard to capture anything about the SQLCMD from this table while it is running. Any ideas? Thanks Wendy > Check the client protocols on server1 using Configuration > Manager. log onto Server1 and check things when you use [quoted text clipped - 94 lines] > >> >ta > >> >Wendy Log onto Server1 and use sqlcmd to connect to Server2. The -S ServerName should be Server2. -Sue >Hi again sue, > [quoted text clipped - 117 lines] >> >> >ta >> >> >Wendy Hi Sue, I have done this and I can connect, it is double hopping that isnt working. I am not sure what you want me to check after I have run my sqlcmd from Server1: SQLCMD -S Server2 -E -I -t 15 -Q "select getdate()" The client protocols are set up like: Name Order Enabled ----------------------------------------------------- Shared Memory 1 Enabled TCP/IP 2 Enabled Named Pipes Disabled VIA Diabled Wendy > Log onto Server1 and use sqlcmd to connect to Server2. The -S > ServerName should be Server2. [quoted text clipped - 122 lines] > >> >> >ta > >> >> >Wendy You would have wanted to check what protocol is being used - specifically if shared memory is used to connect. Are you sure the instances Server1 and Server2 are not on the same box? If shared memory is used between the two then the instances appear to be on the same server. So that's not a double hop. -Sue >Hi Sue, > [quoted text clipped - 142 lines] >> >> >> >ta >> >> >> >Wendy Hi, These instances are not on the same server and I have tried this with different combinations of servers, it is not server specific it must be something to do with the standard SQL Server 2005 install and delegation. I am trying to double hop, that is the problem, it isnt working! If it is using shared memory this isnt because they are on the wrong server,it is because something is wrong and this is the problem. How do I change this? thanks wendy > You would have wanted to check what protocol is being used - > specifically if shared memory is used to connect. Are you [quoted text clipped - 151 lines] > >> >> >> >ta > >> >> >> >Wendy I understand that you are trying to authenticate across servers and the double hop issue. But...when you say "if it is using shared memory"...so we don't even know that for sure. If it is using shared memory and the instances are on different servers then there could be something wrong on the network end of things if it is seeing the other server as a local resource. If it really isn't using shared memory, you could just have a mess with your SPNs. The SPN for a server being incorrect could be at the root of all of it. At this rate, it would likely take months to get at all the information needed through newsgroup posts. I would suggest that you open a case with Microsoft PSS and have them walk through the whole thing by phone. -Sue >Hi, > [quoted text clipped - 166 lines] >> >> >> >> >ta >> >> >> >> >Wendy Thanks Sue. Our SPNs are fine as the SQL2005 ones were setup the same as SQL2000 and they work ok. I have opened a call with microsoft to try and get to the bottom of this. Thanks Wendy > I understand that you are trying to authenticate across > servers and the double hop issue. [quoted text clipped - 183 lines] > >> >> >> >> >ta > >> >> >> >> >Wendy Hiya, Just to let you know I raised a call with MS about this issue and it turns out this double-hop kerberos issue with linked servers in SQL2005 is an actual MS bug and is resolved with SP2: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896 or hotfix 925843 (http://support.microsoft.com/kb/925843/) Cheers Wendy > I understand that you are trying to authenticate across > servers and the double hop issue. [quoted text clipped - 183 lines] > >> >> >> >> >ta > >> >> >> >> >Wendy I am having the same problems as yours. It seems works again after I reboot the client machine. But this is not a solution since our application uses Component Service to access the database. Rebooting the middle tier machines during work hours is not an option. Please let me know how you solve the problem. Lijun > Hi, > [quoted text clipped - 63 lines] > ta > Wendy Hiya, Just to let you know I raised a call with MS about this issue and it turns out this double-hop kerberos issue with linked servers in SQL2005 is an actual MS bug and is resolved with SP2: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896 or hotfix 925843 (http://support.microsoft.com/kb/925843/) Hope this helps! Cheers Wendy > I am having the same problems as yours. It seems works again after I reboot > the client machine. But this is not a solution since our application uses [quoted text clipped - 72 lines] > > ta > > Wendy |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.