We help IT Professionals succeed at work.

Linked server error for sql 2008

I have recently just stood up a new MS sql 2008 server in a win2008 clustered congfiguration, everything I have gotten working but my linked servers.  It is giving me the following errors.  I went in and made suer my tcp/ip connections are on but after this I find miinimal information on how ti fix this error.  I am trying to create connections to my web servers which run on win 2003.  What do I need to do.

TITLE: Microsoft SQL Server Management Studio
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "SQLNCLI10" for linked server "servername" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "Severname".
OLE DB provider "SQLNCLI10" for linked server "Servername" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)



Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
This might get you in the right direction:

http://support.microsoft.com/kb/814398

Author

Commented:
amenkes:
Looked at the permissions for the account but it is an admistrator and has access to everything on the  sql server, still getting the error .
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Can you connect from SSMS?

Sometimes I get that error if I use the wrong user/password for the linked server.

Author

Commented:
amenkes:
I can connect with the user name and password, but when I try to make linked servers with it, it will not allow this.  I have logged onto both wervers with the account to amke sure it works and it does, but when I do a linked server its a no go.

Author

Commented:
amenkes:
Instead of using the impersonate I used with a user name a password and it connected, but he problem is aI cannot see the correct databases all I see is what I asssume are the sql db's becasue all the other db's are not there even though it has administrator access.  I even gave it direct access to all db's but it still does not show them.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It depends on how you are doing the linked server. If you do it via the SQL Server or Other Data Source option.

Try using SQL Server, and the linked server name should be in the form of

ServerName\InstanceName

An example is attached.
linkedserver.PNG
C# ASP.NET Developer
Top Expert 2010
Commented:
Here is the security settings and also the select statement to show that it is working with the linked server.

linkedserverSecurity.PNG
linkedserverresults.PNG

Author

Commented:
amenkes:

Yep it works this way but before we used
Be made without using a security context and then did with a user impersonation and it always worked.  We are tyring to connect from sql 2008 to sql 2005 and this method does not seem to work.  But if I do the same as you it works but I guess i am wonering why it does not work the old way is this something new in sql server 2008.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Not that I am aware of. It could be drivers, 64 bit. It could be anything. Without looking at the whole environment, I could not say.

Author

Commented:
Thanks, we will just have to use this until we can look into it further.