October 19, 2007 at 8:45 am
Hello
[p][/p]
I have a SQL Server 2005 and a SQL Server 2000 (ssdm0201) on two different Servers.
[p][/p]
From the SQL 2005 i Linked the SQL 2000 Server.
No matter what security context I use, I can't query thes Linked SQL 2000.
[p][/p]
The errors I get for "without security" are:
OLE DB provider "SQLNCLI" for linked server "ssdm0201" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ssdm0201" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "ssdm0201".
[p][/p]
The error I get for "logins current security" is:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The user I run this with is dbo on both Servers.
[p][/p]
Can anybody help here? Thanks a lot
Lukas
October 20, 2007 at 8:43 pm
A couple thoughts.
Did you select SQL Server or some other driver?
What type of security options did you set? There's a second and third tab in the add linked server dialog.
October 22, 2007 at 12:53 am
I chose SQL Server as driver.
I used the options "Be made without a security context" and "Be made using the login's current security context" options and received the error messages i mentioned.
I then tried it with "Be made using this security context:" and used a SQL login I created on the SQL 2000 machine. That was successful, but not the way I want this happening.
All the Server Options set to true.
October 22, 2007 at 2:31 pm
We were having the same issue. A couple of things you should try:
1. If your environment is co-existing (i.e. SQLS 2k and 2k5) and you're getting this error then try this:
a) Start --> Run --> Dcomcnfg
b) Component services --> My Computer --> DCOM config --> MSDAINITIALIZE
c) Right click on MSDAINITIALIZE --> Properties -->Security
d) Add the SQL startup account under "Launch and activation permission", "Access permission", "Configuration permission"
e) Give full rights.
2. If you have a 'new' SQLS 2k5 and you're having issues, the chances are that you the server is not set for delegation and the SPNs are not applied. In Sql 2k5 BOL lookup for 'Configuring Linked Servers for Delegation'. If all that is already applied to your server then granting additional rights in GPo should resolve the issue. The issue is not there If a server admin logs in and creates a linked server.
Hope that helps.
May 25, 2008 at 8:30 am
i had same problems with SQL 2005 to SQL 2000 after installing the Cumulative 6 update for SQL 2005 all linked problems were resolved.
March 9, 2010 at 8:41 am
I am seeing an issue while trying to connect to a SQL 2000 instance from SQL 2K5 using a linked server.
When i use the option of " connect using the following credentials" by giving login name and password it works.
Now when i try to restrict the access to linked server by maping the local login to same remote user it does not work. I do not want all users to be able to use the linked server.
Any idea what could be the problem??
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply