sql serversql-server-2008
I tried to set up linked server. The target server is MS SQL 2008R2.
It works fine if I set Remote login by sa account.
However, if I use a windows account, it will popup an error.
And I get the following message from log.
Attempting to use an NT account name with SQL Server Authentication
Is there anyway to let linked server use windows authentications ?
Like @SpaceManSpiff said, don't forget to check if Mixed mode is enabled. Someone changed that setting for me and I had the same problem. Here is how to solve it:
Source: http://forums.eukhost.com/f15/login-failed-user-sa-microsoft-sql-server-error-18456-a-12544/
All are of these computers on a Windows Domain? If so you should be using the Windows Domain User Accounts instead of what sounds like local computer accounts.
Best Answer
No you can't specify a Windows account for the remote side of things.
All you can do is check the Impersonate checkbox, but you can't have a SQL Account on your server connect to the remote machine using a Windows domain account. Nor can you have a Windows user on your server connect to the remote machine using a different Windows account.
Now, that said what you can probably do is use EXECUTE AS to impersonate the windows account within a stored procedure (I'm assuming you are using a stored procedure here) then setup the linked server mapping for the account you are impersonating which will pass through correctly assuming that Kerberos is setup correctly on both machines.