How do I create a linked server based on Windows authentication?

Question

How do I create a linked server based on Windows authentication?

Marco

Default port

Points: 1408
More actions
March 29, 2010 at 12:28 pm

#231266

How do I create a linked server based on Windows authentication?
I know how to build a linked server based on SQL authentication.
EXEC master.dbo.sp_addlinkedserver
@server = N'SERVER01',
@srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SERVER01',
@useself=N'False',
@locallogin=N'SERVER02_User',
@rmtuser=N'SERVER01_User',
@rmtpassword='password'
With this linked server I can execute a query as the user "SERVER02_User"
EXECUTE AS LOGIN='SERVER02_User'
EXEC sp_executesql N'SELECT * FROM SERVER01.test.dbo.sysobjects'
REVERT
When I want to do this with a Windows user, I have a problem. I can't add a Windows user to the "Remote User" column in the "Local server login to remote server login mappings" screen. If I select "Impersonate" we get errors about the user not being trusted.
We have tried options with "SET TRUSTWORTHY ON" and "GRANT AUTHENTICATE SERVER", but we can't succeed in it.
How can we setup a linked server with windows authentication? (We need it for servers which are not running in "mixed mode"). We want to use a specific windows account.
Regards,
Marco
The computers Server01 and Server02 and the users User01 and User02 are all member of the same domain
The services are running under the local system account, so I have trusted the computer accounts

- Server02 trusts Server01 ("Trust this computer for delegation to specified services only", "MSSQLSvc", "Server01", "Port 1433")

User01 and User02 can connect to both servers (tested with "OSQL -E -S Servername", while being logged on as these users)
The statement "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid". Returns the values below:

When run from my client on Server01 and Server02:
net_transport, auth_scheme
TCP, KERBEROS
When run locally on Server01 and Server02
net_transport, auth_scheme
Shared memory, NTLM

I have checked the SPN registrations in the Active Directory for both servers:

"setspn -L Server01" returns

Registered ServicePrincipalNames for CN=Server01,OU=Servers,DC=Domain01:
MSSQLSvc/Server01.Domain01:1433
HOST/Server01
HOST/Server01.Domain01

"setspn -L Server02" returns

Registered ServicePrincipalNames for CN=Server02,OU=Servers,DC=Domain01:
MSSQLSvc/Server02.Domain01:1433
HOST/Server02
HOST/Server02.Domain01

I have done everything described in this article, but I still receive an error with the query below (I have run it locally on Server02 and through SSMS on my client on Server02):

SELECT SUSER_NAME()
EXECUTE AS LOGIN='Domain01\User01'
SELECT SUSER_NAME()
EXEC sp_executesql N'SELECT * FROM SERVER01.test.dbo.sysobjects'
REVERT
SELECT SUSER_NAME()

Result:

Domain01\MyAccount
Domain01\User01
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

What else can I do?

Regards Marco

Lowell SSC Guru Points: 323518 More actions · Answer 1

pretty sure it's simply changing the one parameter to true:

@useself=N'True',...

in that case, the logged in users credentials would be passe dot the linked server, and it would work/fail if they have permissions on that server or not.

from the gui, it looks like this screen:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Uncle Moki SSCrazy Points: 2596 More actions · Answer 2

My understanding is that in order to use Windows Authentication between two server, you'll need to setup some sort of "trust" between the servers and use the impersonate check box in the Server Login Mapping table. I'm not sure how to setup the necessary "trust" between the two servers to allow the token to get passed between them.

Otherwise, the best you can do is use the Server Login Mapping in the linked server's security tab to map a Windows Login (or SQL Login) on the local server to a SQL Login on the remote server. In our enviornment, I don't see everyone in the Local Login drop down, but manually typing a valid domain login works fine (domain\login).

Hopefully someone can explain how to setup the necessary trust between the two servers...that's something I've been wondering about too. And in your case, if you aren't running in Mixed Mode on the remote server, the Local Windows to Remote SQL Login that I mentioned isn't gonig to be much help to you. Sounds like you'll need to setup the proper "trust" (or whatever it's called) so the security token can be passed between the servers...and then use impersonation in your login mapping.

Marco Default port Points: 1408 More actions · Answer 3

Uncle Moki thanks for your reply!

The remote server isn't running in Mixed Mode, so I need to setup a trust. I have found the article below with some extra information:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

My situation:

Uncle Moki SSCrazy Points: 2596 More actions · Answer 4

Marco - Wow...that all sounds impressive. Unfortunately, I have no idea what you are talking about...:blush:. I'm really just an old-school programmer that tries to put on a DBA or IT hat when nobody else will.

Seriously...what you desribed sounds good. The results you mentioned remind of when I have an ASP page that uses Integrated Security and the page tries to access a database on another server. This fails because of the "hop" from Server01 (IIS) to Server02 (SQL). Server01 can't pass the credentials to Server02 so Server02 tries to authenticate Server01 as anonymous...which, of course, fails. I don't think there is any way around it in my web-to-sql environment...which is fine with me....I like handling security in the app istelf anyway.

As for your scenario - I'm not familiar with the way you are accessing the linked server. Is that how impersonation works when done entirely through TSQL? I thought you could only do that with a sproc...didn't know it could be done in-line like that. You don't need to pass the password or anything? Interesting.

In SSMS on Server02, while in your security context, can you execute any 4-part-name queries or open querires against the liked server? Something like this (assuming the linked server name is "SERVER01_LINKED"):

SELECT * FROM SERVER01_LINKED.test.dbo.sysobjects

--or

SELECT * FROM OPENQUERY(SERVER01_LINKED, 'SELECT * FROM test.dbo.sysobjects')

Another question: How is the linked server's security configured? Are you using the login mapping with impersonation that I mentioned or are you using the, "Be made using the logins's security context" option that Lowell mentioned? I'd try Lowell's method first....much easier. If that doesn't work, try adding domain\User01 mapping to the table and check the impersonate box. You'll need to be logged in as domain\User01 to test either technique. I can't vouch for the use of Execute As for domain accounts...maybe that works, maybe not, maybe it only works under certain situations, I don't know.

Uncle Moki SSCrazy Points: 2596 More actions · Answer 5

Ah, just found this MSKB 181362. But it sounds like you are on top of it based on your results....you actually switched context.

mchofman (3/30/2010)
...but I still receive an error with the query below (I have run it locally on Server02 and through SSMS on my client on Server02):
SELECT SUSER_NAME()
EXECUTE AS LOGIN='Domain01\User01'
SELECT SUSER_NAME()
EXEC sp_executesql N'SELECT * FROM SERVER01.test.dbo.sysobjects'
REVERT
SELECT SUSER_NAME()
Result:
Domain01\MyAccount
Domain01\User01
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The only thing I can suggest is to check the linked server's security configuration and then your syntax for using the linked server.