unable to create linked server

This means that the linked server will be created successfully but, failed to test the linked server connection. This is because your current login doesn't have the access to the linked server.

Try login using Domain\vivek, you can see the linked server, test it and it will be successful.

Hi there,
Please check out the below steps. hope this helps,
In SQL Server 2005 they have added a whole load of security issues that make this harder than it should be. The words "Kerberos Authentication" will become the bain of most sys-admins/DBA's lives. It effectively is used for pass-through authentication.

Here are the basics of what you need. 1) The servers (A and B) need to be set-up in Active Directory(AD) with delegation for Kerberos enabled. (this is set through your active directory admin panel)

2) The service account that your SQL Servers run under need to have delegation enabled also (this is also set through your active directory admin panel). - if they are not running under a service account, you need to create one.

3) The Servers need to have SPN's defined for the instance and the HOST and the machine name. (Using a tool called SetSPN in the windows support tools)

Support Tools (SetSPN is in this set) http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en

(Overview of how to add an SPN) http://technet.microsoft.com/en-us/library/bb735885.aspx

4) You may need to set your DB to "trustworthy"

ALTER DATABASE SET trustworthy on

5) After you have all of this done restart your instances.

6) Then try create your linked server again.

Finally you can test your connection to SQL Server. This should work fine if you have it all configured correctly.

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=ServerB;Integrated Security=SSPI;'
    ).MASTER.dbo.syscolumns

Hi,
go through bolow mentioned link.
http://serverfault.com/questions/88962/sql-server-to-sql-server-linked-server-setup
http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

Thnks!

>>This means that the linked server will be created successfully but, failed to test the linked server connection. This is because your current login doesn't have the access to the linked server.

Try login using Domain\vivek, you can see the linked server, test it and it will be successful.<<


But domain\vivek is present in the both the server's local admin group.

but i am able to create a linked server by sa account.

do you able to login to that server using given credentials ?

use the same credentials through which you are login to that server.

After that refer below link to access the same server

http://www.quackit.com/sql_server/sql_server_2008/tutorial/linked_servers.cfm

if you dont have management studio you can do the same using enterprise manager

under master database go to system store procedure to find that sp or directly write below inside your query analiser to run it

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'
parameter are as below...

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]
 


Arguments

--------------------------------------------------------------------------------


 [ @rmtsrvname = ] 'rmtsrvname'
Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | NULL
Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.
[ @locallogin = ] 'locallogin'


Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.
[ @rmtuser = ] 'rmtuser'

Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname, with a default of NULL.
[ @rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

yes i am able to login through te same credentials.

I am using sql server connection, i am not using other data source

Your Domain\vivek login may have admin rights, but when you created linked server you mapped Domain\vivek to the linked server right? this means that only Domain\vivek can Access the linked server.

So when you test the linked server using any other login, this will fail except for Login Domain\vivek.

the sa in SQL server A is different form the sa in SQL server B.

is it okay to use the linked server with the domain id logon?

from the error message
Microsoft SQL Server, Error: 18456

it clearly expalints the vivek is a invalid user, check on both the servers erorlog by typing xp_readerrorlog on both servers

I got below error in server b or external linked server:

Login failed for user 'domain\vivek'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: xx.xx.xx.xx]

I didn't find any related error logs on server A where I am trying to create a link server for server B

typo mistake
Requesting comments from experts...

na