Error when trying to link to a SQL 2000 server from a SQL 2005 server
-
Our DBA is trying to link a SQL 2005 instance to a SQL 2000 instance on another server and is receiving the error 'Failed to retrieve data...' 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I was able to get it working for her by adding the above account to the Security\Logins on the target server, but only as a temporary solution. The link works, but some tables are not visible in the databases she needs.
We have recently migrated a few databases over from the SQL 2000 server and someone ran the stored procedure to copy the user accounts on the 2000 instance to the 2005 instance in order to maintain the SID for each account. One of the app vendors had trouble with security on the accounts on the 2005 instance
, so deleted them and added them back in. I am sure that this did not help the situation, but thought the information might help.I am being beat to death on this so any help would be appreciated.
Thanks,
-
You may need an account on the 2000 server that is for accvess only.
Then setup the linkserver security to use that account.
-
Are you working in a workgroup or in a domain? Looks like you're trying to link between the two servers using NT authentication. Is that indeed the case? If you are working in a workgroup then the 2K server user indeed needs to be defined on the 2K5 (again, assuming NT authentication). If you're in a domain then you should look into the domain controller. Please provide more info about your network setup and auth types.
-
when creating a linked-server [with SSMS in ServerObjects, LinkedServers], the default on the security page is "connections will be made without using a security context" so you attempt the "Guest" credentials [and error you are seeing].
The alternatives are
"connections be made using the login's current security context" which implies impersonation [probably not initially configured]
or
"connections are made using this <remote login> security context"
which is a one-size-fits-all SQL-login (not integrated domain logon)
but may be helpful to create such a login and configure it into l-s as a short-term workaround.
Be advised that when you use SSMS to script out an existing SQL-login [not integrated] you get [whether from SQL2000/2005 box]
CREATE LOGIN [UATreadonly] WITH PASSWORD=N'random', DEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER
LOGIN [UATreadonly] DISABLE
[where "UATreadonly" is local to us] which has 2 problems [thanks MS!]
1. syntax is new to SQL2005 so will fail to SQL2000 instance
2. does not include the actual SID (so expect to do lotsa sp_change_users_login calls)
so on a par with scripting out objects where the default is NOT to script permissions [doh!]
HTH
-
We have enabled a work around by using the Remote Login context and creating a new user account.
We are in a Domain not a Workgroup, and using both Windows and SQL Authentication.
Using the 'accounts current security login' should allow the link provided the user exist and has the proper security on the target server. I think this is where things got screwed up when the DBA scripted them and created them on the 2K5 server.
In any case, I appreciate the help.
Thanks,
-
Just a couple items to add here:
If you are trying to use windows authentication with a Linked server to go from Server A to Server B from a query executed on Workstation C then you have a 'double hop'. In this scenario the only way to get the users credentials to be passed to server B is if the network uses Kerberos, delegation is enabled, and the proper SPN's are registered for Server A.
There is a lot of info out there on this but here is one link to look at: http://support.microsoft.com/kb/319723
Regarding the logins and moving databases.
Were the logins created before or after the databases were restored? If they were created after then one of your problems may be that the users in the databases were orphaned. This occurs because during the restore SQL Server has try to relink the database the users to SQL Logins. Since the orphaned logins are not dropped form the database it often appears that the user is in the database but if you look closely there is no SQL Login associated to the user. My experience with this is on SQL Server 2000 so they may have improved this relinking process in SQL Server 2005.
More info here: http://msdn2.microsoft.com/en-us/library/ms175475.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply