|
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.
Is there anyway to let linked server use windows authentications ? |
|||
|
|
|
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. |
|||
|
|
|
There is some configuration to be done, you can't just start using it. In short, you need to set the logins up properly, create an SPN and be using kerberos. Once you do that, you configure the linked server to perform self-mapping. Microsoft's article on this is here. The tricky thing is getting the SPN correct, it is usually best to grant SPN-creation permission to the domain login that the SQL Server uses. That way, it will create a SPN for itself every time the server starts. You will need the help of your domain admin to get this working. |
|||
|
|
|
The error message says it all. When you type in a user name and a password, it's for a SQL authenticated account. The error message is telling you that you entered in Windows credentials, which aren't SQL credentials. |
|||
|
