create SQL server Link server using Active directory user account

Question

Hi
I am planning to create Link server to other SQL server using active derectory user (AD user) . my expectation is not specifying the password(if password changed ,then we need to change it every location is not handy ) . But didnt work . I checked by entering the password. then I am getting following erro.

+++++++++++++++++++This error noted in destnation server

Login failed for user 'DMN\usrreport'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: xx.xx.xx.xx]
++++++++++++++++++
Please note: I manually varified from AD user and able to login successfully from remote server .

+++++++++++++++++++++++++++++++++++++++++++++++
I did tested with option "Be made using the login current security context". But its appeared apply active loggin(my login) details permission to destination server and not the expected user permission as " dmn/reportuser"
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Any one can help would be highly appreciated

regards

Answer 1

Hi @Ashwan ,

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

Please note: I manually varified from AD user and able to login successfully from remote server .

Quote from thid doc.: to-create-a-linked-server-to-another-instance-of-sql-server-using-sql-server-management-studio

Be made using this security context
Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

I did tested with option "Be made using the login current security context". But its appeared apply active loggin(my login) details permission to destination server and not the expected user permission as " dmn/reportuser"

About this, you can reference @Erland Sommarskog 's reply in this case: query-to-a-server-linked-through-an-34execute-as34.html

You can test with Execute as ...

If you choose using the login current security context, it will use your current security context.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

You cannot use Windows login for the remote login. It must be an SQL login.

You cannot log on to SQL Server with a Windows username and password. You must log on to Windows first and then connect to SQL Server. And when you connect to a linked server, this option is not available to you. Thus, it must be an SQL login.

Answer 3

Hi @Ashwan ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi Mia and Erland Thanks for the update. So Links server are connected to "Containted " databases not the normal databases. so When we have alwayson setup after failover , database users lost access to the DB and we need to fix it . At this stage SQL login use same sids . there for I was Exploring other options as AD users . I understand link server cant use AD Users . Only option SQL Login need to use it.
thank you