I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
Martin
I think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin
|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin
|||Take a look at this
http://support.microsoft.com/default...b;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin
Showing posts with label delegation. Show all posts
Showing posts with label delegation. Show all posts
Monday, March 12, 2012
Linked server delegation problem, works for a while after reboots
I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
used the workaround from the article, which seems a little more
straightforward than mine and has the same effect.
Thanks,
Martin
"Terry" wrote:
> Take a look at this
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> It references 2005>2005, and your issue is 2005>2000 but I beleive only
> the error message changed. This after 3+ months working with Microsoft.
> Terry
> Martin M wrote:
> > I have two SQL servers setup with linked servers pointing back and forth
> > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > The second is running SQL Server 2000 SP4. Both are running on Windows
> > Server 2003 Enterprise SP1.
> >
> > Both linked servers are setup to make connections using the login's current
> > security context.
> >
> > Immediately after both servers have been rebooted, both linked servers work
> > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > starts returning the following error message for users connected using
> > Windows authentication:
> >
> > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have yet to see the linked server from SQL 2000 to 2005 fail.
> >
> > I saw two similar issues to this, neither of which has been resolved. Does
> > anybody know if this issue is specific to connections from 2005 to 2000?
> > Would upgrading the second server resolve it or make the issue affect both
> > linked servers?
> >
> > Thanks,
> > Martin
>|||I just discovered that the linked server I created using the example command
from Microsoft's workaround doesn't seem to work for SQL logins; the original
workaround I found, creating the linked server on an alias that uses named
pipes, works for both, so I'll stick with it.
Martin
"Martin M" wrote:
> Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
> used the workaround from the article, which seems a little more
> straightforward than mine and has the same effect.
> Thanks,
> Martin
>
> "Terry" wrote:
> > Take a look at this
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> >
> > It references 2005>2005, and your issue is 2005>2000 but I beleive only
> > the error message changed. This after 3+ months working with Microsoft.
> >
> > Terry
> >
> > Martin M wrote:
> > > I have two SQL servers setup with linked servers pointing back and forth
> > > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > > The second is running SQL Server 2000 SP4. Both are running on Windows
> > > Server 2003 Enterprise SP1.
> > >
> > > Both linked servers are setup to make connections using the login's current
> > > security context.
> > >
> > > Immediately after both servers have been rebooted, both linked servers work
> > > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > > starts returning the following error message for users connected using
> > > Windows authentication:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I have yet to see the linked server from SQL 2000 to 2005 fail.
> > >
> > > I saw two similar issues to this, neither of which has been resolved. Does
> > > anybody know if this issue is specific to connections from 2005 to 2000?
> > > Would upgrading the second server resolve it or make the issue affect both
> > > linked servers?
> > >
> > > Thanks,
> > > Martin
> >
> >
between them. The first is running SQL Server 2005 with the post SP1 hotfix.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's current
> security context.
> Immediately after both servers have been rebooted, both linked servers work
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Does
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
used the workaround from the article, which seems a little more
straightforward than mine and has the same effect.
Thanks,
Martin
"Terry" wrote:
> Take a look at this
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> It references 2005>2005, and your issue is 2005>2000 but I beleive only
> the error message changed. This after 3+ months working with Microsoft.
> Terry
> Martin M wrote:
> > I have two SQL servers setup with linked servers pointing back and forth
> > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > The second is running SQL Server 2000 SP4. Both are running on Windows
> > Server 2003 Enterprise SP1.
> >
> > Both linked servers are setup to make connections using the login's current
> > security context.
> >
> > Immediately after both servers have been rebooted, both linked servers work
> > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > starts returning the following error message for users connected using
> > Windows authentication:
> >
> > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote host.
> > Msg 18452, Level 14, State 1, Line 0
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have yet to see the linked server from SQL 2000 to 2005 fail.
> >
> > I saw two similar issues to this, neither of which has been resolved. Does
> > anybody know if this issue is specific to connections from 2005 to 2000?
> > Would upgrading the second server resolve it or make the issue affect both
> > linked servers?
> >
> > Thanks,
> > Martin
>|||I just discovered that the linked server I created using the example command
from Microsoft's workaround doesn't seem to work for SQL logins; the original
workaround I found, creating the linked server on an alias that uses named
pipes, works for both, so I'll stick with it.
Martin
"Martin M" wrote:
> Thanks, Terry. That looks like my issue. Rather than obtain the hotfix, I
> used the workaround from the article, which seems a little more
> straightforward than mine and has the same effect.
> Thanks,
> Martin
>
> "Terry" wrote:
> > Take a look at this
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;925001
> >
> > It references 2005>2005, and your issue is 2005>2000 but I beleive only
> > the error message changed. This after 3+ months working with Microsoft.
> >
> > Terry
> >
> > Martin M wrote:
> > > I have two SQL servers setup with linked servers pointing back and forth
> > > between them. The first is running SQL Server 2005 with the post SP1 hotfix.
> > > The second is running SQL Server 2000 SP4. Both are running on Windows
> > > Server 2003 Enterprise SP1.
> > >
> > > Both linked servers are setup to make connections using the login's current
> > > security context.
> > >
> > > Immediately after both servers have been rebooted, both linked servers work
> > > fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> > > starts returning the following error message for users connected using
> > > Windows authentication:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> > > "Communication link failure".
> > > Msg 10054, Level 16, State 1, Line 0
> > > TCP Provider: An existing connection was forcibly closed by the remote host.
> > > Msg 18452, Level 14, State 1, Line 0
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > I have yet to see the linked server from SQL 2000 to 2005 fail.
> > >
> > > I saw two similar issues to this, neither of which has been resolved. Does
> > > anybody know if this issue is specific to connections from 2005 to 2000?
> > > Would upgrading the second server resolve it or make the issue affect both
> > > linked servers?
> > >
> > > Thanks,
> > > Martin
> >
> >
Linked server delegation problem, works for a while after reboots
I have two SQL servers setup with linked servers pointing back and forth
between them. The first is running SQL Server 2005 with the post SP1 hotfix
.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/defaul...kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin
between them. The first is running SQL Server 2005 with the post SP1 hotfix
.
The second is running SQL Server 2000 SP4. Both are running on Windows
Server 2003 Enterprise SP1.
Both linked servers are setup to make connections using the login's current
security context.
Immediately after both servers have been rebooted, both linked servers work
fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
starts returning the following error message for users connected using
Windows authentication:
OLE DB provider "SQLNCLI" for linked server "reporting" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have yet to see the linked server from SQL 2000 to 2005 fail.
I saw two similar issues to this, neither of which has been resolved. Does
anybody know if this issue is specific to connections from 2005 to 2000?
Would upgrading the second server resolve it or make the issue affect both
linked servers?
Thanks,
MartinI think I found a workaround to this issue. When I use the SQL Server
Configuration Manager to add an alias on the 2005 machine for the 2000
machine using Named Pipes, then recreate the linked server using the alias
instead of the actual server name, the linked server works for users logged
in using Windows authentication.
I verified that the new linked server works while the original one is still
broken, but I have not verified that the new one will continue to work...
Martin|||Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin|||Take a look at this
http://support.microsoft.com/defaul...kb;EN-US;925001
It references 2005>2005, and your issue is 2005>2000 but I beleive only
the error message changed. This after 3+ months working with Microsoft.
Terry
Martin M wrote:
> I have two SQL servers setup with linked servers pointing back and forth
> between them. The first is running SQL Server 2005 with the post SP1 hotf
ix.
> The second is running SQL Server 2000 SP4. Both are running on Windows
> Server 2003 Enterprise SP1.
> Both linked servers are setup to make connections using the login's curren
t
> security context.
> Immediately after both servers have been rebooted, both linked servers wor
k
> fine, but eventually the linked server pointing from SQL 2005 to SQL 2000
> starts returning the following error message for users connected using
> Windows authentication:
> OLE DB provider "SQLNCLI" for linked server "reporting" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have yet to see the linked server from SQL 2000 to 2005 fail.
> I saw two similar issues to this, neither of which has been resolved. Doe
s
> anybody know if this issue is specific to connections from 2005 to 2000?
> Would upgrading the second server resolve it or make the issue affect both
> linked servers?
> Thanks,
> Martin
Friday, March 9, 2012
Linked server and delegation
Hi to everybody,
I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
SQL2.
Both SQL Servers:
- are hosted on DC.
- use Windows authentication only.
- run under a domain user account, SQL1User and SQL2User.
Both SQL server domain user accounts:
- are "trusted for delegation" in AD.
- have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
User Admin1 can connect successfully to both SQL Servers.
User Admin1 have not "user cannot be delegated" in AD.
Now I setup a linked server from SQL1 to SQL2.
Linked server security is set to "be made using the login's current security
context"
Trying to access the linked server cause an error 18456 "Login failed for
user NT AUTHORITY/ANONIMOUS ACCESS" error.
What am I missing'
Seems Kerberos delegation does not work and fall back to NTLM which does not
support delegation, but AFAIK my Kerberos delegation setup should be fine.
Thanks,
CorradoYou need to specify the port probably. Sounds like you've pretty much got
it. I have copied and pasted my stock response below and apart from the port
looks like you are ok. Although I suggest it in my stock reply, I probably
wouldn't enable kerberos logging on a DC unless this is a test environment
(it needs a reboot for a start)
It can be a serious PITA and yes the various docs are confusing :-)
First of all I wouldn't use setspn, I tend to use ADSI Edit
It's in the 2k support tools on any 2k server CD. However if you
want to use setspn then the syntax is :
setspn -A MSSQLSvc/SQLNLB02.DOMSQL.COM:1433 NLBSQL02Svc
This is for a server called SQLNLB02 in the domain DOMSQL.COM
with a SQL Service account of DOMSQL\NLBSQL02Svc
You must also be able from the client to resolve the FQDN of the servers
involved using ping -a servername i.e. it must return
Pinging SQLNLB02.DOMSQL.COM [xxx.xxx.xxx.xxx]
and not
Pinging SQLNLB02 [xxx.xxx.xxx.xxx]
Regardless of what anything else says, you just need to set up SPN's
for the service accounts of the two SQL Servers involved. e.g.
I have 2 servers and 2 service accounts as below
Server1 : SQLNLB01
ServiceAccount : NLBSQL01Svc
Server2 : SQLNLB02
ServiceAccount : NLBSQL02Svc
Using ADSI Edit right click on the NLBSQL01Svc in the Users
container and choose Properties.In the select a property to view
listbox choose servicePrincipalName and then add a SPN like so
MSSQLSvc/SQLNLB01.DOMSQL.COM:1433
(where the FQDN of the server is the server that uses the account
I'm editing as it's SQL Service account). Do the same for the second
server and you should be up and running.
What I find really useful is enabling Kerberos logging on all the
computers involved. This will write to the event log and you'll be
able to see exactly why it's failing. .
To enable Kerberos logging look at
http://support.microsoft.com/defaul...kb;en-us;262177
If you've got AD set up then it's generally a malformed SPN or
poor name resolution (make sure you can ping -a the server IP
addresses and get back a FQDN and not just a server name)
This article also has some good stuff about Kerberos and SSPI
http://support.microsoft.com/defaul...kb;en-us;811889
and this one lists some of the kerberos errors you might see
http://support.microsoft.com/defaul...kb;EN-US;230476
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:%23Qwx%236k5DHA.2576@.TK2MSFTNGP11.phx.gbl...
security
not
I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
SQL2.
Both SQL Servers:
- are hosted on DC.
- use Windows authentication only.
- run under a domain user account, SQL1User and SQL2User.
Both SQL server domain user accounts:
- are "trusted for delegation" in AD.
- have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
User Admin1 can connect successfully to both SQL Servers.
User Admin1 have not "user cannot be delegated" in AD.
Now I setup a linked server from SQL1 to SQL2.
Linked server security is set to "be made using the login's current security
context"
Trying to access the linked server cause an error 18456 "Login failed for
user NT AUTHORITY/ANONIMOUS ACCESS" error.
What am I missing'
Seems Kerberos delegation does not work and fall back to NTLM which does not
support delegation, but AFAIK my Kerberos delegation setup should be fine.
Thanks,
CorradoYou need to specify the port probably. Sounds like you've pretty much got
it. I have copied and pasted my stock response below and apart from the port
looks like you are ok. Although I suggest it in my stock reply, I probably
wouldn't enable kerberos logging on a DC unless this is a test environment
(it needs a reboot for a start)
It can be a serious PITA and yes the various docs are confusing :-)
First of all I wouldn't use setspn, I tend to use ADSI Edit
It's in the 2k support tools on any 2k server CD. However if you
want to use setspn then the syntax is :
setspn -A MSSQLSvc/SQLNLB02.DOMSQL.COM:1433 NLBSQL02Svc
This is for a server called SQLNLB02 in the domain DOMSQL.COM
with a SQL Service account of DOMSQL\NLBSQL02Svc
You must also be able from the client to resolve the FQDN of the servers
involved using ping -a servername i.e. it must return
Pinging SQLNLB02.DOMSQL.COM [xxx.xxx.xxx.xxx]
and not
Pinging SQLNLB02 [xxx.xxx.xxx.xxx]
Regardless of what anything else says, you just need to set up SPN's
for the service accounts of the two SQL Servers involved. e.g.
I have 2 servers and 2 service accounts as below
Server1 : SQLNLB01
ServiceAccount : NLBSQL01Svc
Server2 : SQLNLB02
ServiceAccount : NLBSQL02Svc
Using ADSI Edit right click on the NLBSQL01Svc in the Users
container and choose Properties.In the select a property to view
listbox choose servicePrincipalName and then add a SPN like so
MSSQLSvc/SQLNLB01.DOMSQL.COM:1433
(where the FQDN of the server is the server that uses the account
I'm editing as it's SQL Service account). Do the same for the second
server and you should be up and running.
What I find really useful is enabling Kerberos logging on all the
computers involved. This will write to the event log and you'll be
able to see exactly why it's failing. .
To enable Kerberos logging look at
http://support.microsoft.com/defaul...kb;en-us;262177
If you've got AD set up then it's generally a malformed SPN or
poor name resolution (make sure you can ping -a the server IP
addresses and get back a FQDN and not just a server name)
This article also has some good stuff about Kerberos and SSPI
http://support.microsoft.com/defaul...kb;en-us;811889
and this one lists some of the kerberos errors you might see
http://support.microsoft.com/defaul...kb;EN-US;230476
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:%23Qwx%236k5DHA.2576@.TK2MSFTNGP11.phx.gbl...
quote:
> Hi to everybody,
> I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
> SQL2.
> Both SQL Servers:
> - are hosted on DC.
> - use Windows authentication only.
> - run under a domain user account, SQL1User and SQL2User.
> Both SQL server domain user accounts:
> - are "trusted for delegation" in AD.
> - have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
> User Admin1 can connect successfully to both SQL Servers.
> User Admin1 have not "user cannot be delegated" in AD.
> Now I setup a linked server from SQL1 to SQL2.
> Linked server security is set to "be made using the login's current
security
quote:
> context"
> Trying to access the linked server cause an error 18456 "Login failed for
> user NT AUTHORITY/ANONIMOUS ACCESS" error.
> What am I missing'
> Seems Kerberos delegation does not work and fall back to NTLM which does
not
quote:
> support delegation, but AFAIK my Kerberos delegation setup should be fine.
> Thanks,
> Corrado
>
Subscribe to:
Posts (Atom)