unable to create linked server

I am unable to create a linked server using specific logins. ie by using domain\vivek.
Domain\vivek is present on both the server,ie from database server to external linked server.
domain\vivek is present in administrator groups in the local system.
SQL Server as been installed using windows authentication.
both the server services are running under local system.

i am getting below error:
The linked server has been created but failed a connection test. Do you want to keep the linked server?‘Login failed for user ‘domain\vivek’. (Microsoft SQL Server, Error: 18456)

but i am able to create linked server using sa account with password.

what i am missing in creating linked server using specific account?
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?
 
VIVEKANANDHAN_PERIASAMYConnect With a Mentor Author Commented:
The reason why I was not able to create a linked server under domain\vivek is ,we were using the "Be made using the security context" option which will take only SQL Authentication login’s. It cannot take windows authentication.

Resolution:
 
We have to use “Be made using the login’s current security context” option in this case.
 
 
Here there are two possible scenario’s
1.       User using the linked server.
2.       Stored procedure or  SQL jobs using the linked server.
 
Case 1:  When user is using the linked server, it will authenticate the user on the remote server.
 
Case 2:  When stored procedure or SQL jobs using the linked server, it will authenticate on SQL Server Agent credentials. So, if we want to access the server tk2stgsqlc04 under  domain\vivek,then SQL  Server agent should on sn1stgrsql04 should run on domain\vivek.


Requesting comments from expect...
0
 
AnujSQL Server DBACommented:
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.
0
 
Plk_In_EECommented:
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
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
>>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.
0
 
keyuCommented:
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
0
 
keyuCommented:
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.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
yes i am able to login through te same credentials.

I am using sql server connection, i am not using other data source
0
 
AnujSQL Server DBACommented:
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.
0
 
Alpha AuCommented:
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?
0
 
Ramesh Babu VavillaCommented:
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
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
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
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
typo mistake
Requesting comments from experts...
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
na
0
All Courses

From novice to tech pro — start learning today.