Linked server problem to SQL2K
-
Hi,
I am trying to configure a linked server from 2005 to 2000. I have created a new user on 2000 (SQL server user), and gave db access where needed.
I created new sql user on 2005 with same credentials (username / password) which I created on 2000.
Then, I tried to configure Linked server from 2005 to 2000. When I am trying to configure login mappings, I select the user from local login and check impersonate checkbox, and hit ok. But it gives me
The OLE DB provider "SQLNCLI" for linked server "2kservername" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "2kservername".
OLE DB provider "SQLNCLI" for linked server "2kservername" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
error message.
Ok, I remove impersonate checkbox and write remote user and password fileds. And again it gives me same error message.
But when I select "Be mabe with this security context" from "For a login not defined int he list above, connections will:" section and fill same user name and password, it accepts and everthing works fine.
What am I doing wrong?
Any help will be appreciated.
-
Bonjour,
Are you in a 64 bits environment? Probably yes!
You have to execute against your SQL 2000 a script found into the service pack folder. "instcat.sql".
This will update some system table in master.
I ran it while production it runs for about 15sec.
Backup master before, just in case!!!
If you want to use integrated security you will have to enable Kerberos, I did not set it up yet so I can’t tell you if it’s running well. For what I need to do on my SQL 2000 I have setup a sql account that access what I need. But yes I know it's not super clean
Let me know if that help.
David
-
Thanks for your reply,
Its not a 64 bits system. I have read about "instcat.sql" but as you mentioned its for 64bits systems. One interesting thing is, when I map Local account on 2005 to sql account on 2000, it works. But sql account to sql account mapping, it fails, and thats what I need.
Regards
-
Yes I have executed instcat.sql, nothing changed.
I found another interesting thing.
As I mentioned before, when I map local user account on 2005 to sql user account on 2000 it works, but not sql account to sql account. So for the first mapping I use local user account on 2005 to sql user account on 2000, and create a second mapping and set sql user to sql user mapping, everything works fine. I tested each user on 2005 and no problem, but again, when I remove first mapping (local to sql user), and leave sql user to sql user mapping alone, it gives me same error message and doesnt create linked server.
Do we need to map at least one local account on 2005 map any kind of account on 2000? Sounds not a good solution.
-
I do not use this kind of authentication, but it look logical to have a kind of both way authentication.
An easiest way will be to use "be made using this security context" insted of user mapping.
Or better, setting up kerberos and using Integrated security "current security context".
This is all I have on linked server.
-
Thanks for your interest.
Another funny thing: When I add linked server via script it works. eg:
EXEC sp_addlinkedserver 'SERVER2k', N'SQL Server';
GO
EXEC sp_addlinkedsrvlogin 'SERVER2k', 'false', 'sqluser', 'sqluser', 'p@ssw0rd'
GO
sp_testlinkedserver SERVER2k;
GO
no problem, but when I try samething via Management Studio it fails.
Anyway, I did it via script and dont touch anymore.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply