Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
I know what it means. What I want is how to use windows authentication instead of SQL authentication ? –  maxisam Oct 8 '12 at 18:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.